Temporal Functions

Description

DATE

• Function

  Returns a SQL date parsed from string in form of yyyy-MM-dd.

• Description

  DATE DATE string

• Input parameters

  Parameter	Data Types	Parameters
  string	STRING	String in the SQL date format. Note that the string must be in the yyyy-MM-dd format. Otherwise, an error will be reported.

• Example
  • Test statement

    SELECT 
    	DATE "2021-08-19" AS `result`
    FROM
    	testtable;

  • Test Result

    result
    2021-08-19

TIME

• Function

  Returns a SQL time parsed from string in form of HH:mm:ss[.fff].

• Description

  TIME TIME string

• Input parameters

  Parameter	Data Types	Parameters
  string	STRING	Time Note that the string must be in the format of HH:mm:ss[.fff]. Otherwise, an error will be reported.

• Example
  • Test statement

    SELECT 
    	TIME "10:11:12" AS `result`,
            TIME "10:11:12.032" AS `result2`
    FROM
    	testtable;

  • Test result

    result	result2
    10:11:12	10:11:12.032

TIMESTAMP

• Function

  Converts the time string into timestamp. The time string format is yyyy-MM-dd HH:mm:ss[.fff]. The return value is of the TIMESTAMP(3) type.

• Description

  TIMESTAMP(3) TIMESTAMP string

• Input parameters

  Parameter	Data Types	Parameters
  string	STRING	Time Note that the string must be in the format of yyyy-MM-dd HH:mm:ss[.fff]. Otherwise, an error will be reported.

• Example
  • Test statement

    SELECT 
    	TIMESTAMP "1997-04-25 13:14:15" AS `result`,
            TIMESTAMP "1997-04-25 13:14:15.032" AS `result2`
    FROM
    	testtable;

  • Test result

    result	result2
    1997-04-25 13:14:15	1997-04-25 13:14:15.032

INTERVAL

• Function

  Parses an interval string.

• Description

  INTERVAL INTERVAL string range

• Input parameters

  Parameter	Data Types	Parameters
  string	STRING	Timestamp string used together with the range parameter. The string is in either of the following two formats: yyyy-MM for SQL intervals of months. An interval range might be YEAR or YEAR TO MONTH for intervals of months. dd hh:mm:ss.fff for SQL intervals of milliseconds. An interval range might be DAY, MINUTE, DAY TO HOUR, or DAY TO SECOND.
  range	INTERVAL	Interval range. This parameter is used together with the string parameter. Available values are as follows: YEAR, YEAR To Month, DAY, MINUTE, DAY TO HOUR and DAY TO SECOND.

• Example
  Test statement

  -- indicates that the interval is 10 days and 4 milliseconds.
  INTERVAL '10 00:00:00.004' DAY TO second
  -- The interval is 10 days.
  INTERVAL '10' 
  -- The interval is 2 years and 10 months.
  INTERVAL '2-10' YEAR TO MONTH

CURRENT_DATE

• Function

  Returns the current SQL time (yyyy-MM-dd) in the local time zone. The return value is of the DATE type.

• Description

  DATE CURRENT_DATE

• Input parameters

  None

• Example
  • Test statement

    SELECT 
    	CURRENT_DATE AS `result`
    FROM
    	testtable;

  • Test result

    result
    2021-10-28

CURRENT_TIME

• Function

  Returns the current SQL time (HH:mm:sss.fff) in the local time zone. The return value is of the TIME type.

• Description

  TIME CURRENT_TIME

• Input parameters

  None

• Example
  • Test statement

    SELECT 
    	CURRENT_TIME AS `result`
    FROM
    	testtable;

  • Test Result

    result
    08:29:19.289

CURRENT_TIMESTAMP

• Function

  Returns the current SQL timestamp in the local time zone. The return value is of the TIMESTAMP(3) type.

• Description

  TIMESTAMP(3) CURRENT_TIMESTAMP

• Input parameters

  None

• Example
  • Test statement

    SELECT 
    	CURRENT_TIMESTAMP AS `result`
    FROM
    	testtable;

  • Test Result

    result
    2021-10-28 08:33:51.606

LOCALTIME

• Function

  Returns the current SQL time in the local time zone. The return value is of the TIME type.

• Description

  TIME LOCALTIME

• Input parameters

  None

• Example
  • Test statement

    SELECT 
    	LOCALTIME AS `result`
    FROM
    	testtable;

  • Test Result

    result
    16:39:37.706

LOCALTIMESTAMP

• Function

  Returns the current SQL timestamp in the local time zone. The return value is of the TIMESTAMP(3) type.

• Description

  TIMESTAMP(3) LOCALTIMESTAMP

• Input parameters

  None

• Example
  • Test statement

    SELECT 
    	LOCALTIMESTAMP AS `result`
    FROM
    	testtable;

  • Test Result

    result
    2021-10-28 16:43:17.625

EXTRACT

• Function

  Returns a value extracted from the timeintervalunit part of temporal. The return value is of the BIGINT type.

• Description

  BIGINT EXTRACT(timeinteravlunit FROM temporal)

• Input parameters

  Parameter	Data Types	Parameters
  timeinteravlunit	TIMEUNIT	Time unit to be extracted from a time point or interval. The value can be YEAR, QUARTER, MONTH, WEEK, DAY, DOY, HOUR, MINUTE, SECOND.
  temporal	DATE/TIME/TIMESTAMP/INTERVAL	Time point or interval

  

  Do not specify a time unit that is not of any time points or intervals. Otherwise, the job fails to be submitted.

  For example, an error message is displayed when the following statement is executed because YEAR cannot be extracted from TIME.

  SELECT 
  	EXTRACT(YEAR FROM TIME '12:44:31' ) AS `result`
  FROM
  	testtable;

• Example
  • Test statement

    SELECT 
    	EXTRACT(YEAR FROM DATE '1997-04-25' ) AS `result`,
            EXTRACT(MINUTE FROM TIME '12:44:31') AS `result2`,
            EXTRACT(SECOND FROM TIMESTAMP '1997-04-25 13:14:15') AS `result3`,
            EXTRACT(YEAR FROM INTERVAL '2-10' YEAR TO MONTH) AS `result4`,
    FROM
    	testtable;

  • Test result

    result	result2	result3	result4
    1997	44	15	2

YEAR

• Function

  Returns the year from a SQL date date. The return value is of the BIGINT type.

• Description

  BIGINT YEAR(date)

• Input parameters

  Parameter	Data Types	Parameters
  date	DATE	SQL date

• Example
  • Test statement

    SELECT 
    	YEAR(DATE '1997-04-25' ) AS `result`
    FROM
    	testtable;

  • Test result

    result
    1997

QUARTER

• Function

  Returns the quarter of a year (an integer between 1 and 4) from a SQL date date. The return value is of the BIGINT type.

• Description

  BIGINT QUARTER(date)

• Input parameters

  Parameter	Data Types	Parameters
  date	DATE	SQL date

• Example
  • Test statement

    SELECT 
    	QUARTER(DATE '1997-04-25' ) AS `result`
    FROM
    	testtable;

  • Test result

    result
    2

MONTH

• Function

  Returns the month of a year (an integer between 1 and 12) from a SQL date date. The return value is of the BIGINT type.

• Description

  BIGINT MONTH(date)

• Input parameters

  Parameter	Data Types	Parameters
  date	DATE	SQL date

• Example
  • Test statement

    SELECT 
    	MONTH(DATE '1997-04-25' ) AS `result`
    FROM
    	testtable;

  • Test result

    result
    4

WEEK

• Function

  Returns the week of a year from a SQL date date. The return value is of the BIGINT type.

• Description

  BIGINT WEEK(date)

• Input parameters

  Parameter	Data Types	Parameters
  date	DATE	SQL date

• Example
  • Test statement

    SELECT 
    	WEEK(DATE '1997-04-25' ) AS `result`
    FROM
    	testtable;

  • Test result

    result
    17

DAYOFYEAR

• Function

  Returns the day of a year (an integer between 1 and 366) from SQL date date. The return value is of the BIGINT type.

• Description

  BIGINT DAYOFYEAR(date)

• Input parameters

  Parameter	Data Types	Parameters
  date	DATE	SQL date

• Example
  • Test statement

    SELECT 
    	DAYOFYEAR(DATE '1997-04-25' ) AS `result`
    FROM
    	testtable;

  • Test Result

    result
    115

DAYOFMONTH

• Function

  Returns the day of a month (an integer between 1 and 31) from a SQL date date. The return value is of the BIGINT type.

• Description

  BIGINT DAYOFMONTH(date)

• Input parameters

  Parameter	Data Types	Parameters
  date	DATE	SQL date

• Example
  • Test statement

    SELECT 
    	DAYOFMONTH(DATE '1997-04-25' ) AS `result`
    FROM
    	testtable;

  • Test Result

    result
    25

DAYOFWEEK

• Function

  Returns the day of a week (an integer between 1 and 7) from a SQL date date. The return value is of the BIGINT type.

  

  Note that the start day of a week is Sunday.

• Description

  BIGINT DAYOFWEEK(date)

• Input parameters

  Parameter	Data Types	Parameters
  date	DATE	SQL date

• Example
  • Test statement

    SELECT 
    	DAYOFWEEK(DATE '1997-04-25') AS `result`
    FROM
    	testtable;

  • Test Result

    result
    6

HOUR

• Function

  Returns the hour of a day (an integer between 0 and 23) from SQL timestamp timestamp. The return value is of the BIGINT type.

• Description

  BIGINT HOUR(timestamp)

• Input parameters

  Parameter	Data Types	Parameters
  timestamp	TIMESTAMP	SQL timestamp

• Example
  • Test statement

    SELECT 
    	HOUR(TIMESTAMP '1997-04-25 10:11:12') AS `result`
    FROM
    	testtable;

  • Test Result

    result
    10

MINUTE

• Function

  Returns the minute of an hour (an integer between 0 and 59) from a SQL timestamp. The return value is of the BIGINT type.

• Description

  BIGINT MINUTE(timestamp)

• Input parameters

  Parameter	Data Types	Parameters
  timestamp	TIMESTAMP	SQL timestamp

• Example
  • Test statement

    SELECT 
    	MINUTE(TIMESTAMP '1997-04-25 10:11:12') AS `result`
    FROM
    	testtable;

  • Test Result

    result
    11

SECOND

• Function

  Returns the second of an hour (an integer between 0 and 59) from a SQL timestamp. The return value is of the BIGINT type.

• Description

  BIGINT SECOND(timestamp)

• Input parameters

  Parameter	Data Types	Parameters
  timestamp	TIMESTAMP	SQL timestamp

• Example
  • Test statement

    SELECT 
    	SECOND(TIMESTAMP '1997-04-25 10:11:12') AS `result`
    FROM
    	testtable;

  • Test result

    result
    12

FLOOR

• Function

  Returns a value that rounds timepoint down to the time unit timeintervalunit.

• Description

  TIME/TIMESTAMP(3) FLOOR(timepoint TO timeintervalunit)

• Input parameters

  Parameter	Data Types	Parameters
  timepoint	TIMESTAMP/TIME	SQL time or SQL timestamp
  timeintervalunit	TIMEUNIT	Time unit. The value can be YEAR, QUARTER, MONTH, WEEK, DAY, DOY, HOUR, MINUTE, or SECOND.

• Example
  • Test statement

    SELECT 
    	FLOOR(TIME '13:14:15' TO MINUTE) AS `result`
            FLOOR(TIMESTAMP '1997-04-25 13:14:15' TO MINUTE) AS `result2`,
            FLOOR(TIMESTAMP '1997-04-25 13:14:15' TO MINUTE) AS `result3`
    FROM	testtable;

  • Test result

    message	message2	message3
    13:14	13:14	1997-04-25T13:14

CEIL

• Function

  Returns a value that rounds timepoint up to the time unit timeintervalunit.

• Description

  TIME/TIMESTAMP(3) CEIL(timepoint TO timeintervalunit)

• Input parameters

  Parameter	Data Types	Parameters
  timepoint	TIMESTAMP/TIME	SQL time or SQL timestamp
  timeintervalunit	TIMEUNIT	Time unit. The value can be YEAR, QUARTER, MONTH, WEEK, DAY, DOY, HOUR, MINUTE, or SECOND.

• Example
  • Test statement

    SELECT 
    	CEIL(TIME '13:14:15' TO MINUTE) AS `result`
            CEIL(TIMESTAMP '1997-04-25 13:14:15' TO MINUTE) AS `result2`,
            CEIL(TIMESTAMP '1997-04-25 13:14:15' TO MINUTE) AS `result3`
    FROM	testtable;

  • Test Result

    result	result2	result3
    13:15	13:15	1997-04-25T13:15

OVERLAPS

• Function

  Returns TRUE if two time intervals overlap; returns FALSE otherwise.

• Description

  BOOLEAN (timepoint1, temporal1) OVERLAPS (timepoint2, temporal2)

• Input parameters

  Parameter	Data Types	Parameters
  timepoint1/timepoint2	DATE/TIME/TIMESTAMP	Time point
  temporal1/temporal2	DATE/TIME/TIMESTAMP/INTERVAL	Time point or interval

  

  • (timepoint, temporal) is a closed interval.
  • The temporal can be of the DATE, TIME, TIMESTAMP, or INTERVAL type.
    • When th temporal is DATE, TIME, or TIMESTAMP, (timepoint, temporal) indicates an interval between timepoint and temporal. The temporal can be earlier than the value of timepoint, for example, (DATE '1997-04-25', DATE '1997-04-23').
    • When the temporal is INTERVAL, (timepoint, temporal) indicates an interval between timepoint and timepoint + temporal.
  • Ensure that (timepoint1, temporal1) and (timepoint2, temporal2) are intervals of the same data type.
• Example
  • Test statement

    SELECT 
    	(TIME '2:55:00', INTERVAL '1' HOUR) OVERLAPS (TIME '3:30:00', INTERVAL '2' HOUR) AS `result`,
            (TIME '2:30:00', INTERVAL '1' HOUR) OVERLAPS (TIME '3:30:00', INTERVAL '2' HOUR) AS `result2`,
    	(TIME '2:30:00', INTERVAL '1' HOUR) OVERLAPS (TIME '3:31:00', INTERVAL '2' HOUR) AS `result3`,
    	(TIME '9:00:00', TIME '10:00:00') OVERLAPS (TIME '10:00:00', INTERVAL '3' HOUR) AS `result4`,
    	(TIMESTAMP '1997-04-25 12:00:00', TIMESTAMP '1997-04-25 12:20:00') OVERLAPS (TIMESTAMP '1997-04-25 13:00:00', INTERVAL '2' HOUR) AS `result5`,
    	(DATE '1997-04-23', INTERVAL '2' DAY) OVERLAPS (DATE '1997-04-25', INTERVAL '2' DAY) AS `result6`,
    	(DATE '1997-04-25', DATE '1997-04-23') OVERLAPS (DATE '1997-04-25', INTERVAL '2' DAY) AS `result7`
    FROM
    	testtable;

  • Test Result

    result	result2	result3	result4	result5	result6	result7
    true	true	false	true	false	true	true

DATE_FORMAT

• Function

  Converts a timestamp to a value of string in the format specified by the date format string.

• Description

  STRING DATE_FORMAT(timestamp, dateformat)

• Input parameters

  Parameter	Data Types	Parameters
  timestamp	TIMESTAMP/STRING	Time point
  dateformat	STRING	String in the date format

• Example
  • Test statement

    SELECT 
    	DATE_FORMAT(TIMESTAMP '1997-04-25 10:11:12', 'yyyy-MM-dd HH:mm:ss') AS `result`,
            DATE_FORMAT(TIMESTAMP '1997-04-25 10:11:12', 'yyyy-MM-dd') AS `result2`,
    	DATE_FORMAT(TIMESTAMP '1997-04-25 10:11:12', 'yy/MM/dd HH:mm') AS `result3`,
            DATE_FORMAT('1997-04-25 10:11:12', 'yyyy-MM-dd') AS `result4`
    FROM	testtable;

  • Test Result

    result	result2	result3	result4
    1997-04-25 10:11:12	1997-04-25	97/04/25 10:11	1997-04-25

TIMESTAMPADD

• Function

  Returns the date and time by combining interval and timeintervalunit and adding the combination to timepoint.

  

  The return value of TIMESTAMPADD is the value of timepoint. An exception is that if the input timepoint is of the TIMESTAMP type, the return value can be inserted into a table field of the DATE type.

• Description

  TIMESTAMP(3)/DATE/TIME TIMESTAMPADD(timeintervalunit, interval, timepoint)

• Input parameters

  Parameter	Data Types	Parameters
  timeintervalunit	TIMEUNIT	Time unit.
  interval	INT	Interval
  timepoint	TIMESTAMP/DATE/TIME	Time point

• Example
  • Test statement

    SELECT 
    	TIMESTAMPADD(WEEK, 1, DATE '1997-04-25') AS `result`,
            TIMESTAMPADD(QUARTER, 1, TIMESTAMP '1997-04-25 10:11:12') AS `result2`,
    	TIMESTAMPADD(SECOND, 2, TIME '10:11:12') AS `result3`
    FROM	testtable;

  • Test Result

    result	result2	result3
    1997-05-02	If this field is inserted into a table field of the TIMESTAMP type, 1997-07-25T10:11:12 is returned. If this field is inserted into a table field of the TIMESTAMP type, 1997-07-25 is returned.	10:11:14

TIMESTAMPDIFF

• Function

  Returns the (signed) number of timepointunit between timepoint1 and timepoint2. The unit for the interval is given by the first argument.

• Description

  INT TIMESTAMPDIFF(timepointunit, timepoint1, timepoint2)

• Input parameters

  Parameter	Data Types	Parameters
  timepointunit	TIMEUNIT	Time unit. The value can be SECOND, MINUTE, HOUR, DAY, MONTH or YEAR.
  timepoint1/timepoint2	TIMESTAMP/DATE	Time point

• Example
  • Test statement

    SELECT 
    	TIMESTAMPDIFF(DAY, TIMESTAMP '1997-04-25 10:00:00', TIMESTAMP '1997-04-28 10:00:00') AS `result`,
            TIMESTAMPDIFF(DAY, DATE '1997-04-25', DATE '1997-04-28') AS `result2`,
    	TIMESTAMPDIFF(DAY, TIMESTAMP '1997-04-27 10:00:20', TIMESTAMP '1997-04-25 10:00:00') AS `result3`
    FROM	testtable;

  • Test result

    result	result2	result3
    3	3	-2

CONVERT_TZ

• Function

  Converts a datetime string1 (with default ISO timestamp format 'yyyy-MM-dd HH:mm:ss') from time zone string2 to time zone string3.

• Description

  STRING CONVERT_TZ(string1, string2, string3)

• Input parameters

  Parameter	Data Types	Parameters
  string1	STRING	SQL timestamp. If the value does not meet the format requirements, NULL is returned.
  string2	STRING	Time zone before conversion. The format of time zone should be either an abbreviation such as PST, a full name such as America/Los_Angeles, or a custom ID such as GMT-08:00.
  string3	STRING	Time zone after conversion. The format of time zone should be either an abbreviation such as PST, a full name such as America/Los_Angeles, or a custom ID such as GMT-08:00.

• Example
  • Test statement

    SELECT 
    	CONVERT_TZ(1970-01-01 00:00:00, UTC, America/Los_Angeles) AS `result`,
            CONVERT_TZ(1997-04-25 10:00:00, UTC, GMT-08:00) AS `result2`
    FROM	testtable;

  • Test Result

    result	result2
    1969-12-31 16:00:00	1997-04-25 02:00:00

FROM_UNIXTIME

• Function

  Returns a representation of the numeric argument as a value in string format.

• Description

  STRING FROM_UNIXTIME(numeric[, string])

• Input parameters

  Parameter	Data Types	Parameters
  numeric	BIGINT	An internal timestamp representing the number of seconds since 1970-01-01 00:00:00 UTC. The value can be generated by the UNIX_TIMESTAMP() function.
  string	STRING	Time. If this parameter is not specified, the default time format is yyyy-MM-dd HH:mm:ss format.

• Example
  • Test statement

    SELECT 
    	FROM_UNIXTIME(44) AS `result`,
            FROM_UNIXTIME(44, 'yyyy:MM:dd') AS `result2`
    FROM	testtable;

  • Test Result

    result	result2
    1970-01-01 08:00:44	1970:01:01

UNIX_TIMESTAMP

• Function

  Gets current Unix timestamp in seconds. The return value is of the BIGINT type.

• Description

  BIGINT UNIX_TIMESTAMP()

• Input parameters

  None

• Example
  • Test statement

    SELECT 
    	UNIX_TIMESTAMP() AS `result`
    FROM
    	table;

  • Test result

    result
    1635401982

UNIX_TIMESTAMP(string1[, string2])

• Function

  Converts date time string1 in format string2 to Unix timestamp (in seconds). The return value is of the BIGINT type.

• Description

  BIGINT UNIX_TIMESTAMP(string1[, string2])

• Input parameters

  Parameter	Data Types	Parameters
  string1	STRING	SQL timestamp string. An error is reported if the value does not comply with the string2 format.
  string2	STRING	Time. If this parameter is not specified, the default time format is yyyy-MM-dd HH:mm:ss.

• Example
  • Test statement

    SELECT 
    	UNIX_TIMESTAMP('1997-04-25', 'yyyy-MM-dd') AS `result`,
            UNIX_TIMESTAMP('1997-04-25 00:00:10', 'yyyy-MM-dd HH:mm:ss') AS `result2`,
            UNIX_TIMESTAMP('1997-04-25 00:00:00') AS `result3`
    FROM
    	testtable;

  • Test result

    result	result2	result3
    861897600	861897610	861897600

TO_DATE

• Function

  Converts a date string1 with format string2 to a date.

• Description

  DATE TO_DATE(string1[, string2])

• Input parameters

  Parameter	Data Types	Parameters
  string1	STRING	SQL timestamp string. If the value is not in the required format, an error is reported.
  string2	STRING	Format. If this parameter is not specified, the default time format is yyyy-MM-dd.

• Example
  • Test statement

    SELECT 
    	TO_DATE('1997-04-25') AS `result`,
            TO_DATE('1997:04:25', 'yyyy-MM-dd') AS `result2`,
            TO_DATE('1997-04-25 00:00:00', 'yyyy-MM-dd HH:mm:ss') AS `result3`
    FROM
    	testtable;

  • Test result

    result	result2	result3
    1997-04-25	1997-04-25	1997-04-25

TO_TIMESTAMP

• Function

  Converts date time string1 with format string2 to a timestamp.

• Description

  TIMESTAMP TO_TIMESTAMP(string1[, string2])

• Input parameters

  Parameter	Data Types	Parameters
  string1	STRING	SQL timestamp string. If the value is not in the required format, NULL is returned.
  string2	STRING	Date format. If this parameter is not specified, the default format is yyyy-MM-dd HH:mm:ss.

• Example
  • Test statement

    SELECT 
    	TO_TIMESTAMP('1997-04-25', 'yyyy-MM-dd') AS `result`,
            TO_TIMESTAMP('1997-04-25 00:00:00') AS `result2`,
            TO_TIMESTAMP('1997-04-25 00:00:00', 'yyyy-MM-dd HH:mm:ss') AS `result3`
    FROM
    	testtable;

  • Test result

    result	result2	result3
    1997-04-25 00:00	1997-04-25 00:00	1997-04-25 00:00