One of my Hive table has a column which has dates in String format. After loading that table into Spark I am converting those dates to unix timestamp format using unix_timestamp() function. I am getting different values for same date and same date format when I use it in different Spark environments.
Here is the sample date for one of the row 2017-08-04 03:26:51.756658 and date format passed to unix_timestamp() function is yyyy-MM-dd HH:mm:ss.SSSSSS
Actual command used is as follows:
val baseWithUnixTime = base.withColumn("ZZOTIMSTP",
$"ZZOTIMSTP".cast(TimestampType))
.withColumn("ZZOTIMSTP", $"ZZOTIMSTP".cast(TimestampType))
.withColumn("unix_time", unix_timestamp($"ZZOTIMSTP", "yyyy-MM-dd HH:mm:ss.SSSSSS"))
When I execute above command in my local Spark, for this 2017-08-04 03:26:51.756658 value I get 1501813611 as unix timestamp.
When I execute same command in EMR Spark cluster, I get 1501817211 value.
If I try the same thing in Hive using select unix_timestamp("2017-08-04 03:26:51.756658", "yyyy-MM-dd HH:mm:ss.SSSSSS"); command, I get 1501817967 this value.
To summarize, Environment wise results are as follows:
+---------------------------+-----------+
| local Spark (version 2.2) | 1501813611|
+---------------------------|-----------+
| Spark in EMR (version 2.1)| 1501817211|
+---------------------------|-----------+
| Hive | 1501817967|
+---------------------------|-----------+
I wonder which is giving me true value? Why Hive and Spark give different values for same function and same set of values?