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

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.