Temporal Functions_Data Lake Insight

Description

**Table 1** Temporal Functions
Function	Return Type	Description
DATE string	DATE	Returns the SQL date parsed from a string in the format of "yyyy-MM-dd".
DATE_ADD	STRING	Resulting date after adding a certain number of days to a specified date. The data type is STRING.
DATE_SUB	STRING	Resulting date after subtracting a certain number of days from a specified date. The data type is STRING.
TIME string	TIME	Returns the SQL time parsed from a string in the format of "HH:mm:ss".
TIMESTAMP string	TIMESTAMP	Returns the SQL timestamp parsed from a string in the format of "yyyy-MM-dd HH:mm:ss[.SSS]".
INTERVAL string range	INTERVAL	Parses the SQL millisecond interval from a string in the format of "dd hh:mm:ss.fff" or the SQL month interval from a string in the format of "yyyy-mm". The interval range can be DAY, MINUTE, DAY TO HOUR, or DAY TO SECOND, with the interval in milliseconds; YEAR or YEAR TO MONTH represents the interval in months. For example, INTERVAL '10 00:00:00.004' DAY TO SECOND, INTERVAL '10' DAY, or INTERVAL '2-10' YEAR TO MONTH returns the interval.
CURRENT_DATE	DATE	Returns the current SQL date in the local time zone. In streaming mode, it is evaluated for each record. In batch processing mode, it is evaluated once at the beginning of the query and the same result is used for each row.
CURRENT_TIME	TIME	Returns the current SQL time in the local time zone, which is a synonym for LOCAL_TIME.
CURRENT_TIMESTAMP	TIMESTAMP	Returns the current SQL timestamp in the local time zone, with the return type of TIMESTAMP_LTZ(3). In streaming mode, it is evaluated for each record. In batch processing mode, it is evaluated once at the beginning of the query and the same result is used for each row.
LOCALTIME	TIME	Returns the current SQL time in the local time zone, with the return type of TIME(0). In streaming mode, it is evaluated for each record. In batch processing mode, it is evaluated once at the beginning of the query and the same result is used for each row.
LOCALTIMESTAMP	TIMESTAMP	Returns the current SQL timestamp in the local time zone, with the return type of TIMESTAMP(3). In streaming mode, it is evaluated for each record. In batch processing mode, it is evaluated once at the beginning of the query and the same result is used for each row.
NOW()	TIMESTAMP	Returns the current SQL timestamp in the local time zone, which is a synonym for CURRENT_TIMESTAMP.
CURRENT_ROW_TIMESTAMP()	TIMESTAMP_LTZ(3)	Returns the current SQL timestamp in the local time zone, with the return type of TIMESTAMP_LTZ(3). It is evaluated for each record, regardless of whether it is in batch processing mode or streaming mode.
EXTRACT(timeintervalunit FROM temporal)	BIGINT	Returns the long value extracted from the time interval unit part of the time. For example, EXTRACT(DAY FROM DATE '2006-06-05') returns 5.
YEAR(date)	BIGINT	Returns the year from the SQL date, which is equivalent to EXTRACT(YEAR FROM date). For example, YEAR(DATE '1994-09-27') returns 1994.
QUARTER(date)	BIGINT	Returns the quarter of the year from the SQL date, which is an integer between 1 and 4, equivalent to EXTRACT(QUARTER FROM date). For example, QUARTER(DATE '1994-09-27') returns 3.
MONTH(date)	BIGINT	Returns the month of the year from the SQL date, which is an integer between 1 and 12, equivalent to EXTRACT(MONTH FROM date). For example, MONTH(DATE '1994-09-27') returns 9.
WEEK(date)	BIGINT	Returns the week of the year from the SQL date, which is an integer between 1 and 53, equivalent to EXTRACT(WEEK FROM date). For example, WEEK(DATE '1994-09-27') returns 39.
DAYOFYEAR(date)	BIGINT	Returns the day of the year from the SQL date, which is an integer between 1 and 366, equivalent to EXTRACT(DOY FROM date). For example, DAYOFYEAR(DATE '1994-09-27') returns 270.
DAYOFMONTH(date)	BIGINT	Returns the day of the month from the SQL date, which is an integer between 1 and 31, equivalent to EXTRACT(DAY FROM date). For example, DAYOFWEEK(DATE '1994-09-27') returns 3.
DAYOFWEEK(date)	BIGINT	Calculates which day of the week the current date is, with Sunday being 1. For example, DAYOFWEEK(DATE'1994-09-27') returns 3.
HOUR(timestamp)	BIGINT	Returns the hour part of the hour unit from the SQL timestamp, which is an integer between 0 and 23, equivalent to EXTRACT(HOUR FROM timestamp). For example, MINUTE(TIMESTAMP '1994-09-27 13:14:15') returns 14.
MINUTE(timestamp)	BIGINT	Returns the minute part of the minute unit from the SQL timestamp, which is an integer between 0 and 59, equivalent to EXTRACT(MINUTE FROM timestamp). For example, MINUTE(TIMESTAMP '1994-09-27 13:14:15') returns 14.
SECOND(timestamp)	BIGINT	Returns the second part of the second unit from the SQL timestamp, which is an integer between 0 and 59, equivalent to EXTRACT(SECOND FROM timestamp). For example, SECOND(TIMESTAMP '1994-09-27 13:14:15') returns 15.
FLOOR(timepoint TO timeintervalunit)	TIME	Returns the value of timepoint rounded down to the time interval unit timeintervalunit. For example, FLOOR(TIME '12:44:31' TO MINUTE) returns 12:44:00.
CEIL(timepoint TO timeintervalunit)	TIME	Return the value of timepoint rounded up to the time interval unit timeintervalunit. For example, CEIL(TIME '12:44:31' TO MINUTE) returns 12:45:00.
(timepoint1, temporal1) OVERLAPS (timepoint2, temporal2)	BOOLEAN	Returns TRUE if the two time intervals defined by (timepoint1, temporal1) and (timepoint2, temporal2) overlap. The time value can be a time point or a time interval. For 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	Converts the timestamp to a string value in the specified date format string. The format string is compatible with Java's SimpleDateFormat.
TIMESTAMPADD(timeintervalunit, interval, timepoint)	TIMESTAMP/DATE/TIME	Adds the result of combining interval with timeintervalunit to a timepoint that includes a date or datetime, and returns the resulting datetime. For example, TIMESTAMPADD(WEEK, 1, DATE '2003-01-02') returns 2003-01-09.
TIMESTAMPDIFF(timepointunit, timepoint1, timepoint2)	INT	Returns the time interval between timepoint1 and timepoint2. The unit of the interval is given by the first parameter, which should be one of the following values: SECOND, MINUTE, HOUR, DAY, MONTH, or YEAR.
CONVERT_TZ(string1, string2, string3)	TIMESTAMP	Convert the datetime string1 (with the default ISO timestamp format 'yyyy-MM-dd HH:mm:ss') from time zone string2 to the value in time zone string3. The format of time zone should be either an abbreviation such as PST, a full name such as Country A/City A, or a custom ID such as GMT-08:00. For example, CONVERT_TZ('1970-01-01 00:00:00', 'UTC', 'Country A/City A') returns '1969-12-31 16:00:00'.
FROM_UNIXTIME(numeric[, string])	STRING	Returns the representation of the numeric parameter numberic in the string format (default is yyyy-MM-dd HH:mm:ss). Numeric is an internal timestamp value that represents the number of seconds since '1970-01-01 00:00:00' UTC, generated by the UNIX_TIMESTAMP() function. The return value is represented in the session time zone (specified in TableConfig). For example, if in the UTC time zone, FROM_UNIXTIME(44) returns 1970-01-01 00:00:44, and if in the Asia/Tokyo time zone, it returns 1970-01-01 09:00:44.
UNIX_TIMESTAMP()	BIGINT	Gets the current Unix timestamp in seconds. This function is non-deterministic, meaning it will be recomputed for each record.
UNIX_TIMESTAMP(string1[, string2])	BIGINT	Converts the datetime string1 in the format of string2 (default is 'yyyy-MM-dd HH:mm:ss') to a Unix timestamp in seconds, using the time zone specified in the table configuration.
TO_DATE(string1[, string2])	DATE	Converts the string1 in the format of string2 (default is yyyy-MM-dd) to a date.
TO_TIMESTAMP_LTZ(numeric, precision)	TIMESTAMP_LTZ	Converts the epoch seconds or epoch milliseconds to TIMESTAMP_LTZ, with a valid precision of 0 or 3, where 0 represents TO_TIMESTAMP_LTZ(epochSeconds, 0) and 3 represents TO_TIMESTAMP_LTZ(epochMilliseconds, 3).
TO_TIMESTAMP(string1[, string2])	TIMESTAMP	Converts the string1 in the format of string2 (default is yyyy-MM-dd HH:mm:ss) in the UTC+0 time zone to a timestamp.
CURRENT_WATERMARK(rowtime)	-	Returns the current watermark of the given time column attribute rowtime. If there is no common watermark available from upstream operations in the pipeline, the function returns NULL. The return type of the function is inferred to match the provided time column attribute, but with an adjusted precision of 3. For example, if the time column attribute is TIMESTAMP_LTZ(9), the function returns TIMESTAMP_LTZ(3). Note that this function can return NULL, which you may need to consider. For example, if you want to filter out late data, you can use: WHERE CURRENT_WATERMARK(ts) IS NULL OR ts > CURRENT_WATERMARK(ts)

DATE

Function
Returns a SQL date parsed from string in form of yyyy-MM-dd.
Description
```
DATE DATE string
```

Input parameters

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

Data 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

DATE_ADD

Function
Returns the resulting date after adding a certain number of days to a specified date.
Description
```
DATE_ADD(string startdate, int days)
```
Input parameters
- startdate: specified date. The data type is TIMESTAMP or STRING.
  
  The date format for the STRING type is yyyy-MM-dd HH:mm:ss.
  
  The function supports the special case where this parameter value is NULL.
- days: target number of days. The data type is INT.
Return values
Resulting date after adding a certain number of days to a specified date. The data type is STRING.

Example

Submit a Flink SQL statement.

CREATE TABLE source (
  time1 TIMESTAMP
) WITH (
  'connector' = 'datagen',
  'rows-per-second' = '1'
);
create table Sink (
  date1 string,
  date2 string,
  date3 string
) with ('connector' = 'print');
INSERT into
  Sink
select
  DATE_ADD(time1, 30) as date1,
  DATE_ADD('2017-09-15 00:00:00', 30) as date2,
  DATE_ADD(cast(null as timestamp),30) as date3
FROM source

Test result

date1 (string)	date2 (string)	date3 (string)
2024-06-28	2017-10-15	null

DATE_SUB

Function
Returns the resulting date after subtracting a certain number of days from a specified date.
Description
```
DATE_SUB(string startdate, int days)
```
Input parameters
- startdate: specified date. The data type is TIMESTAMP or STRING.
  
  The date format for the STRING type is yyyy-MM-dd HH:mm:ss.
  
  The function supports the special case where this parameter value is NULL.
- days: target number of days. The data type is INT.
Return values
Resulting date after subtracting a certain number of days from a specified date. The data type is STRING.

Example

Submit a Flink SQL statement.

CREATE TABLE source (
  time1 TIMESTAMP
) WITH (
  'connector' = 'datagen',
  'rows-per-second' = '1'
);
create table Sink (
  date1 string,
  date2 string,
  date3 string
) with ('connector' = 'print');
INSERT into
  Sink
select
  DATE_SUB(time1,30) as date1,
  DATE_SUB('2017-09-15 00:00:00', 30) as date2,
  DATE_SUB(cast(null as timestamp),30) as date3
FROM source

Test result

date1 (string)	date2 (string)	date3 (string)
2024-04-29	2017-08-16	null

TIME

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

Input parameters

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

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

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

Data 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

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

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

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

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

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

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 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 date. The return value is of the BIGINT type.
Description
```
BIGINT YEAR(date)
```

Input parameters

Parameter	Data 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 date. The return value is of the BIGINT type.
Description
```
BIGINT QUARTER(date)
```

Input parameters

Parameter	Data 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 date. The return value is of the BIGINT type.
Description
```
BIGINT MONTH(date)
```

Input parameters

Parameter	Data 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 date. The return value is of the BIGINT type.
Description
```
BIGINT WEEK(date)
```

Input parameters

Parameter	Data 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 date. The return value is of the BIGINT type.
Description
```
BIGINT DAYOFYEAR(date)
```

Input parameters

Parameter	Data 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 date. The return value is of the BIGINT type.
Description
```
BIGINT DAYOFMONTH(date)
```

Input parameters

Parameter	Data 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 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 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 timestamp. The return value is of the BIGINT type.
Description
```
BIGINT HOUR(timestamp)
```

Input parameters

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

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

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 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	Data 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	Data 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 TIMESTAMP 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 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 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	Data 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 Country A/City A, 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 Country A/City A, or a custom ID such as GMT-08:00.

Example

Test statement

SELECT 
	CONVERT_TZ(1970-01-01 00:00:00, UTC, Country A/City A) 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 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
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 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	Data 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	Data 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

Description

DATE

DATE_ADD

DATE_SUB

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?