Kontext Kontext / Code Snippets & Tips

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

event 2022-06-04 visibility 4,524 comment 0 insights toc
insights Stats
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'));

UNIX date to Spark date via date_from_unix_date

spark-sql>select date_from_unix_date(1);

Date to UNIX date via unix_date

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

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)

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)

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)

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.
More from Kontext
comment Comments
No comments yet.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts