to_timstamp function in non-legacy mode does not parse this format: yyyyMMddHHmmssSS

b_1 — Tue, 20 Jun 2023 08:51:36 GMT

I have this datetime string in my dataset: '2023061218154258' and I want to convert it to datetime, using below code. However the format that I expect to work, doesn't work, namely: yyyyMMddHHmmssSS. This code will reproduce the issue:

from pyspark.sql.functions import *
spark.conf.set("spark.sql.legacy.timeParserPolicy","CORRECTED")
# If the config is set to CORRECTED then the conversion will return null instead of throwing an exception.
 
df=spark.createDataFrame(
         data=[ ("1",  "2023061218154258")
                , ("2", "20230612181542.58")]
        ,schema=["id","input_timestamp"])
df.printSchema()
 
#Timestamp String to DateType
1. df.withColumn("timestamp",to_timestamp("input_timestamp", format = 'yyyyMMddHHmmssSS')).show(truncate=False)
df.withColumn("timestamp",to_timestamp("input_timestamp", format = 'yyyyMMddHHmmss.SS')).show(truncate=False)

output:

+---+-----------------+---------+
|id |input_timestamp  |timestamp|
+---+-----------------+---------+
|1  |2023061218154258 |null     |
|2  |20230612181542.58|null     |
+---+-----------------+---------+
 
+---+-----------------+----------------------+
|id |input_timestamp  |timestamp             |
+---+-----------------+----------------------+
|1  |2023061218154258 |null                  |
|2  |20230612181542.58|2023-06-12 18:15:42.58|
+---+-----------------+----------------------+

I tried to_timestamp with the format yyyyMMddHHmmssSS and I expected that it would convert the string 2023061218154258 into the timestamp 2023-06-12 18:15:42.58

When I change the line

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

into

spark.conf.set("spark.sql.legacy.timeParserPolicy","LEGACY") the issue is solved, but I don't want to use legacy mode (because it gives other issues).

Re: to_timstamp function in non-legacy mode does not parse this format: yyyyMMddHHmmssSS

Anonymous — Wed, 21 Jun 2023 06:10:53 GMT

Hi @Bas van den Berg

Great to meet you, and thanks for your question!

Let's see if your peers in the community have an answer to your question. Thanks.

Re: to_timstamp function in non-legacy mode does not parse this format: yyyyMMddHHmmssSS

b_1 — Wed, 11 Oct 2023 16:20:05 GMT

Is there anybody who has the same issue or knows that this is in fact an issue?

topic to_timstamp function in non-legacy mode does not parse this format: yyyyMMddHHmmssSS in Data Engineering

to_timstamp function in non-legacy mode does not parse this format: yyyyMMddHHmmssSS

Re: to_timstamp function in non-legacy mode does not parse this format: yyyyMMddHHmmssSS

Re: to_timstamp function in non-legacy mode does not parse this format: yyyyMMddHHmmssSS