Temporal Functions_Data Lake Insight

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

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

Temporal Functions

Function Description

DATE

TIME

TIMESTAMP

INTERVAL

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

LOCALTIME

LOCALTIMESTAMP

EXTRACT

YEAR

QUARTER

MONTH

WEEK

DAYOFYEAR

DAYOFMONTH

DAYOFWEEK

HOUR

MINUTE

SECOND

FLOOR

CEIL

OVERLAPS

DATE_FORMAT

TIMESTAMPADD

TIMESTAMPDIFF

CONVERT_TZ

FROM_UNIXTIME

UNIX_TIMESTAMP

UNIX_TIMESTAMP(string1[, string2])

TO_DATE

TO_TIMESTAMP

Feedback

Was this page helpful?