0

I have a column in Timestamp format that includes milliseconds.

I would like to reformat my timestamp column so that it does not include milliseconds. For example if my Timestamp column has values like 2019-11-20T12:23:13.324+0000, I would like my reformatted Timestamp column to have values of 2019-11-20T12:23:13

Is there a straight forward way to perform this operation in spark-scala? I have found lots of posts on converting string to timestamp but not for changing the format of a timestamp.

RKB
  • 73
  • 1
  • 11

1 Answers1

1

You can try trunc.

See more examples: https://sparkbyexamples.com/spark/spark-date-functions-truncate-date-time/

Cesar A. Mostacero
  • 720
  • 6
  • 12
  • Thanks for your post Cesar. Using date_trunc and "Second" as my measure of time almost gets me there. However what occurs is that my date goes from 2019-11-20T00:23:00.324+0000 to 2019-11-20T00:23:00.000+0000. So it just sets all values less than 1 second to 0 rather than dropping those. I want my timestamp format to drop milliseconds entirely and return "yyyy-mm-ddTHH:mm:ss" with no values (even 0) for milliseconds and below. – RKB Nov 23 '19 at 00:40
  • Then you are looking for a date format, please check : https://sparkbyexamples.com/spark/spark-sql-how-to-convert-date-to-string-format/ – Cesar A. Mostacero Nov 23 '19 at 05:18
  • Thanks @Cesar, that provides the correct datetime without milliseconds but as a string column. I took that string column and applied every method I could find to convert back to timestamp but in every case conversion re-adds the milliseconds values. This includes using unix_timestamp, to_timestamp, cast(TimestampType).as("timestamp"). Again, thanks for your help, any other suggestions? – RKB Nov 23 '19 at 16:00
  • The issue I had with milliseconds, was that if milliseconds were non-zero I could not upload to a database table. Reformatting using the date_format from your last post in the with column code: .withColumn("DTTM", date_format(DF("DTTM"), "yyyy-MM-dd HH:mm:ss").cast(TimestampType)) provides the timestamp with zeros for milliseconds+nanoseconds. While I thought this would still be an issue for transfer, it was not and transfer occurred without error. Thanks for your help @Cesar! – RKB Nov 23 '19 at 16:45