Spark SQL - Date/Timestamp Conversation from/to UNIX Date/Timestamp

event 2022-06-04 thumb_up 0 visibility 4,362 comment 0 insights toc

more_vert

warning Please login first to view stats information.

UNIX timestamp to Spark timestamp via timestamp_seconds
Timestamp to UNIX timestamp via unix_seconds
UNIX date to Spark date via date_from_unix_date
Date to UNIX date via unix_date
Function to_unix_timestamp
Function from_unixtime
Other functions

UNIX timestamp is an integer that represents the seconds since UTC epoch (Jan 01 1970). Similarly, UNIX date is an integer the represents the days since UTC epoch. Spark provides a number of functions that can be used to convert UNIX timestamp or date to Spark timestamp or date, vice versa.

infoSome of the following functions are only available from Spark 3.1.0.

UNIX timestamp to Spark timestamp via timestamp_seconds

spark-sql> select timestamp_seconds(1);
1970-01-01 00:00:01

Timestamp to UNIX timestamp via unix_seconds

spark-sql> select unix_seconds(TIMESTAMP('1970-01-01 00:00:01Z'));
 1

UNIX date to Spark date via date_from_unix_date

spark-sql>select date_from_unix_date(1);
 1970-01-02

Date to UNIX date via unix_date

spark-sql> select unix_date(DATE("1970-01-03"));
 2

Function to_unix_timestamp

Function to_unix_timestamp(timeExp[, fmt]) can be used to return the UNIX timestamp of the given time based on the format. This function is available from Spark 1.6.0.

The following are some examples to convert Spark date or timestamp to UNIX timestamp (seconds).

spark-sql> select to_unix_timestamp(date'2022-01-01','yyyy-MM-dd');
to_unix_timestamp(DATE '2022-01-01', yyyy-MM-dd)
1640955600

spark-sql> select to_unix_timestamp('2022-01-01 16:30:05','yyyy-MM-dd HH:mm:ss');
to_unix_timestamp(2022-01-01 16:30:05, yyyy-MM-dd HH:mm:ss)
1641015005

spark-sql> select to_unix_timestamp('2022-01-01 16:30:05.630','yyyy-MM-dd HH:mm:ss.SSS');
to_unix_timestamp(2022-01-01 16:30:05.630, yyyy-MM-dd HH:mm:ss.SSS)
1641015005

Function from_unixtime

Function from_unixtime(unix_time[, fmt]) converts UNIX timestamp in the specified format.

spark-sql> select from_unixtime(1641015005, 'yyyy-MM-dd HH:mm:ss');
from_unixtime(CAST(1641015005 AS BIGINT), yyyy-MM-dd HH:mm:ss)
2022-01-01 16:30:05

Other functions

There are also some other functions available to convert from or to UNIX microseconds and milliseconds.

timestamp_micros: creates timestamp from the number of microseconds since UTC epoch.
timestamp_millis: creates timestamp from the number of milliseconds since UTC epoch.
unix_micros: returns the number of microseconds since 1970-01-01 00:00:00 UTC.
unix_millis: returns the number of milliseconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision.