Convert string epoch to string timestamp in Scala

Question

I have a column ORDER_DATE with epoch timestamp in string. How can I convert this column with string like str = "1536309236032" which is time in epoch to a string with format: 2018-09-07T14:03:56.032Z in Scala?

Currently I am using:

from_unixtime(input.col(ORDER_DATE), "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")

but this converts incorrectly to 50668-08-21 01:10:00.000. Here, it increases the year and increments 000 for milliseconds.

I don't want to divide by 1000 as we would like to have the result in milli seconds.

Possible duplicate https://stackoverflow.com/questions/33475229/convert-epoch-to-datetime-in-scala-spark — darkmatter
– darkmatter, Commented Sep 18, 2018 at 13:25
I have made my question more clear and it is a different requirement from what link you mentioned. Can you help me in this? — Abhinav Kaushal Keshari
– Abhinav Kaushal Keshari, Commented Sep 18, 2018 at 14:36
From the length of your input time string, it's probably in milliseconds. Try: from_unixtime($"epochstr".cast("long")/1000, "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'") — Leo C
– Leo C, Commented Sep 18, 2018 at 16:01
But I want the output in milliseconds as well. Is there a way to get that? — Abhinav Kaushal Keshari
– Abhinav Kaushal Keshari, Commented Sep 18, 2018 at 17:18

Shaido · Accepted Answer · 2018-09-19 05:35:59Z

1

In the documentation, the definition of from_unixtime is as follows:

Converts the number of seconds from unix epoch (1970-01-01 00:00:00 UTC) to a string representing the timestamp of that moment in the current system time zone in the given format.

It uses seconds and are thus not compatible with milliseconds which is why the result is wrong. To convert the epoch timestamp and keeping the millisecond information, you can use concat:

val spark = SparkSession.builder.getOrCreate()
import spark.implicits._

df.withColumn("time", concat(
    from_unixtime($"ORDER_DATE"/1000, "yyyy-MM-dd'T'HH:mm:ss."), 
    $"ORDER_DATE".substr(length($"ORDER_DATE")-2, length($"ORDER_DATE")), 
    lit("Z")))

This will work since the last 3 digits in the epoch timestamp is the same as those in the wanted result.

edited Sep 19, 2018 at 5:35

answered Sep 19, 2018 at 3:31

Shaido

28.6k26 gold badges76 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Abhinav Kaushal Keshari Over a year ago

Can you also tell a way so that I perform this action only when the field in the column is not "0" otherwise it leaves the field empty?

Shaido Over a year ago

@AbhinavKaushalKeshari: You can use when and otherwise, see for example here: stackoverflow.com/questions/37064315/…

Abhinav Kaushal Keshari · Accepted Answer · 2018-09-19 09:27:01Z

0

I got the idea from @Shaido and I did something similar. Finally, this solved the issue for me:

input.withColumn("time",
concat(from_unixtime(input.col("ORDER_DATE")/1000, "yyyy-MM-dd'T'HH:mm:ss"), 
typedLit("."), substring(input.col("ORDER_DATE"), 11, 3), typedLit("Z")))

edited Sep 19, 2018 at 9:27

answered Sep 19, 2018 at 9:11

Abhinav Kaushal Keshari

5784 silver badges12 bronze badges

Collectives™ on Stack Overflow

Convert string epoch to string timestamp in Scala

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related