如何将自定义日期时间格式转换为时间戳? [英] How to convert custom datetime format to timestamp?

问题描述

知道为什么我会在下面得到结果吗?

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|
+------------------+-------------------+-----------+--------+----------------+---------+-------------------+

我正在尝试将DATETIME(格式为ddMMMYY:HH:mm:ss)转换为Timestamp(如上面的最后一列所示)，但它似乎没有转换为正确的值. 我提到了以下帖子，但没有帮助:

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:

更好的方法将字符串字段转换为Spark中的时间戳

任何人都可以帮助我吗?

Anyone can help me ?

推荐答案

使用y(年)而不是Y(周年):

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|
// +--------------------------------------------------------+

这篇关于如何将自定义日期时间格式转换为时间戳?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！