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.

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

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.

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

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.

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

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.

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

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

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

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

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

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.

Caution

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

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

Parameter	Type	Description
date	DATE	SQL date

Example

Test statement

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

Test result
result
1997

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

Parameter	Type	Description
date	DATE	SQL date

Example

Test statement

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

Test result
Result
2

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

Parameter	Type	Description
date	DATE	SQL date

Example

Test statement

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

Test result
result
4

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

Parameter	Type	Description
date	DATE	SQL date

Example

Test statement

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

Test result
result
17

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

Parameter	Type	Description
date	DATE	SQL date

Example

Test statement

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

Test result
result
115

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

Parameter	Type	Description
date	DATE	SQL date

Example

Test statement

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

Test result
Result
25

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
Note that the start day of a week is Sunday.
Description
```
BIGINT DAYOFWEEK(date)
```
Input parameters
Parameter
Type
Description
date
DATE
SQL date

Parameter	Type	Description
date	DATE	SQL date

Example

Test statement

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

Test result
result
6

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

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

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

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

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

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

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.

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

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.

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

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

Parameter	Type	Description
timepoint1/timepoint2	DATE/TIME/TIMESTAMP	Time point
temporal1/temporal2	DATE/TIME/TIMESTAMP/INTERVAL	Time point or interval

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

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

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

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

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

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

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

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

last updated: 2024-11-20 15:34 UTC - commit: c856450509a511f9057fb9577287974ed6fce406