Skip to main content
Last updated on

make_timestamp function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Creates a timestamp from year, month, day, hour, min, sec, and timezone fields.

Syntax​

make_timestamp(year, month, day, hour, min, sec [, timezone] )

Arguments​

  • year: An INTEGER expression evaluating to a value from 1 to 9999.
  • month: An INTEGER expression evaluating to a value from 1 (January) to 12 (December).
  • day: An INTEGER expression evaluating to a value from 1 to 31.
  • hour: An INTEGER expression evaluating to a value between 0 and 23.
  • min: An INTEGER expression evaluating to a value between 0 and 59.
  • sec: A numeric expression evaluating to a value between 0 and 60.
  • timezone: An optional STRING expression evaluating to a valid timezone string. For example: CET, UTC.

Returns​

A TIMESTAMP.

In Databricks SQL, if any of the arguments is out of bounds, the result is NULL. In Databricks Runtime, if any of the arguments are out of bounds, Databricks raises DATETIME_FIELD_OUT_OF_BOUNDS unless spark.sql.ansi.enabled is false, in which case the function returns NULL. If sec is 60 it is interpreted as 0 and a minute is added to the result.

If sec has a fractional value that cannot be represented in microsecond precision, Databricks raises INVALID_FRACTION_OF_SECOND.

Common error conditions​

Examples​

SQL
> SELECT make_timestamp(2014, 12, 28, 6, 30, 45.887);
 2014-12-28 06:30:45.887
> SELECT make_timestamp(2014, 12, 28, 6, 30, 45.887, 'CET');
 2014-12-27 21:30:45.887
> SELECT make_timestamp(2019, 6, 30, 23, 59, 60);
 2019-07-01 00:00:00
> SELECT make_timestamp(2019, 13, 1, 10, 11, 12, 'PST');
 NULL
> SELECT make_timestamp(NULL, 7, 22, 15, 30, 0);
 NULL

-- In DBR with ANSI mode, out-of-bounds fields raise an error.
> SELECT make_timestamp(2024, 13, 1, 0, 0, 0);
  Error: DATETIME_FIELD_OUT_OF_BOUNDS

Related functions​

On this page