Datetime Literals

Datetime literals capture specific dates or timestamps.

Date Syntax

DATE 'yyyy'DATE 'yyyy-m[m]'DATE 'yyyy-m[m]-d[d]'DATE 'yyyy-m[m]-d[d]T'

y, m, and d represent a digit (0-9) for the year, month, and day, respectively. The month or day defaults to 01 if not specified.

Date Examples

SELECT DATE '2001' AS col;
-- +----------+
-- |       col|
-- +----------+
-- |2001-01-01|
-- +----------+

SELECT DATE '2005-07' AS col;
-- +----------+
-- |       col|
-- +----------+
-- |2005-07-01|
-- +----------+

SELECT DATE '2019-12-25' AS col;
-- +----------+
-- |       col|
-- +----------+
-- |2019-12-25|
-- +----------+

Timestamp Syntax

TIMESTAMP 'yyyy'TIMESTAMP 'yyyy-m[m]'TIMESTAMP 'yyyy-m[m]-d[d]'TIMESTAMP 'yyyy-m[m]-d[d](T| )H[H]'TIMESTAMP 'yyyy-m[m]-d[d](T| )H[H]:M[M]'TIMESTAMP 'yyyy-m[m]-d[d](T| )H[H]:M[M]:S[S][.fraction][zone]'

y, m, d, H, M, and S represent a digit (0-9) for the year, month, day, hour, minute, and second, respectively. The month and day default to 01 if not specified. The hour, minute, and second default to 00 if not specified.

fraction, if specified, is the fractional seconds part that can have from 1 to 6 decimal digits (0-9), representing microsecond precision. If fewer than 6 digits are provided, the value is padded on the right with zeros to reach microsecond precision.

zone represents the time zone and should have one of the forms:

• Z (Zulu time zone UTC+0)
• (+|-)H[H]:M[M]
• A string with one of the prefixes UTC+, UTC-, GMT+, GMT-, UT+ or UT-, and a suffix in the following forms:
  • (+|-)H[H]
  • (+|-)HH[:]MM
  • (+|-)HH:MM:SS
  • (+|-)HHMMSS
• A time zone name in the form area/city, such as Europe/Paris

The time zone defaults to the session local timezone (set via spark.sql.session.timeZone) if not specified.

Timestamp Examples

SELECT TIMESTAMP '2000-02-29 23:59:59.123' AS col;
-- +-----------------------+
-- |                    col|
-- +-----------------------+
-- |2000-02-29 23:59:59.123|
-- +-----------------------+

SELECT TIMESTAMP '2015-06-30 12:00:00.999999UTC-05:00' AS col;
-- +--------------------------+
-- |                      col |
-- +--------------------------+
-- |2015-06-30 17:00:00.999999|
-- +--------------------------+

SELECT TIMESTAMP '2010-08' AS col;
-- +-------------------+
-- |                col|
-- +-------------------+
-- |2010-08-01 00:00:00|
-- +-------------------+