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] )

make_timestamp(date [, time [, 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.
  • date: A DATE expression providing the year, month, and day fields.
  • time: An optional TIME expression providing the hour, minute, and second fields. Defaults to 00:00:00 if omitted.
  • 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, Azure 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, Azure Databricks raises INVALID_FRACTION_OF_SECOND.

Common error conditions

Examples

> 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

> SELECT make_timestamp(DATE'2014-12-28', TIME'06:30:45.887');
 2014-12-28 06:30:45.887

> SELECT make_timestamp(DATE'2014-12-28', TIME'06:30:45.887', 'CET');
 2014-12-27 21:30:45.887

> SELECT make_timestamp(DATE'2014-12-28');
 2014-12-28 00:00:00

-- 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
  • Last updated on