How to remove milliseconds in timestamp spark sql

user_6605964 0
Viewed 10147

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

apache-spark-sql hive
3 Answers

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

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

user_85881890

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'))
user_114934780
Related