How do I convert column of unix epoch to Date in Apache spark DataFrame using Java?

Question

I have a json data file which contain one property [creationDate] which is unix epoc in "long" number type. The Apache Spark DataFrame schema look like below:

root 
 |-- creationDate: long (nullable = true) 
 |-- id: long (nullable = true) 
 |-- postTypeId: long (nullable = true)
 |-- tags: array (nullable = true)
 |    |-- element: string (containsNull = true)
 |-- title: string (nullable = true)
 |-- viewCount: long (nullable = true)

I would like to do some groupBy "creationData_Year" which need to get from "creationDate".

What's the easiest way to do this kind of convert in DataFrame using Java?

Anurag · Accepted Answer · 2023-06-16 07:12:14Z

16

After checking spark dataframe api and sql function, I come out below snippet:

DateFrame df = sqlContext.read().json("MY_JSON_DATA_FILE");

DataFrame df_DateConverted = df.withColumn("creationDt", from_unixtime(df.col("creationDate").divide(1000)));

The reason why "creationDate" column is divided by "1000" is cause the TimeUnit is different. The orgin "creationDate" is unix epoch in "milli-second", however spark sql "from_unixtime" is designed to handle unix epoch in "second".

edited Jun 16, 2023 at 7:12

Anurag

4512 silver badges13 bronze badges

answered Jan 6, 2016 at 5:50

ErhWen Kuo

1,4971 gold badge14 silver badges18 bronze badges

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

4 Comments

Leet-Falcon Over a year ago

Your original issue was regarding the "group-by" granularity of long creationDate ?

ErhWen Kuo Over a year ago

Yes, I would like to group-by "Year" & "Month" of "creationDate", then do some aggregation.

Leet-Falcon Over a year ago

So what was wrong with grouping by original creationDate column?

ErhWen Kuo Over a year ago

Because the origin data type in JSON is "long" (unix epoc) , I need to convert this field to "Year" and "Month", for example: 1452066042000 need to convert to "2016" for "createDate_Year" column and "1" for "createDate_Month" column. This way, I can utilize spark df.groupBy() and some other aggregation function to computing.

Ray Metz · Accepted Answer · 2018-05-23 00:37:01Z

8

pyspark converts from Unix epoch milliseconds to dataframe timestamp

df.select(from_unixtime((df.my_date_column.cast('bigint')/1000)).cast('timestamp').alias('my_date_column'))

answered May 23, 2018 at 0:37

Ray Metz

811 silver badge1 bronze badge

Comments

Ganesh · Accepted Answer · 2020-07-20 07:35:27Z

5

In spark scala,

spark.sql("select from_unixtime(1593543333062/1000) as ts").show(false)

answered Jul 20, 2020 at 7:35

Ganesh

7679 silver badges13 bronze badges

Collectives™ on Stack Overflow

How do I convert column of unix epoch to Date in Apache spark DataFrame using Java?

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related