Why does `unix_timestamp` behave differently on Spark 2.3.x/2.0.x?

Question

Trying to do a simple transformation using unix_timestamp behave differently from Spark 2.0.2 and 2.3.x.

At first, I was thinking it could be a Spark environment related issue, like timezone differences. But all the settings are the same.

The example below shows the described behavior.


import org.apache.spark.sql.types.{TimestampType}


case class Dummy(mts:String, sts:String)

val testData = Seq(Dummy("2018-05-09-06.57.53.013768", "2018-05-09-06.57.53.013198"), Dummy("2018-11-21-04.30.03.804441", "2018-11-21-04.30.03.802212")).toDF

val result = testData
      .withColumn("time1", unix_timestamp(col("sts"), "yyyy-MM-dd-HH.mm.ss.SSSSSS").cast(TimestampType))
      .withColumn("time2", unix_timestamp(col("sts"), "yyyy-MM-dd-HH.mm.ss.SSSSSS").cast(TimestampType))


result.select($"time1", $"time2", $"sts", $"mts").show(false)

scala> spark.version
res25: String = 2.3.1.3.0.1.0-187

scala> result.select("time1", "time2", "sts", "mts").show(false)
+-----+-----+--------------------------+--------------------------+
|time1|time2|sts                      |mts                      |
+-----+-----+--------------------------+--------------------------+
|null |null |2018-05-09-06.57.53.013198|2018-05-09-06.57.53.013768|
|null |null |2018-11-21-04.30.03.802212|2018-11-21-04.30.03.804441|
+-----+-----+--------------------------+--------------------------+

scala>

scala> spark.version
   def version: String

scala> spark.version
res4: String = 2.0.2

scala> result.select("time1", "time2", "sts", "mts").show(false)
+---------------------+---------------------+--------------------------+--------------------------+
|time1                |time2                |sts                      |mts                      |
+---------------------+---------------------+--------------------------+--------------------------+
|2018-05-09 06:58:06.0|2018-05-09 06:58:06.0|2018-05-09-06.57.53.013198|2018-05-09-06.57.53.013768|
|2018-11-21 04:43:25.0|2018-11-21 04:43:27.0|2018-11-21-04.30.03.802212|2018-11-21-04.30.03.804441|
+---------------------+---------------------+--------------------------+--------------------------+

Is there any particular reason for this behavior?

Carlos Vilchez · Accepted Answer · 2019-06-03T07:48:48.610

The problem you have is related to the function unix_timestamp.

It converts a string to Unix timestamp in seconds. So anything after seconds is ignored. Spark 2.0.2 was quite forgiving and substituted the SSSSSS part of your pattern with a 0.

However, somewhere between Spark 2.0.2 and 2.3.x, the implementation changed and it, and you have a null to call your attention to it.

How to solve it? Just remove the .SSSSSS and it looks like this:

val result = testData
  .withColumn("time1", unix_timestamp(col("sts"), "yyyy-MM-dd-HH.mm.ss").cast(TimestampType))
  .withColumn("time2", unix_timestamp(col("sts"), "yyyy-MM-dd-HH.mm.ss").cast(TimestampType))

result.select("time1", "time2", "sts", "mts").show(false)

+-------------------+-------------------+--------------------------+--------------------------+
|time1              |time2              |sts                       |mts                       |
+-------------------+-------------------+--------------------------+--------------------------+
|2018-05-09 06:57:53|2018-05-09 06:57:53|2018-05-09-06.57.53.013198|2018-05-09-06.57.53.013768|
|2018-11-21 04:30:03|2018-11-21 04:30:03|2018-11-21-04.30.03.802212|2018-11-21-04.30.03.804441|
+-------------------+-------------------+--------------------------+--------------------------+

Thanks, Exactly I noticed the zero part as well, I have tried it both in 2.0.2 and other most recent versions but with no luck at all to get the milliseconds. Is there a way to get the milliseconds? — OBarros, May 30 '19 at 19:02
There is no Spark function for this, but you can create a udf to do what you need. You can find that someone has already done it [in this link](https://stackoverflow.com/a/44889783/702002). — Carlos Vilchez, May 31 '19 at 08:24
Thanks. I'm currently using and UDF as well. I somehow find it odd not having a build in functionality for it, since it's achievable by using the `SimpleDateFormat` to parse and get the time. Probably worth adding? — OBarros, Jun 01 '19 at 08:32

Why does `unix_timestamp` behave differently on Spark 2.3.x/2.0.x?

1 Answers1