Null values while converting string to datetime with pyspark

user_1592334 0
Viewed 2014

I have recently started working with pyspark on databricks and it's funny how I have been getting NULL values when converting the following string to DateTime data type. I have checked several articles here on how to do that but none of them seem worked for me.

sample data

    invoiceId  quantity invoicedate
     001         34     12/1/2010 8:26
     003         10     12/2/2010 8:26
     004         10     30/2/2010 8:26

I am trying to convert invoicedate (string datatype) into DateTime datatype using pyspark

pyspark apache-spark
2 Answers

Updated with example for spark 3.0

This solution is for spark 2, because it's using Java SimpleDateFormat for datetime pattern for to_timestamp


import pyspark.sql.functions as f

df.select(
    f.to_timestamp(f.col('invoicedate'), 'dd/MM/yyyy HH:mm').alias('some date')
)

In spark 3, to_timestamp uses own dateformat and it's more strict than in spark 2, so if your date doesn't match with datetime pattern you will get the error(like in your case).

So you have 2 options with spark 3:

  1. Set property "spark.sql.legacy.timeParserPolicy"="LEGACY" and use code from my example above.
  2. Specify pattern accroding to spark3 dateformat. Like this:

df.select(
    f.to_timestamp(f.col('invoicedate'), 'd/M/y H:m').alias('some date')
)

Anyway you will get null for 30/2/2010 8:26 as there isn't 30 days in Febrary.

user_77798650

I added the below codes to the codes @Artem Astashov provided and it worked

import pyspark.sql.functions as f
spark.sql("set spark.sql.legacy.timeParserPolicy=LEGACY") # this bit 
resolves the issues

df.select(
f.to_timestamp(f.col('invoicedate'), 'dd/MM/yyyy HH:mm').alias('some date'))
user_15923340
Related