Format timestamp value using Spark Dataframe API [duplicate]

Question

I have a Dataframe with a date column representing Unix timestamp in String format. The column values need to be formatted to a different String representation as below -

Input Dataframe

+----+----+-------------+
|name|code|         date|
+----+----+-------------+
|   A|   1|1545905416000|
|   B|   3|1545905416000|
|   C|   5|1545905416000|
+----+----+-------------+

Expected output Dataframe

+----+----+-------------+
|name|code|         date|
+----+----+-------------+
|   A|   1|   2018-12-27|
|   B|   3|   2018-12-27|
|   C|   5|   2018-12-27|
+----+----+-------------+

This didn't work as it is giving null for all values -

 peopleDFCsv.withColumn("formatted_date", 
            functions.date_format(functions.col("date"), "yyyy-MM-dd"))
            .show();

Or Scala: Spark SQL to_date(unix_timestamp) returning NULL

10465355
– 10465355

2018-12-27 12:20:53 +00:00
Commented Dec 27, 2018 at 12:20 — 10465355
– 10465355, Commented Dec 27, 2018 at 12:20

Frank Wilson · Accepted Answer · 2018-12-27 12:09:48Z

2

The date_format function works with timestamp not milliseconds since epoch. Try doing the conversion with CAST:

df.withColumn("formatted_date",
  functions.date_format(expr("CAST(date/1000 AS TIMESTAMP)"), "yyyy-MM-dd"))
  .show()

//    Outputs:
//    +----+----+-------------+--------------+
//    |name|code|         date|formatted_date|
//    +----+----+-------------+--------------+
//    |   A|   1|1545905416000|    2018-12-27|
//    |   B|   3|1545905416000|    2018-12-27|
//    |   C|   5|1545905416000|    2018-12-27|
//    +----+----+-------------+--------------+

The reason why you are getting null is because date_format is interpreting your string as a timestamp such as "2018-12-27 11:10:16". The plain number of does not conform to the format it expects so it just returns null.

edited Dec 27, 2018 at 12:09

answered Dec 27, 2018 at 12:03

Frank Wilson

3,3101 gold badge23 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Format timestamp value using Spark Dataframe API [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related