Convert date from String to Date format in Dataframes

Question

I am trying to convert a column which is in String format to Date format using the to date function but its returning Null values   df createOrReplaceTempView  incidents   spark sql  select Date from incidents   show     ----------         Date   ----------   08 26 2016   08 26 2016   08 26 2016   06 14 2016   spark sql  select to date Date  from incidents   show     ---------------------------   to date CAST Date AS DATE      ---------------------------                          null                          null                          null                          null    The Date column is in String format     -- Date  string  nullable   true

User · Answer

The solution proposed above by Sai Kiriti Badam worked for me.

I'm using Azure Databricks to read data captured from an EventHub. This contains a string column named EnqueuedTimeUtc with the following format...

12/7/2018 12:54:13 PM

I'm using a Python notebook and used the following...

import pyspark.sql.functions as func

sports_messages = sports_df.withColumn("EnqueuedTimestamp", func.to_timestamp("EnqueuedTimeUtc", "MM/dd/yyyy hh:mm:ss aaa"))

... to create a new column EnqueuedTimestamp of type "timestamp" with data in the following format...

2018-12-07 12:54:13

User · Answer

Since your main aim was to convert the type of a column in a DataFrame from String to Timestamp  I think this approach would be better   import org apache spark sql functions  to date  to timestamp  val modifiedDF   DF withColumn  Date   to date   Date    MM dd yyyy      You could also use to timestamp  I think this is available from Spark 2 x  if you require fine grained timestamp

User · Answer

You can also pass date format  df withColumn  Date  to date unix timestamp df col  your date column     your date format   cast  timestamp       For Example  import org apache spark sql functions   val df   sc parallelize Seq  06 Jul 2018    toDF  dateCol   df withColumn  Date  to date unix timestamp df col  dateCol     dd MMM yyyy   cast  timestamp

User · Answer

Use below function in PySpark to convert datatype into your required datatype  Here I m converting all the date datatype into the Timestamp column   def change dtype df       for name  dtype in df dtypes          if dtype     date               df   df withColumn name  col name  cast  timestamp        return df

User · Answer

Find the below-mentioned code  it might be helpful for you      val stringDate   spark sparkContext parallelize Seq  12 16 2019    toDF  StringDate                       val dateCoversion   stringDate withColumn  dateColumn   to date unix timestamp   StringDate    dd mm yyyy   cast  Timestamp                         dateCoversion show false   ---------- ----------   StringDate dateColumn   ---------- ----------   12 16 2019 2019-01-12   ---------- ----------

User · Answer

dateID is int column contains date in Int format  spark sql  SELECT from unixtime unix timestamp cast dateid as varchar 10     yyyymmdd     yyyy-mm-dd   from XYZ   show 50  false

User · Answer

You could simply do df withColumn  quot date quot   date format col  quot string quot    quot yyyy-MM-dd HH mm ss ssssss quot    show

User · Answer

I have personally found some errors in when using unix timestamp based date converstions from dd-MMM-yyyy format to yyyy-mm-dd  using spark 1 6  but this may extend into recent versions  Below I explain a way to solve the problem using java time that should work in all versions of spark   I ve seen errors when doing   from unixtime unix timestamp StockMarketClosingDate   dd-MMM-yyyy     yyyy-MM-dd   as FormattedDate   Below is code to illustrate the error  and my solution to fix it  First I read in stock market data  in a common standard file format       import sys process       import org apache spark sql SQLContext     import org apache spark sql functions udf     import org apache spark sql types  StructType  StructField  StringType  IntegerType  DateType      import sqlContext implicits        val EODSchema   StructType Array          StructField  Symbol                   StringType  true          1                StructField  Date                     StringType  true          2                StructField  Open                     StringType  true          3                StructField  High                     StringType  true          4         StructField  Low                      StringType  true          5         StructField  Close                    StringType  true          6         StructField  Volume                   StringType  true          7                 val textFileName     user feeds eoddata INDEX INDEX 19  csv          below is code to read using later versions of spark       val eoddata   spark read format  csv   option  sep        schema EODSchema  option  header    true   load textFileName           here is code to read using 1 6  via   com databricks spark-csv 2 10 1 2 0       val eoddata   sqlContext read                                 format  com databricks spark csv                                   option  header    true                                    Use first line of all files as header                                 option  delimiter                                         option  dateFormat    dd-MMM-yyyy   failed to work                                 schema EODSchema                                  load textFileName       eoddata registerTempTable  eoddata     And here is the date conversions having issues    sql  -- notice there are errors around the turn of the year Select      e Date as StringDate     cast from unixtime unix timestamp e Date   dd-MMM-yyyy     YYYY-MM-dd   as Date   as ProperDate     e Close from eoddata e where e Symbol    SPX IDX  order by cast from unixtime unix timestamp e Date   dd-MMM-yyyy     YYYY-MM-dd   as Date  limit 1000   A chart made in zeppelin shows spikes  which are errors     and here is the check that shows the date conversion errors      shows the unix timestamp conversion approach can create errors val result    sqlContext sql     Select errors   from       Select      t         substring t OriginalStringDate  8  11  as String Year yyyy        substring t ConvertedCloseDate  0  4   as Converted Date Year yyyy     from              Select                 Symbol                 cast from unixtime unix timestamp e Date   dd-MMM-yyyy     YYYY-MM-dd   as Date   as ConvertedCloseDate                 e Date as OriginalStringDate                 Close             from eoddata e             where e Symbol    SPX IDX        t    errors where String Year yyyy  lt  gt  Converted Date Year yyyy          df withColumn  tx date   to date unix timestamp   date    M dd yyyy   cast  timestamp       result registerTempTable  SPX   result cache   result show 100  result  org apache spark sql DataFrame    Symbol  string  ConvertedCloseDate  date  OriginalStringDate  string  Close  string  String Year yyyy  string  Converted Date Year yyyy  string  res53  result type    Symbol  string  ConvertedCloseDate  date  OriginalStringDate  string  Close  string  String Year yyyy  string  Converted Date Year yyyy  string   ------- ------------------ ------------------ ------- ---------------- ------------------------    Symbol ConvertedCloseDate OriginalStringDate   Close String Year yyyy Converted Date Year yyyy   ------- ------------------ ------------------ ------- ---------------- ------------------------   SPX IDX         1997-12-30        30-Dec-1996  753 85             1996                     1997   SPX IDX         1997-12-31        31-Dec-1996  740 74             1996                     1997   SPX IDX         1998-12-29        29-Dec-1997  953 36             1997                     1998   SPX IDX         1998-12-30        30-Dec-1997  970 84             1997                     1998   SPX IDX         1998-12-31        31-Dec-1997  970 43             1997                     1998   SPX IDX         1998-01-01        01-Jan-1999 1229 23             1999                     1998   ------- ------------------ ------------------ ------- ---------------- ------------------------  FINISHED      After this result  I switched to java time conversions with a UDF like this  which worked for me      now we will create a UDF that uses the very nice java time library to properly convert the silly stockmarket dates    start by importing the specific java time libraries that superceded the joda time ones import java time LocalDate import java time format DateTimeFormatter     now define a specific data conversion function we want  def fromEODDate  YourStringDate  String   String          val formatter   DateTimeFormatter ofPattern  dd-MMM-yyyy       var   retDate   LocalDate parse YourStringDate  formatter          this should return a proper yyyy-MM-dd date from the silly dd-MMM-yyyy formats        now we format this true local date with a formatter to the desired yyyy-MM-dd format      val retStringDate   retDate format DateTimeFormatter ISO LOCAL DATE      return retStringDate      Now I register it as a function for use in sql   sqlContext udf register  fromEODDate   fromEODDate   String     and check the results  and rerun test   val results   sqlContext sql         Select         e Symbol    as Symbol         e Date      as OrigStringDate         Cast fromEODDate e Date  as Date  as ConvertedDate         e Open         e High         e Low         e Close     from eoddata e     order by Cast fromEODDate e Date  as Date        results printSchema   results cache   results registerTempTable  results   results show 10  results  org apache spark sql DataFrame    Symbol  string  OrigStringDate  string  ConvertedDate  date  Open  string  High  string  Low  string  Close  string  root   -- Symbol  string  nullable   true    -- OrigStringDate  string  nullable   true    -- ConvertedDate  date  nullable   true    -- Open  string  nullable   true    -- High  string  nullable   true    -- Low  string  nullable   true    -- Close  string  nullable   true  res79  results type    Symbol  string  OrigStringDate  string  ConvertedDate  date  Open  string  High  string  Low  string  Close  string   -------- -------------- ------------- ------- ------- ------- -------     Symbol OrigStringDate ConvertedDate    Open    High     Low   Close   -------- -------------- ------------- ------- ------- ------- -------   ADVA IDX    01-Jan-1996    1996-01-01     364     364     364     364   ADVN IDX    01-Jan-1996    1996-01-01    1527    1527    1527    1527   ADVQ IDX    01-Jan-1996    1996-01-01    1283    1283    1283    1283   BANK IDX    01-Jan-1996    1996-01-01 1009 41 1009 41 1009 41 1009 41    BKX IDX    01-Jan-1996    1996-01-01   39 39   39 39   39 39   39 39   COMP IDX    01-Jan-1996    1996-01-01 1052 13 1052 13 1052 13 1052 13    CPR IDX    01-Jan-1996    1996-01-01   1 261   1 261   1 261   1 261   DECA IDX    01-Jan-1996    1996-01-01     205     205     205     205   DECN IDX    01-Jan-1996    1996-01-01     825     825     825     825   DECQ IDX    01-Jan-1996    1996-01-01     754     754     754     754   -------- -------------- ------------- ------- ------- ------- -------  only showing top 10 rows   which looks ok  and I rerun my chart  to see if there are errors spikes     As you can see  no more spikes or errors  I now use a UDF as I ve shown to apply my date format transformations to a standard yyyy-MM-dd format  and have not had spurious errors since   -

User · Answer

I solved the same problem without the temp table view and with dataframe functions   Of course I found that only one format works with this solution and that s yyyy-MM-DD   For example   val df   sc parallelize Seq  2016-08-26    toDF  Id   val df2   df withColumn  Timestamp    col  Id   cast  timestamp     val df3   df2 withColumn  Date    col  Id   cast  date      df3 printSchema  root   -- Id  string  nullable   true    -- Timestamp  timestamp  nullable   true    -- Date  date  nullable   true   df3 show   ---------- -------------------- ----------           Id            Timestamp       Date   ---------- -------------------- ----------   2016-08-26 2016-08-26 00 00     2016-08-26   ---------- -------------------- ----------    The timestamp of course has 00 00 00 0 as a time value

User · Answer

you can also do this query      sqlContext sql     select from unixtime unix timestamp  08 26 2016    MM dd yyyy     yyyy MM dd   as new format      show

User · Answer

Use to date with Java SimpleDateFormat   TO DATE CAST UNIX TIMESTAMP date   MM dd yyyy   AS TIMESTAMP     Example   spark sql       SELECT TO DATE CAST UNIX TIMESTAMP  08 26 2016    MM dd yyyy   AS TIMESTAMP   AS newdate      show     ----------           dt   ----------   2016-08-26   ----------

[apache-spark] Convert date from String to Date format in Dataframes

Examples related to apache-spark

Examples related to apache-spark-sql