CODEWITHSUNDEEP
hiveapache-spark-sql
Babu
Babu

Reputation: 891

How to remove milliseconds in timestamp spark sql

I want to remove the milli seconds part when selecting the column through spark sql.

Ex: 2012-10-17 13:02:50.320

I want the result as 2012-10-17 13:02:50 I tried 

spark.sql("select cast(datecol as timestamp) from table 
spark.sql("select unix_timestamp(datecol,"yyyy-MM-dd HH:mm:ss") from table

Both seems not working, substring works but I need timestamp format ,Is there an other way to do it?

Thanks in advance

Upvotes: 8

Views: 16102

Answers (3)

Salim
Salim

Reputation: 2546

For everyone who is looking for a solution with spark DataFrame methods: In case your column is of type Timestamp and not String, you can use the date_trunc("second", column) function:

// remove milliseconds of datetime column
val df2 = df.withColumn("datetime", date_trunc("second", col("datetime")))

Upvotes: 9

KS17
KS17

Reputation: 95

A way to solve it is to use to_timestamp function if you want to move the values to a new column

df = df.withColumn("New Column", to_timestamp("DateTimeCol", 'yyyy-MM-dd HH:mm:ss'))

Upvotes: -2

Manu Gupta
Manu Gupta

Reputation: 830

As your timestamp value is string and you are casting it to timestamp, you can try it using substring function.

Second option :

spark.sql("select from_unixtime(unix_timestamp(datecol, 'yyyy-MM-dd HH:mm:ss.SSS'),'yyyy-MM-dd HH:mm:ss') from table")

You were not providing the input format, that may be the reason you are getting the error.

I hope, this will work.

Thanks, Manu

Upvotes: 3

Related Questions