How to convert custom datetime format to timestamp?

Question

Any Idea why I am getting the result below?

scala> val b = to_timestamp($"DATETIME", "ddMMMYYYY:HH:mm:ss")
b: org.apache.spark.sql.Column = to_timestamp(`DATETIME`, 'ddMMMYYYY:HH:mm:ss')

scala> sourceRawData.withColumn("ts", b).show(6,false)
+------------------+-------------------+-----------+--------+----------------+---------+-------------------+
|DATETIME          |LOAD_DATETIME      |SOURCE_BANK|EMP_NAME|HEADER_ROW_COUNT|EMP_HOURS|ts                 |
+------------------+-------------------+-----------+--------+----------------+---------+-------------------+
|01JAN2017:01:02:03|01JAN2017:01:02:03 | RBS       | Naveen |100             |15.23    |2017-01-01 01:02:03|
|15MAR2017:01:02:03|15MAR2017:01:02:03 | RBS       | Naveen |100             |115.78   |2017-01-01 01:02:03|
|02APR2015:23:24:25|02APR2015:23:24:25 | RBS       |Arun    |200             |2.09     |2014-12-28 23:24:25|
|28MAY2010:12:13:14| 28MAY2010:12:13:14|RBS        |Arun    |100             |30.98    |2009-12-27 12:13:14|
|04JUN2018:10:11:12|04JUN2018:10:11:12 |XZX        | Arun   |400             |12.0     |2017-12-31 10:11:12|
+------------------+-------------------+-----------+--------+----------------+---------+-------------------+

I am trying to convert DATETIME (which is in ddMMMYY:HH:mm:ss format) to Timestamp (which is shown in the last column above) but it doesn't seem to be converting to correct value. I referred the below post but no help:

Better way to convert a string field into timestamp in Spark

Anyone can help me ?

score 5 · Accepted Answer · edited Dec 06 '20 at 21:06

Use y (year) not Y (week year):

spark.sql("SELECT to_timestamp('04JUN2018:10:11:12', 'ddMMMyyyy:HH:mm:ss')").show
// +--------------------------------------------------------+
// |to_timestamp('04JUN2018:10:11:12', 'ddMMMyyyy:HH:mm:ss')|
// +--------------------------------------------------------+
// |                                     2018-06-04 10:11:12|
// +--------------------------------------------------------+

Another example:

scala> sql("select to_timestamp('12/08/2020 1:24:21 AM', 'MM/dd/yyyy H:mm:ss a')").show
+-------------------------------------------------------------+
|to_timestamp('12/08/2020 1:24:21 AM', 'MM/dd/yyyy H:mm:ss a')|
+-------------------------------------------------------------+
|                                          2020-12-08 01:24:21|
+-------------------------------------------------------------+

score -1 · Answer 2 · answered Dec 17 '17 at 12:23

-1

Try this UDF:

val changeDtFmt = udf{(cFormat: String,
                         rFormat: String,
                         date: String) => {
  val formatterOld = new SimpleDateFormat(cFormat)
  val formatterNew = new SimpleDateFormat(rFormat)
  formatterNew.format(formatterOld.parse(date))
}}

sourceRawData.
  withColumn("ts", 
    changeDtFmt(lit("ddMMMyyyy:HH:mm:ss"), lit("yyyy-MM-dd HH:mm:ss"), $"DATETIME")).
  show(6,false)

answered Dec 17 '17 at 12:23

rohitkulky

1,112
10
23

what is lit() function in the above answer? – Arun Dec 17 '17 at 13:45
2

There's a standard function for date format so no need for a custom UDF. – Jacek Laskowski Dec 17 '17 at 13:54

LUZO · Answer 3 · 2018-01-19T08:47:05.507

try below code

I have created a sample dataframe "df" for the table

+---+-------------------+
| id|               date|
+---+-------------------+
|  1| 01JAN2017:01:02:03|
|  2| 15MAR2017:01:02:03|
|  3|02APR2015:23:24:25 |
+---+-------------------+
val t_s= unix_timestamp($"date","ddMMMyyyy:HH:mm:ss").cast("timestamp")

df.withColumn("ts",t_s).show()

+---+-------------------+--------------------+
| id|               date|                  ts|
+---+-------------------+--------------------+
|  1| 01JAN2017:01:02:03|2017-01-01 01:02:...|
|  2| 15MAR2017:01:02:03|2017-03-15 01:02:...|
|  3|02APR2015:23:24:25 |2015-04-02 23:24:...|
+---+-------------------+--------------------+

Thanks

How to convert custom datetime format to timestamp?

3 Answers3

Linked

Related