Temporal functions SQL Function Description DATE string Returns a SQL date parsed from string in the form yyyy-MM-dd. SELECT DATE '2024-01-01', TYPEOF(DATE '2024-01-01'); +------------+---------------+ | EXPR$0 | EXPR$1 | +------------+---------------+ | 2024-01-01 | DATE NOT NULL | +------------+---------------+ TIME string Returns a SQL time parsed from string in the form HH:mm:ss. SELECT TIME '00:00:00', TYPEOF(TIME '00:00:00'); +----------+------------------+ | EXPR$0 | EXPR$1 | +----------+------------------+ | 00:00:00 | TIME(0) NOT NULL | +----------+------------------+ TIMESTAMP string Returns a SQL timestamp parsed from a string yyyy-MM-dd HH:mm:ss. SELECT TIMESTAMP '2024-01-01 00:00:00', TYPEOF(TIMESTAMP '2024-01-01 00:00:00'); +---------------------+-----------------------+ | EXPR$0 | EXPR$1 | +---------------------+-----------------------+ | 2024-01-01 00:00:00 | TIMESTAMP(0) NOT NULL | +---------------------+-----------------------+ INTERVAL string range Returns an interval which can be used with other datetime functions. Simple values for range are: SECOND / SECONDS MINUTE / MINUTES HOUR / HOURS DAY / DAYS MONTH / MONTHS YEAR / YEARS For example: -- Add five days SELECT TIMESTAMP '2024-01-01 00:00:00' + INTERVAL '5' DAYS; +---------------------+ | EXPR$0 | +---------------------+ | 2024-01-06 00:00:00 | +---------------------+ You can also combine ranges with TO in order to specify more complex intervals. In this case, string should be specified as follows: dd hh:mm:ss.fff (for SQL intervals of milliseconds up to days) yyyy-mm (for SQL intervals of months and years). For example: -- Add five days and 42 minutes SELECT TIMESTAMP '2024-01-01 00:00:00.000' + INTERVAL '5 00:42' DAY TO MINUTES; +-------------------------+ | EXPR$0 | +-------------------------+ | 2024-01-06 00:42:00.000 | +-------------------------+ -- Add five days, one hour, and 42 milliseconds SELECT TIMESTAMP '2024-01-01 00:00:00.000' + INTERVAL '5 01:00:00.042' DAY TO SECOND; +-------------------------+ | EXPR$0 | +-------------------------+ | 2024-01-06 01:00:00.042 | +-------------------------+ -- Add five years and one month SELECT TIMESTAMP '2024-01-01 00:00:00.000' + INTERVAL '5-1' YEAR TO MONTH; +-------------------------+ | EXPR$0 | +-------------------------+ | 2029-02-01 00:00:00.000 | +-------------------------+ YEAR(date) Returns the year value of date SELECT YEAR(DATE '2024-01-01'), TYPEOF(YEAR(DATE '2024-01-01')); +--------+-----------------+ | EXPR$0 | EXPR$1 | +--------+-----------------+ | 2024 | BIGINT NOT NULL | +--------+-----------------+ LOCALTIME CURRENT_TIME Returns the current SQL time in the local time zone. LOCALTIMESTAMP CURRENT_TIMESTAMP CURRENT_ROW_TIMESTAMP() NOW() Returns the current SQL timestamp in local time zone. CURRENT_DATE Returns the current SQL date in the local time zone. EXTRACT(timeintervalunit FROM temporal) Returns a long value extracted from the timeintervalunit part of temporal. Values for timeintervalunit are: NANOSECOND MICROSECOND MILLISECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR DECADE CENTURY MILLENNIUM DOW DOY ISODOW ISOYEAR EPOCH For example: Returns EXTRACT(YEAR FROM DATE '2024-01-01') 2024 YEAR(date) Returns the year from SQL date date. Equivalent to EXTRACT(YEAR FROM date). For example: Returns YEAR(DATE '2024-01-01') 2024 QUARTER(date) Returns the quarter of a year (an integer between 1 and 4) from SQL date date. Equivalent to EXTRACT(QUARTER FROM date). For example: Returns QUARTER(DATE '2024-06-01') 2 MONTH(date) Returns the month of a year (an integer between 1 and 12) from SQL date date. Equivalent to EXTRACT(MONTH FROM date). For example: Returns MONTH(DATE '2024-06-01') 6 WEEK(date) Returns the week of a year (an integer between 1 and 53) from SQL date date. Equivalent to EXTRACT(WEEK FROM date). For example: Returns WEEK(DATE '2024-06-01') 22 DAYOFYEAR(date) Returns the day of a year (an integer between 1 and 366) from SQL date date. Equivalent to EXTRACT(DOY FROM date). For example: Returns DAYOFYEAR(DATE '2024-06-01') 153 DAYOFMONTH Returns the day of a month (an integer between 1 and 31) from SQL date date. Equivalent to EXTRACT(DAY FROM date). For example: Returns DAYOFWEEK(DATE '2024-06-01') 7 HOUR(timestamp) Returns the hour of a day (an integer between 0 and 23) from SQL timestamp timestamp. Equivalent to EXTRACT(HOUR FROM timestamp). For example: Returns HOUR(TIMESTAMP '2024-06-01 00:42:42') 0 MINUTE(timestamp) Returns the minute of an hour (an integer between 0 and 59) from SQL timestamp timestamp. Equivalent to EXTRACT(MINUTE FROM timestamp). For example: Returns MINUTE(TIMESTAMP '2024-06-01 00:42:42') 42 SECOND(timestamp) Returns the second of a minute (an integer between 0 and 59) from SQL timestamp timestamp. Equivalent to EXTRACT(SECOND FROM timestamp). For example: Returns SECOND(TIMESTAMP '2024-06-01 00:42:42') 42 FLOOR(timepoint TO timeintervalunit) Returns a value that rounds timepoint down to the time unit timeintervalunit. For example: Returns FLOOR(TIME '00:42:42' TO MINUTE) 00:42:00 CEIL(timepoint TO timeintervalunit) Returns a value that rounds timepoint up to the time unit timeintervalunit. For example: Returns CEIL(TIME '00:42:42' TO MINUTE) 00:43:00 (timepoint1, temporal1) OVERLAPS (timepoint2, temporal2) Returns TRUE if two time intervals defined by (timepoint1, temporal1) and (timepoint2, temporal2) overlap. The temporal values can be either a time point or a time interval. For example: Returns (TIME '2:55:00', INTERVAL '1' HOUR) OVERLAPS (TIME '3:30:00', INTERVAL '2' HOUR) TRUE (TIME '9:00:00', TIME '10:00:00') OVERLAPS (TIME '10:15:00', INTERVAL '3' HOUR) FALSE DATE_FORMAT(timestamp, string) Converts timestamp to a value of string in the format specified by the date format string. The format string is compatible with Java’s SimpleDateFormat. TIMESTAMPADD(timeintervalunit, interval, timepoint) Adds the interval in timeintervalunit units to the specified timepoint Values for timeintervalunit can be: SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR For example: Returns TIMESTAMPADD(WEEK, 1, DATE '2024-06-01') 2024-06-08 TIMESTAMPADD(SECONDS, 4242, TIME '00:00:00') 01:10:42 TIMESTAMPADD(MINUTE, 42, TIMESTAMP '2024-01-01 00:00:00') 2024-01-01 00:42:00 TIMESTAMPDIFF(timepointunit, timepoint1, timepoint2) Returns the (signed) number of timepointunit between timepoint1 and timepoint2. The unit for the interval is given by the first argument, which should be one of the following values: SECOND, MINUTE, HOUR, DAY, MONTH, or YEAR. For example: Returns TIMESTAMPDIFF(MINUTE, TIMESTAMP '2024-01-01 00:00:00', TIMESTAMP '2024-01-01 00:42:20') 42 CONVERT_TZ(string1, string2, string3) Converts a datetime string1 (with default ISO timestamp format yyyy-MM-dd HH:mm:ss) from time zone string2 to time zone string3. The format of time zone should be either an abbreviation such as PST, a full name such as Europe/London, or a custom ID such as GMT-08:00. For example: Returns CONVERT_TZ('2024-06-01 00:00:00', 'PST', 'Europe/London') 2024-06-01 08:00:00 FROM_UNIXTIME(numeric[, string]) Returns a representation of the numeric argument as a value in string format (default is yyyy-MM-dd HH:mm:ss). numeric is an internal timestamp value representing seconds since the Unix epoch (1970-01-01 00:00:00 UTC). The return value is expressed in the session time zone. UNIX_TIMESTAMP() Gets current Unix timestamp in seconds. This function isn’t deterministic which means the value would be recalculated for each record. UNIX_TIMESTAMP(string1[, string2]) Converts date time string string1 in format string2 (default: yyyy-MM-dd HH:mm:ss) to Unix timestamp (in seconds). TO_DATE(string1[, string2]) Converts a date string string1 with format string2 (default 'yyyy-MM-dd') to a DATE. TO_TIMESTAMP_LTZ(numeric, precision) Converts numeric epoch seconds or epoch milliseconds to a TIMESTAMP_LTZ. Value for precision is 0 (numeric is in seconds) or 3 (numeric is in milliseconds). TO_TIMESTAMP(string1[, string2]) Converts date time string string1 with format string2 (default: 'yyyy-MM-dd HH:mm:ss') to a TIMESTAMP.