将12小时制转换为24小时制

Question

我正在尝试使用SparkyR将以下时间转换为24小时：

2021-05-18 9:00:00 PM

我的预期结果：2021-05-18 21:00:00

我试过：

data %>% 
  mutate(datetime_24 = to_timestamp("datetime_12", "yyyy-MM-dd hh:mm:ss"))

data %>% 
  mutate(datetime_24 = to_date("datetime_12", "yyyy-MM-dd hh:mm:ss"))

两者都会导致空值

我尝试以下作为起点，并得到了这个错误

data %>%
  mutate(datetime_24 = unix_timestamp(datetime_12, "yyyy-MM-dd hh:mm:ss"))

You may get a different result due to the upgrading of Spark 3.0: Fail to parse '2021-05-18 9:00:00 PM' in the new parser. You can set spark.sql.legacy.timeParserPolicy to LEGACY to restore the behavior before Spark 3.0, or set to CORRECTED and treat it as an invalid datetime string.

我在pyspark中也尝试了以下操作，但得到了类似的错误：

from pyspark.sql.functions import from_unixtime, unix_timestamp, col

df_time = spark.table("data")

df_time_new = df_time.withColumn('datetime_24', \
             from_unixtime(unix_timestamp(col(('datetime_12')), "yyyy-mm-dd hh:mm:ss"), "yyyy-mm-dd HH:mm:ss"))

错误：

You may get a different result due to the upgrading of Spark 3.0: Fail to parse '2021-05-18 9:00:00 PM' in the new parser. You can set spark.sql.legacy.timeParserPolicy to LEGACY to restore the behavior before Spark 3.0, or set to CORRECTED and treat it as an invalid datetime string. Caused by: DateTimeParseException: Text '2021-05-18 9:00:00 PM' could not be parsed at index 11

Answer 1

您可以将spark.sql.legacy.timeParserPolicy设置为legacy，如下所示：

spark.conf.set("spark.sql.legacy.timeParserPolicy","LEGACY")

在此之后，在解析datetime时不应出现错误

因为在spark v3.0（Read here）中有一些与datetime解析器相关的更改，所以您将得到该错误

读取日期时间模式here。根据这一点，您可以使用模式“a”进行PM或AM解析

to_date("datetime_12", "yyyy-MM-dd hh:mm:ss a")