I want to convert String column to timestamp column , but it returns always null values .
val t = unix_timestamp(col("tracking_time"),"MM/dd/yyyy").cast("timestamp")
val df= df2.withColumn("ts", t)
Any idea ?
Thank you .
2 Answers
Make sure your String column is matching with the format specified MM/dd/yyyy.
- If not matching then
nullwill be returned.
Example:
val df2=Seq(("12/12/2020")).toDF("tracking_time")
val t = unix_timestamp(col("tracking_time"),"MM/dd/yyyy").cast("timestamp")
df2.withColumn("ts", t).show()
//+-------------+-------------------+
//|tracking_time| ts|
//+-------------+-------------------+
//| 12/12/2020|2020-12-12 00:00:00|
//+-------------+-------------------+
df2.withColumn("ts",unix_timestamp(col("tracking_time"),"MM/dd/yyyy").cast("timestamp")).show()
//+-------------+-------------------+
//|tracking_time| ts|
//+-------------+-------------------+
//| 12/12/2020|2020-12-12 00:00:00|
//+-------------+-------------------+
//(or) by using to_timestamp function.
df2.withColumn("ts",to_timestamp(col("tracking_time"),"MM/dd/yyyy")).show()
//+-------------+-------------------+
//|tracking_time| ts|
//+-------------+-------------------+
//| 12/12/2020|2020-12-12 00:00:00|
//+-------------+-------------------+
1 Comment
HBoulmi
It was because of the date format, was not compatible with specified format . Thank you @Shu
As @Shu mentioned, the cause might have been the invalid format of tracking_time column. It is worth mentioning though, that Spark is looking for the pattern as a prefix of the column's value. Study these examples for better intuition
Seq(
"03/29/2020 00:00",
"03/29/2020",
"00:00 03/29/2020",
"03/29/2020somethingsomething"
).toDF("tracking_time")
.withColumn("ts", unix_timestamp(col("tracking_time"), "MM/dd/yyyy").cast("timestamp"))
.show()
//+--------------------+-------------------+
//| tracking_time| ts|
//+--------------------+-------------------+
//| 03/29/2020 00:00|2020-03-29 00:00:00|
//| 03/29/2020|2020-03-29 00:00:00|
//| 00:00 03/29/2020| null|
//|03/29/2020somethi...|2020-03-29 00:00:00|
tracking_timecolumn?