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, the function returns an error 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.

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