Klun
Reputation: 54
Spark SQL 2.3.1 : strange behavior with unix_timestamp function
I have a Spark SQL query like that :
SELECT
CAST(UNIX_TIMESTAMP('2015-03-29T01:11:23Z', "yyyy-MM-dd'T'HH:mm:ss'Z'") AS TIMESTAMP) as column1,
CAST(UNIX_TIMESTAMP('2015-03-29T02:11:23Z', "yyyy-MM-dd'T'HH:mm:ss'Z'") AS TIMESTAMP) as column2
FROM ...
The first column return the good result. However the second column is always NULL.
What I am missing ?
UPDATE :
The full code (I have removed the FROM clause to simplify) :
val spark = SparkSession.builder.appName("My Application").getOrCreate()
spark.sql("SELECT CAST(UNIX_TIMESTAMP('2015-03-29T01:11:23Z', \"yyyy-MM-dd'T'HH:mm:ss'Z'\") AS TIMESTAMP) as column1, CAST(UNIX_TIMESTAMP('2015-03-29T02:11:23Z', \"yyyy-MM-dd'T'HH:mm:ss'Z'\") AS TIMESTAMP) as column2").show(false)
The full trace :
2018-07-20 10:26:52 WARN NativeCodeLoader:62 - Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
2018-07-20 10:26:52 INFO SparkContext:54 - Running Spark version 2.3.1
2018-07-20 10:26:52 INFO SparkContext:54 - Submitted application: My Application
2018-07-20 10:26:52 INFO SecurityManager:54 - Changing view acls to: XXX
2018-07-20 10:26:52 INFO SecurityManager:54 - Changing modify acls to: XXX
2018-07-20 10:26:52 INFO SecurityManager:54 - Changing view acls groups to:
2018-07-20 10:26:52 INFO SecurityManager:54 - Changing modify acls groups to:
2018-07-20 10:26:52 INFO SecurityManager:54 - SecurityManager: authentication disabled; ui acls disabled; users with view permissions: Set(XXX); groups with view permissions: Set(); users with modify permissions: Set(XXX); groups with modify permissions: Set()
2018-07-20 10:26:52 INFO Utils:54 - Successfully started service 'sparkDriver' on port 36615.
2018-07-20 10:26:52 INFO SparkEnv:54 - Registering MapOutputTracker
2018-07-20 10:26:52 INFO SparkEnv:54 - Registering BlockManagerMaster
2018-07-20 10:26:52 INFO BlockManagerMasterEndpoint:54 - Using org.apache.spark.storage.DefaultTopologyMapper for getting topology information
2018-07-20 10:26:52 INFO BlockManagerMasterEndpoint:54 - BlockManagerMasterEndpoint up
2018-07-20 10:26:52 INFO DiskBlockManager:54 - Created local directory at /tmp/blockmgr-d8b77ac8-53a3-4923-810e-d81539b30369
2018-07-20 10:26:52 INFO MemoryStore:54 - MemoryStore started with capacity 366.3 MB
2018-07-20 10:26:52 INFO SparkEnv:54 - Registering OutputCommitCoordinator
2018-07-20 10:26:52 INFO log:192 - Logging initialized @1074ms
2018-07-20 10:26:52 INFO Server:346 - jetty-9.3.z-SNAPSHOT
2018-07-20 10:26:52 INFO Server:414 - Started @1129ms
2018-07-20 10:26:52 INFO AbstractConnector:278 - Started ServerConnector@7544a1e4{HTTP/1.1,[http/1.1]}{0.0.0.0:4040}
2018-07-20 10:26:52 INFO Utils:54 - Successfully started service 'SparkUI' on port 4040.
2018-07-20 10:26:52 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@1a6f5124{/jobs,null,AVAILABLE,@Spark}
2018-07-20 10:26:52 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@781e7326{/jobs/json,null,AVAILABLE,@Spark}
2018-07-20 10:26:52 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@22680f52{/jobs/job,null,AVAILABLE,@Spark}
2018-07-20 10:26:52 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@39c11e6c{/jobs/job/json,null,AVAILABLE,@Spark}
2018-07-20 10:26:52 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@324dcd31{/stages,null,AVAILABLE,@Spark}
2018-07-20 10:26:52 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@503d56b5{/stages/json,null,AVAILABLE,@Spark}
2018-07-20 10:26:52 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@72bca894{/stages/stage,null,AVAILABLE,@Spark}
2018-07-20 10:26:52 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@2575f671{/stages/stage/json,null,AVAILABLE,@Spark}
2018-07-20 10:26:52 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@329a1243{/stages/pool,null,AVAILABLE,@Spark}
2018-07-20 10:26:52 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@ecf9fb3{/stages/pool/json,null,AVAILABLE,@Spark}
2018-07-20 10:26:52 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@2d35442b{/storage,null,AVAILABLE,@Spark}
2018-07-20 10:26:52 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@27f9e982{/storage/json,null,AVAILABLE,@Spark}
2018-07-20 10:26:52 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@4593ff34{/storage/rdd,null,AVAILABLE,@Spark}
2018-07-20 10:26:52 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@37d3d232{/storage/rdd/json,null,AVAILABLE,@Spark}
2018-07-20 10:26:52 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@30c0ccff{/environment,null,AVAILABLE,@Spark}
2018-07-20 10:26:52 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@581d969c{/environment/json,null,AVAILABLE,@Spark}
2018-07-20 10:26:52 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@22db8f4{/executors,null,AVAILABLE,@Spark}
2018-07-20 10:26:52 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@2b46a8c1{/executors/json,null,AVAILABLE,@Spark}
2018-07-20 10:26:52 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@1d572e62{/executors/threadDump,null,AVAILABLE,@Spark}
2018-07-20 10:26:52 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@29caf222{/executors/threadDump/json,null,AVAILABLE,@Spark}
2018-07-20 10:26:52 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@46cf05f7{/static,null,AVAILABLE,@Spark}
2018-07-20 10:26:52 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@7048f722{/,null,AVAILABLE,@Spark}
2018-07-20 10:26:52 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@c074c0c{/api,null,AVAILABLE,@Spark}
2018-07-20 10:26:52 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@58dea0a5{/jobs/job/kill,null,AVAILABLE,@Spark}
2018-07-20 10:26:52 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@2a2bb0eb{/stages/stage/kill,null,AVAILABLE,@Spark}
2018-07-20 10:26:52 INFO SparkUI:54 - Bound SparkUI to 0.0.0.0, and started at http://XXX:4040
2018-07-20 10:26:52 INFO SparkContext:54 - Added JAR file:XXX.jar at spark://XXX with timestamp 1532075212997
2018-07-20 10:26:53 INFO Executor:54 - Starting executor ID driver on host localhost
2018-07-20 10:26:53 INFO Utils:54 - Successfully started service 'org.apache.spark.network.netty.NettyBlockTransferService' on port 42981.
2018-07-20 10:26:53 INFO NettyBlockTransferService:54 - Server created on XXX:42981
2018-07-20 10:26:53 INFO BlockManager:54 - Using org.apache.spark.storage.RandomBlockReplicationPolicy for block replication policy
2018-07-20 10:26:53 INFO BlockManagerMaster:54 - Registering BlockManager BlockManagerId(driver, XXX, 42981, None)
2018-07-20 10:26:53 INFO BlockManagerMasterEndpoint:54 - Registering block manager XXX:42981 with 366.3 MB RAM, BlockManagerId(driver, XXX, 42981, None)
2018-07-20 10:26:53 INFO BlockManagerMaster:54 - Registered BlockManager BlockManagerId(driver, XXX, 42981, None)
2018-07-20 10:26:53 INFO BlockManager:54 - Initialized BlockManager: BlockManagerId(driver, XXX, 42981, None)
2018-07-20 10:26:53 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@66bfd864{/metrics/json,null,AVAILABLE,@Spark}
2018-07-20 10:26:53 INFO SharedState:54 - Setting hive.metastore.warehouse.dir ('null') to the value of spark.sql.warehouse.dir ('file:XXX').
2018-07-20 10:26:53 INFO SharedState:54 - Warehouse path is 'file:XXX'.
2018-07-20 10:26:53 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@2dbd803f{/SQL,null,AVAILABLE,@Spark}
2018-07-20 10:26:53 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@3e48e859{/SQL/json,null,AVAILABLE,@Spark}
2018-07-20 10:26:53 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@22d1886d{/SQL/execution,null,AVAILABLE,@Spark}
2018-07-20 10:26:53 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@7df60067{/SQL/execution/json,null,AVAILABLE,@Spark}
2018-07-20 10:26:53 INFO ContextHandler:781 - Started o.s.j.s.ServletContextHandler@748fe51d{/static/sql,null,AVAILABLE,@Spark}
2018-07-20 10:26:53 INFO StateStoreCoordinatorRef:54 - Registered StateStoreCoordinator endpoint
2018-07-20 10:26:55 INFO CodeGenerator:54 - Code generated in 238.939456 ms
2018-07-20 10:26:55 INFO CodeGenerator:54 - Code generated in 14.895801 ms
2018-07-20 10:26:55 INFO SparkContext:54 - Starting job: show at XXXTransform.scala:21
2018-07-20 10:26:55 INFO DAGScheduler:54 - Got job 0 (show at XXXTransform.scala:21) with 1 output partitions
2018-07-20 10:26:55 INFO DAGScheduler:54 - Final stage: ResultStage 0 (show at XXXTransform.scala:21)
2018-07-20 10:26:55 INFO DAGScheduler:54 - Parents of final stage: List()
2018-07-20 10:26:55 INFO DAGScheduler:54 - Missing parents: List()
2018-07-20 10:26:55 INFO DAGScheduler:54 - Submitting ResultStage 0 (MapPartitionsRDD[4] at show at XXXTransform.scala:21), which has no missing parents
2018-07-20 10:26:55 INFO MemoryStore:54 - Block broadcast_0 stored as values in memory (estimated size 7.3 KB, free 366.3 MB)
2018-07-20 10:26:55 INFO MemoryStore:54 - Block broadcast_0_piece0 stored as bytes in memory (estimated size 3.5 KB, free 366.3 MB)
2018-07-20 10:26:55 INFO BlockManagerInfo:54 - Added broadcast_0_piece0 in memory on XXX:42981 (size: 3.5 KB, free: 366.3 MB)
2018-07-20 10:26:55 INFO SparkContext:54 - Created broadcast 0 from broadcast at DAGScheduler.scala:1039
2018-07-20 10:26:55 INFO DAGScheduler:54 - Submitting 1 missing tasks from ResultStage 0 (MapPartitionsRDD[4] at show at XXXTransform.scala:21) (first 15 tasks are for partitions Vector(0))
2018-07-20 10:26:55 INFO TaskSchedulerImpl:54 - Adding task set 0.0 with 1 tasks
2018-07-20 10:26:55 INFO TaskSetManager:54 - Starting task 0.0 in stage 0.0 (TID 0, localhost, executor driver, partition 0, PROCESS_LOCAL, 8067 bytes)
2018-07-20 10:26:55 INFO Executor:54 - Running task 0.0 in stage 0.0 (TID 0)
2018-07-20 10:26:55 INFO Executor:54 - Fetching spark://XXX.jar with timestamp 1532075212997
2018-07-20 10:26:55 INFO TransportClientFactory:267 - Successfully created connection to XXX/10.1.97.135:36615 after 23 ms (0 ms spent in bootstraps)
2018-07-20 10:26:55 INFO Utils:54 - Fetching spark://XXX.jar to /tmp/spark-853e3e5d-e228-473d-be50-c641d2b678a4/userFiles-7f3d8d8b-e9d4-4d9c-ac52-38e7fe59de7d/fetchFileTemp7750137601738248500.tmp
2018-07-20 10:26:55 INFO Executor:54 - Adding file:/tmp/spark-853e3e5d-e228-473d-be50-c641d2b678a4/userFiles-7f3d8d8b-e9d4-4d9c-ac52-38e7fe59de7d/XXX.jar to class loader
2018-07-20 10:26:55 INFO CodeGenerator:54 - Code generated in 5.345898 ms
2018-07-20 10:26:55 INFO Executor:54 - Finished task 0.0 in stage 0.0 (TID 0). 1139 bytes result sent to driver
2018-07-20 10:26:55 INFO TaskSetManager:54 - Finished task 0.0 in stage 0.0 (TID 0) in 178 ms on localhost (executor driver) (1/1)
2018-07-20 10:26:55 INFO TaskSchedulerImpl:54 - Removed TaskSet 0.0, whose tasks have all completed, from pool
2018-07-20 10:26:55 INFO DAGScheduler:54 - ResultStage 0 (show at XXXTransform.scala:21) finished in 0,321 s
2018-07-20 10:26:55 INFO DAGScheduler:54 - Job 0 finished: show at XXXTransform.scala:21, took 0,356248 s
+-------------------+-------+
|column1> > > |column2|
+-------------------+-------+
|2015-03-29 01:11:23|null |
+-------------------+-------+
2018-07-20 10:26:55 INFO SparkContext:54 - Invoking stop() from shutdown hook
2018-07-20 10:26:55 INFO AbstractConnector:318 - Stopped Spark@7544a1e4{HTTP/1.1,[http/1.1]}{0.0.0.0:4040}
2018-07-20 10:26:55 INFO SparkUI:54 - Stopped Spark web UI at http://XXX:4040
2018-07-20 10:26:55 INFO MapOutputTrackerMasterEndpoint:54 - MapOutputTrackerMasterEndpoint stopped!
2018-07-20 10:26:55 INFO MemoryStore:54 - MemoryStore cleared
2018-07-20 10:26:55 INFO BlockManager:54 - BlockManager stopped
2018-07-20 10:26:55 INFO BlockManagerMaster:54 - BlockManagerMaster stopped
2018-07-20 10:26:55 INFO OutputCommitCoordinator$OutputCommitCoordinatorEndpoint:54 - OutputCommitCoordinator stopped!
2018-07-20 10:26:55 INFO SparkContext:54 - Successfully stopped SparkContext
2018-07-20 10:26:55 INFO ShutdownHookManager:54 - Shutdown hook called
2018-07-20 10:26:55 INFO ShutdownHookManager:54 - Deleting directory /tmp/spark-1b68790c-9242-480e-a0b4-1b9d7068b27a
2018-07-20 10:26:55 INFO ShutdownHookManager:54 - Deleting directory /tmp/spark-853e3e5d-e228-473d-be50-c641d2b678a4
Upvotes: 2
Views: 1245
Answers (1)
Bartosz Konieczny
Reputation: 2033
I tried to reproduce the error and I succeeded. I've started my investigation by discovering what dates are problematic and after executing a small for loop I ended with:
val spark = SparkSession.builder.appName("My Application").getOrCreate()
for (hour <- 0 to 23) {
println(s"checking ${hour}")
val queryHour = if (hour < 10) {
s"0${hour}"
} else {
hour
}
spark.sql("SELECT CAST(UNIX_TIMESTAMP('2015-03-29T01:11:23Z', \"yyyy-MM-dd'T'HH:mm:ss'Z'\") AS TIMESTAMP) as column1, " +
"CAST(UNIX_TIMESTAMP('2015-03-29T"+queryHour+":11:23Z', \"yyyy-MM-dd'T'HH:mm:ss'Z'\") AS TIMESTAMP) as column2").show(false)
}
And it returned:
checking 0
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 01:11:23|2015-03-29 00:11:23|
+-------------------+-------------------+
checking 1
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 01:11:23|2015-03-29 01:11:23|
+-------------------+-------------------+
checking 2
+-------------------+-------+
|column1 |column2|
+-------------------+-------+
|2015-03-29 01:11:23|null |
+-------------------+-------+
checking 3
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 01:11:23|2015-03-29 03:11:23|
+-------------------+-------------------+
checking 4
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 01:11:23|2015-03-29 04:11:23|
+-------------------+-------------------+
checking 5
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 01:11:23|2015-03-29 05:11:23|
+-------------------+-------------------+
checking 6
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 01:11:23|2015-03-29 06:11:23|
+-------------------+-------------------+
checking 7
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 01:11:23|2015-03-29 07:11:23|
+-------------------+-------------------+
checking 8
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 01:11:23|2015-03-29 08:11:23|
+-------------------+-------------------+
checking 9
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 01:11:23|2015-03-29 09:11:23|
+-------------------+-------------------+
checking 10
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 01:11:23|2015-03-29 10:11:23|
+-------------------+-------------------+
checking 11
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 01:11:23|2015-03-29 11:11:23|
+-------------------+-------------------+
checking 12
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 01:11:23|2015-03-29 12:11:23|
+-------------------+-------------------+
checking 13
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 01:11:23|2015-03-29 13:11:23|
+-------------------+-------------------+
checking 14
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 01:11:23|2015-03-29 14:11:23|
+-------------------+-------------------+
checking 15
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 01:11:23|2015-03-29 15:11:23|
+-------------------+-------------------+
checking 16
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 01:11:23|2015-03-29 16:11:23|
+-------------------+-------------------+
checking 17
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 01:11:23|2015-03-29 17:11:23|
+-------------------+-------------------+
checking 18
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 01:11:23|2015-03-29 18:11:23|
+-------------------+-------------------+
checking 19
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 01:11:23|2015-03-29 19:11:23|
+-------------------+-------------------+
checking 20
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 01:11:23|2015-03-29 20:11:23|
+-------------------+-------------------+
checking 21
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 01:11:23|2015-03-29 21:11:23|
+-------------------+-------------------+
checking 22
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 01:11:23|2015-03-29 22:11:23|
+-------------------+-------------------+
checking 23
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 01:11:23|2015-03-29 23:11:23|
+-------------------+-------------------+
Thus, only 2 o'clock was impacted. From there I dug into Spark's datetimeExpressions.scala file where the conversion for datetime stuff is made. More precisely, after adding some breakpoints, I found the problem was made by a silent parsing error in org.apache.spark.sql.catalyst.expressions.UnixTime#eval method, and more exactly here:
case StringType if right.foldable =>
if (constFormat == null || formatter == null) {
null
} else {
try {
formatter.parse(t.asInstanceOf[UTF8String].toString).getTime / 1000L
} catch {
case NonFatal(_) => null
}
}
And the formatter is created with a time zone:
private lazy val formatter: DateFormat =
try {
DateTimeUtils.newDateFormat(constFormat.toString, timeZone)
} catch {
case NonFatal(_) => null
}
Next, I isolated the code introducing problem:
val formatterParseError: DateFormat = DateTimeUtils.newDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'", DateTimeUtils.getTimeZone("Europe/Paris"))
val formattedDateParseError = formatterParseError.parse("2015-03-29T02:11:23Z")
And effectively, I got a ParseException:
Unparseable date: "2015-03-29T02:11:23Z"
java.text.ParseException: Unparseable date: "2015-03-29T02:11:23Z"
at java.text.DateFormat.parse(DateFormat.java:366)
at com.waitingforcode.stackoverflow.SchemaTest$$anonfun$2.apply$mcV$sp(SchemaTest.scala:225)
at com.waitingforcode.stackoverflow.SchemaTest$$anonfun$2.apply(SchemaTest.scala:218)
at com.waitingforcode.stackoverflow.SchemaTest$$anonfun$2.apply(SchemaTest.scala:218)
at org.scalatest.OutcomeOf$class.outcomeOf(OutcomeOf.scala:85)
at org.scalatest.OutcomeOf$.outcomeOf(OutcomeOf.scala:104)
From that I checked time zone management in Javadoc https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html#timezone . It seems that when the time zone is expressed as 'Z' (ISO 8601 Time zone), the correct pattern option to use is X. And when I've changed it in my isolated test case, the date was correctly formatted:
val formatter: DateFormat = DateTimeUtils.newDateFormat("yyyy-MM-dd'T'HH:mm:ssX", DateTimeUtils.getTimeZone("Europe/Paris"))
val formattedDate = formatter.parse("2015-03-29T02:11:23Z")
println(s"formattedDate = ${formattedDate}")
// formattedDate = Sun Mar 29 04:11:23 CEST 2015
And it also worked for Spark's SQL that given (converted to Europe/Paris timezone, you change it in spark.sql.session.timeZone option):
checking 0
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 03:11:23|2015-03-29 01:11:23|
+-------------------+-------------------+
checking 1
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 03:11:23|2015-03-29 03:11:23|
+-------------------+-------------------+
checking 2
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 03:11:23|2015-03-29 04:11:23|
+-------------------+-------------------+
checking 3
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 03:11:23|2015-03-29 05:11:23|
+-------------------+-------------------+
checking 4
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 03:11:23|2015-03-29 06:11:23|
+-------------------+-------------------+
checking 5
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 03:11:23|2015-03-29 07:11:23|
+-------------------+-------------------+
checking 6
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 03:11:23|2015-03-29 08:11:23|
+-------------------+-------------------+
checking 7
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 03:11:23|2015-03-29 09:11:23|
+-------------------+-------------------+
checking 8
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 03:11:23|2015-03-29 10:11:23|
+-------------------+-------------------+
checking 9
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 03:11:23|2015-03-29 11:11:23|
+-------------------+-------------------+
checking 10
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 03:11:23|2015-03-29 12:11:23|
+-------------------+-------------------+
checking 11
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 03:11:23|2015-03-29 13:11:23|
+-------------------+-------------------+
checking 12
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 03:11:23|2015-03-29 14:11:23|
+-------------------+-------------------+
checking 13
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 03:11:23|2015-03-29 15:11:23|
+-------------------+-------------------+
checking 14
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 03:11:23|2015-03-29 16:11:23|
+-------------------+-------------------+
checking 15
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 03:11:23|2015-03-29 17:11:23|
+-------------------+-------------------+
checking 16
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 03:11:23|2015-03-29 18:11:23|
+-------------------+-------------------+
checking 17
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 03:11:23|2015-03-29 19:11:23|
+-------------------+-------------------+
checking 18
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 03:11:23|2015-03-29 20:11:23|
+-------------------+-------------------+
checking 19
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 03:11:23|2015-03-29 21:11:23|
+-------------------+-------------------+
checking 20
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 03:11:23|2015-03-29 22:11:23|
+-------------------+-------------------+
checking 21
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 03:11:23|2015-03-29 23:11:23|
+-------------------+-------------------+
checking 22
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 03:11:23|2015-03-30 00:11:23|
+-------------------+-------------------+
checking 23
+-------------------+-------------------+
|column1 |column2 |
+-------------------+-------------------+
|2015-03-29 03:11:23|2015-03-30 01:11:23|
+-------------------+-------------------+
To recap, the null is returned in case of failure and we can learn it from:
- JIRA tasks https://issues.apache.org/jira/browse/SPARK-8174 https://issues.apache.org/jira/browse/SPARK-8175
- Hive https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
and code comment:
/** * 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. If the format is missing, using format like "1970-01-01 00:00:00". * Note that hive Language Manual says it returns 0 if fail, but in fact it returns null. */
And we can solve it by using ISO-like timezone option (X).
Upvotes: 4
Related Questions
- Unix timestamp granularity changed to hours instead of milliseconds
- SparkSQL Timestamp query failure
- Spark 2.2 extracting date not working from unix_timestamp
- Pyspark - from_unixtime not showing the correct datetime
- Spark/Scala - unix_timestamp returning the wrong date?
- Why does `unix_timestamp` behave differently on Spark 2.3.x/2.0.x?
- spark timestamp conversion fail
- Why is unix_timestamp parsing this incorrectly by 12 hours off?
- unix_timestamp() function changes hour in scala spark
- Using unix_timestamp method in creating timestamp in spark