How to remove milliseconds in timestamp spark sql

Question

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

Answer 1

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")))

Answer 2

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