Updated on 2023-03-17 GMT+08:00

Temporal Functions

Table 1 lists the temporal functions supported by Flink OpenSource SQL.

Function Description

Table 1 Temporal functions

Function

Return Type

Description

DATE string

DATE

Parse the date string (yyyy-MM-dd) to a SQL date.

TIME string

TIME

Parse the time string (HH:mm:ss[.fff]) to a SQL time.

TIMESTAMP string

TIMESTAMP

Convert the time string into a timestamp. The time string format is yyyy-MM-dd HH:mm:ss[.fff].

INTERVAL string range

INTERVAL

Parse an interval string in the following two forms:

  • yyyy-MM for SQL intervals of months. An interval range might be YEAR or YEAR TO MONTH.
  • dd hh:mm:ss.fff for SQL intervals of milliseconds. An interval range might be DAY, MINUTE, DAY TO HOUR, or DAY TO SECOND.

Example:

INTERVAL '10 00:00:00.004' DAY TO second indicates that the interval is 10 days and 4 milliseconds.

INTERVAL '10' DAY: indicates that the interval is 10 days.

INTERVAL '2-10' YEAR TO MONTH indicates that the interval is two years and ten months.

CURRENT_DATE

DATE

Return the SQL date of UTC time zone.

CURRENT_TIME

TIME

Return the SQL time of UTC time zone.

CURRENT_TIMESTAMP

TIMESTAMP

Return the SQL timestamp of UTC time zone.

LOCALTIME

TIME

Return the SQL time of the local time zone.

LOCALTIMESTAMP

TIMESTAMP

Return the SQL timestamp of the local time zone.

EXTRACT(timeintervalunit FROM temporal)

BIGINT

Extract part of the time point or interval. Return the part in the int type.

For example, extract the date 2006-06-05 and return 5.

EXTRACT(DAY FROM DATE '2006-06-05') returns 5.

YEAR(date)

BIGINT

Return the year from a SQL date.

For example, YEAR(DATE'1994-09-27') returns 1994.

QUARTER(date)

BIGINT

Return the quarter of a year from a SQL date.

MONTH(date)

BIGINT

Return the month of a year from a SQL date.

For example, MONTH(DATE '1994-09-27') returns 9.

WEEK(date)

BIGINT

Return the week of a year from a SQL date.

For example, WEEK(DATE'1994-09-27') returns 39.

DAYOFYEAR(date)

BIGINT

Return the day of a year from a SQL date.

For example, DAYOFYEAR(DATE '1994-09-27') is 270.

DAYOFMONTH(date)

BIGINT

Return the day of a month from a SQL date.

For example, DAYOFMONTH(DATE'1994-09-27') returns 27.

DAYOFWEEK(date)

BIGINT

Return the day of a week from a SQL date.

Sunday is set to 1.

For example, DAYOFWEEK(DATE'1994-09-27') returns 3.

HOUR(timestamp)

BIGINT

Return the hour of a day (an integer between 0 and 23) from a SQL timestamp.

For example, HOUR(TIMESTAMP '1994-09-27 13:14:15') returns 13.

MINUTE(timestamp)

BIGINT

Return the minute of an hour (an integer between 0 and 59) from a SQL timestamp.

For example, MINUTE(TIMESTAMP '1994-09-27 13:14:15') returns 14.

SECOND(timestamp)

BIGINT

Returns the second of a minute (an integer between 0 and 59) from a SQL timestamp.

For example, SECOND(TIMESTAMP '1994-09-27 13:14:15') returns 15.

FLOOR(timepoint TO timeintervalunit)

TIME

Round a time point down to the given unit.

For example, 12:44:00 is returned from FLOOR(TIME '12:44:31' TO MINUTE).

CEIL(timepoint TO timeintervalunit)

TIME

Round a time point up to the given unit.

For example, CEIL(TIME '12:44:31' TO MINUTE) returns 12:45:00.

(timepoint1, temporal1) OVERLAPS (timepoint2, temporal2)

BOOLEAN

Return TRUE if two time intervals overlap.

Example:

(TIME '2:55:00', INTERVAL '1' HOUR) OVERLAPS (TIME '3:30:00', INTERVAL '2' HOUR) returns TRUE.

(TIME '9:00:00', TIME '10:00:00') OVERLAPS (TIME '10:15:00', INTERVAL '3' HOUR) returns FALSE.

DATE_FORMAT(timestamp, string)

STRING

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

TIMESTAMPADD(timeintervalunit, interval, timepoint)

TIMESTAMP/DATE/TIME

Return the date and time added to timepoint based on the result of interval and timeintervalunit.

For example, TIMESTAMPADD(WEEK, 1, DATE '2003-01-02') returns 2003-01-09.

TIMESTAMPDIFF(timepointunit, timepoint1, timepoint2)

INT

Return 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, and YEAR.

For example, TIMESTAMPDIFF(DAY, TIMESTAMP '2003-01-02 10:00:00', TIMESTAMP '2003-01-03 10:00:00') returns 1.

CONVERT_TZ(string1, string2, string3)

TIMESTAMP

Convert a datetime string1 from time zone string2 to time zone string3.

For example, CONVERT_TZ('1970-01-01 00:00:00', 'UTC', 'America/Los_Angeles') returns '1969-12-31 16:00:00'.

FROM_UNIXTIME(numeric[, string])

STRING

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

The default string format is YYYY-MM-DD hh:mm:ss.

For example, FROM_UNIXTIME(44) returns 1970-01-01 09:00:44.

UNIX_TIMESTAMP()

BIGINT

Get current Unix timestamp in seconds.

UNIX_TIMESTAMP(string1[, string2])

BIGINT

Convert date time string string1 in format string2 to Unix timestamp (in seconds), using the specified timezone in table config.

The default format of string2 is yyyy-MM-dd HH:mm:ss.

TO_DATE(string1[, string2])

DATE

Convert a date string string1 with format string2 to a date.

The default format of string2 is yyyy-MM-dd.

TO_TIMESTAMP(string1[, string2])

TIMESTAMP

Convert date time string string1 with format string2 to a timestamp.

The default format of string2 is yyyy-MM-dd HH:mm:ss.

DATE

  • Function

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

  • Description
    DATE DATE string
  • Input parameters

    Parameter

    Type

    Description

    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

    Type

    Description

    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

    Type

    Description

    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

    Type

    Description

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

    N/A

  • 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

    N/A

  • 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

    N/A

  • 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

    N/A

  • 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

    N/A

  • 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

    Type

    Description

    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. The return value is of the BIGINT type.

  • Description
    BIGINT YEAR(date)
  • Input parameters

    Parameter

    Type

    Description

    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. The return value is of the BIGINT type.

  • Description
    BIGINT QUARTER(date)
  • Input parameters

    Parameter

    Type

    Description

    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. The return value is of the BIGINT type.

  • Description
    BIGINT MONTH(date)
  • Input parameters

    Parameter

    Type

    Description

    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. The return value is of the BIGINT type.

  • Description
    BIGINT WEEK(date)
  • Input parameters

    Parameter

    Type

    Description

    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. The return value is of the BIGINT type.

  • Description
    BIGINT DAYOFYEAR(date)
  • Input parameters

    Parameter

    Type

    Description

    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. The return value is of the BIGINT type.

  • Description
    BIGINT DAYOFMONTH(date)
  • Input parameters

    Parameter

    Type

    Description

    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. 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

    Type

    Description

    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. The return value is of the BIGINT type.

  • Description
    BIGINT HOUR(timestamp)
  • Input parameters

    Parameter

    Type

    Description

    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

    Type

    Description

    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

    Type

    Description

    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

    Type

    Description

    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 For details about the syntax of the userDefined result table, see User-defined Result Table.
      create table PrintSink (
        message TIME,
        message2 TIME,
        message3 TIMESTAMP(3)
      )
      with (
        'connector.type' = 'user-defined',
        'connector.class-name' = 'com.swqtest.flink.sink.PrintSink'--Replace the class with a user-defined class. For details, see the syntax description in the userDefined result table.
      );
      
      INSERT INTO 
      	PrintSink
      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`;
      
    • Test result
      The values of the fields in the PrintSink table are as follows:

      Message

      Message 2

      Message 3

      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

    Type

    Description

    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 For details about the syntax of the userDefined result table, see User-defined Result Table.
      create table PrintSink (
        message TIME,
        message2 TIME,
        message3 TIMESTAMP(3)
      )
      with (
        'connector.type' = 'user-defined',
        'connector.class-name' = 'com.swqtest.flink.sink.PrintSink'--Replace the class with a user-defined class. For details, see the syntax description in the userDefined result table.
      );
      
      INSERT INTO 
      	PrintSink
      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`;
    • 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

    Type

    Description

    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

    Type

    Description

    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

    Type

    Description

    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 DATE 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

    Type

    Description

    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

    Type

    Description

    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

    Type

    Description

    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

    N/A

  • 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

    Type

    Description

    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

    Type

    Description

    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

    Type

    Description

    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