Temporal Functions
Table 1 lists the time functions supported by Flink OpenSource SQL.
Description
Function |
Return Type |
Description |
---|---|---|
DATE |
Parse the date string (yyyy-MM-dd) to a SQL date. |
|
TIME |
Parse the time string (HH:mm:ss[.fff]) to a SQL time. |
|
TIMESTAMP |
Convert the time string into a timestamp. The time string format is yyyy-MM-dd HH:mm:ss[.fff]. |
|
INTERVAL |
interval indicates the interval. There are two forms:
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. |
|
DATE |
Return the SQL date of UTC time zone. |
|
TIME |
Return the SQL time of UTC time zone. |
|
TIMESTAMP |
Return the SQL timestamp of UTC time zone. |
|
TIME |
Return the SQL time of the current time zone. |
|
TIMESTAMP |
Return the SQL timestamp of the current time zone. |
|
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. |
|
BIGINT |
Return the year from SQL date. For example, YEAR(DATE'1994-09-27') returns 1994. |
|
BIGINT |
Return the quarter of a year (an integer between 1 and 4) from SQL date. |
|
BIGINT |
Return the month of a year (an integer between 1 and 12) from SQL date. For example, MONTH(DATE '1994-09-27') returns 9. |
|
BIGINT |
Return the week of a year (an integer between 1 and 53) from SQL date. For example, WEEK(DATE'1994-09-27') returns 39. |
|
BIGINT |
Returns the day of a year (an integer between 1 and 366) from SQL date. For example, DAYOFYEAR(DATE '1994-09-27') is 270. |
|
BIGINT |
Return the day of a month (an integer between 1 and 31) from SQL date. For example, DAYOFMONTH(DATE'1994-09-27') returns 27. |
|
BIGINT |
Return the day of a week (an integer between 1 and 7) from SQL date. Sunday is set to 1. For example, DAYOFWEEK(DATE'1994-09-27') returns 3. |
|
BIGINT |
Returns the hour of a day (an integer between 0 and 23) from SQL timestamp. For example, HOUR(TIMESTAMP '1994-09-27 13:14:15') returns 13. |
|
BIGINT |
Returns the minute of an hour (an integer between 0 and 59) from SQL timestamp. For example, MINUTE(TIMESTAMP '1994-09-27 13:14:15') returns 14. |
|
BIGINT |
Returns the second of a minute (an integer between 0 and 59) from SQL timestamp. For example, SECOND(TIMESTAMP '1994-09-27 13:14:15') returns 15. |
|
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). |
|
TIME |
Round a time point up to the given unit. For example, CEIL(TIME '12:44:31' TO MINUTE) returns 12:45:00. |
|
BOOLEAN |
Return TRUE if two time intervals defined by (timepoint1, temporal1) and (timepoint2, temporal2) 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. |
|
STRING |
Convert timestamp to a value of string in the format specified by the date format string. |
|
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. |
|
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, or YEAR. For example, TIMESTAMPDIFF(DAY, TIMESTAMP '2003-01-02 10:00:00', TIMESTAMP '2003-01-03 10:00:00') returns 1. |
|
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'. |
|
STRING |
Return a string representation of the numeric argument (in seconds) in the current time zone. The default string format is YYYY-MM-DD hh:mm:ss. For example, FROM_UNIXTIME(44) returns 1970-01-01 09:00:44. |
|
BIGINT |
Get current Unix timestamp in seconds. |
|
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. |
|
DATE |
Convert a date string string1 with format string2 to a date. The default format of string2 is yyyy-MM-dd. |
|
TIMESTAMP |
Converts date time string string1 with format string2 under the 'UTC+0' time zone to a timestamp. The default format of string2 is yyyy-MM-dd HH:mm:ss. |
DATE
- Function
Returns a SQL date parsed from string in form of yyyy-MM-dd.
- Description
DATE DATE string
- Input parameters
Parameter
Data Types
Parameters
string
STRING
String in the SQL date format.
Note that the string must be in the yyyy-MM-dd format. Otherwise, an error will be reported.
- Example
- Test statement
SELECT DATE "2021-08-19" AS `result` FROM testtable;
- Test Result
result
2021-08-19
- Test statement
TIME
- Function
Returns a SQL time parsed from string in form of HH:mm:ss[.fff].
- Description
TIME TIME string
- Input parameters
Parameter
Data Types
Parameters
string
STRING
Time
Note that the string must be in the format of HH:mm:ss[.fff]. Otherwise, an error will be reported.
- Example
- Test statement
SELECT TIME "10:11:12" AS `result`, TIME "10:11:12.032" AS `result2` FROM testtable;
- Test result
result
result2
10:11:12
10:11:12.032
- Test statement
TIMESTAMP
- Function
Converts the time string into timestamp. The time string format is yyyy-MM-dd HH:mm:ss[.fff]. The return value is of the TIMESTAMP(3) type.
- Description
TIMESTAMP(3) TIMESTAMP string
- Input parameters
Parameter
Data Types
Parameters
string
STRING
Time
Note that the string must be in the format of yyyy-MM-dd HH:mm:ss[.fff]. Otherwise, an error will be reported.
- Example
- Test statement
SELECT TIMESTAMP "1997-04-25 13:14:15" AS `result`, TIMESTAMP "1997-04-25 13:14:15.032" AS `result2` FROM testtable;
- Test result
result
result2
1997-04-25 13:14:15
1997-04-25 13:14:15.032
- Test statement
INTERVAL
- Function
Parses an interval string.
- Description
INTERVAL INTERVAL string range
- Input parameters
Parameter
Data Types
Parameters
string
STRING
Timestamp string used together with the range parameter. The string is in either of the following two formats:
- yyyy-MM for SQL intervals of months. An interval range might be YEAR or YEAR TO MONTH for intervals of months.
- dd hh:mm:ss.fff for SQL intervals of milliseconds. An interval range might be DAY, MINUTE, DAY TO HOUR, or DAY TO SECOND.
range
INTERVAL
Interval range. This parameter is used together with the string parameter.
Available values are as follows: YEAR, YEAR To Month, DAY, MINUTE, DAY TO HOUR and DAY TO SECOND.
- Example
Test statement
-- indicates that the interval is 10 days and 4 milliseconds. INTERVAL '10 00:00:00.004' DAY TO second -- The interval is 10 days. INTERVAL '10' -- The interval is 2 years and 10 months. INTERVAL '2-10' YEAR TO MONTH
CURRENT_DATE
- Function
Returns the current SQL time (yyyy-MM-dd) in the local time zone. The return value is of the DATE type.
- Description
DATE CURRENT_DATE
- Input parameters
None
- Example
- Test statement
SELECT CURRENT_DATE AS `result` FROM testtable;
- Test result
result
2021-10-28
- Test statement
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
- Test statement
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
- Test statement
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
- Test statement
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
- Test statement
EXTRACT
- Function
Returns a value extracted from the timeintervalunit part of temporal. The return value is of the BIGINT type.
- Description
BIGINT EXTRACT(timeinteravlunit FROM temporal)
- Input parameters
Parameter
Data Types
Parameters
timeinteravlunit
TIMEUNIT
Time unit to be extracted from a time point or interval. The value can be YEAR, QUARTER, MONTH, WEEK, DAY, DOY, HOUR, MINUTE, SECOND.
temporal
DATE/TIME/TIMESTAMP/INTERVAL
Time point or interval
Do not specify a time unit that is not of any time points or intervals. Otherwise, the job fails to be submitted.
For example, an error message is displayed when the following statement is executed because YEAR cannot be extracted from TIME.
SELECT EXTRACT(YEAR FROM TIME '12:44:31' ) AS `result` FROM testtable;
- Example
- Test statement
SELECT EXTRACT(YEAR FROM DATE '1997-04-25' ) AS `result`, EXTRACT(MINUTE FROM TIME '12:44:31') AS `result2`, EXTRACT(SECOND FROM TIMESTAMP '1997-04-25 13:14:15') AS `result3`, EXTRACT(YEAR FROM INTERVAL '2-10' YEAR TO MONTH) AS `result4`, FROM testtable;
- Test result
result
result2
result3
result4
1997
44
15
2
- Test statement
YEAR
- Function
Returns the year from a SQL date date. The return value is of the BIGINT type.
- Description
BIGINT YEAR(date)
- Input parameters
Parameter
Data Types
Parameters
date
DATE
SQL date
- Example
- Test statement
SELECT YEAR(DATE '1997-04-25' ) AS `result` FROM testtable;
- Test result
result
1997
- Test statement
QUARTER
- Function
Returns the quarter of a year (an integer between 1 and 4) from a SQL date date. The return value is of the BIGINT type.
- Description
BIGINT QUARTER(date)
- Input parameters
Parameter
Data Types
Parameters
date
DATE
SQL date
- Example
- Test statement
SELECT QUARTER(DATE '1997-04-25' ) AS `result` FROM testtable;
- Test result
result
2
- Test statement
MONTH
- Function
Returns the month of a year (an integer between 1 and 12) from a SQL date date. The return value is of the BIGINT type.
- Description
BIGINT MONTH(date)
- Input parameters
Parameter
Data Types
Parameters
date
DATE
SQL date
- Example
- Test statement
SELECT MONTH(DATE '1997-04-25' ) AS `result` FROM testtable;
- Test result
result
4
- Test statement
WEEK
- Function
Returns the week of a year from a SQL date date. The return value is of the BIGINT type.
- Description
BIGINT WEEK(date)
- Input parameters
Parameter
Data Types
Parameters
date
DATE
SQL date
- Example
- Test statement
SELECT WEEK(DATE '1997-04-25' ) AS `result` FROM testtable;
- Test result
result
17
- Test statement
DAYOFYEAR
- Function
Returns the day of a year (an integer between 1 and 366) from SQL date date. The return value is of the BIGINT type.
- Description
BIGINT DAYOFYEAR(date)
- Input parameters
Parameter
Data Types
Parameters
date
DATE
SQL date
- Example
- Test statement
SELECT DAYOFYEAR(DATE '1997-04-25' ) AS `result` FROM testtable;
- Test Result
result
115
- Test statement
DAYOFMONTH
- Function
Returns the day of a month (an integer between 1 and 31) from a SQL date date. The return value is of the BIGINT type.
- Description
BIGINT DAYOFMONTH(date)
- Input parameters
Parameter
Data Types
Parameters
date
DATE
SQL date
- Example
- Test statement
SELECT DAYOFMONTH(DATE '1997-04-25' ) AS `result` FROM testtable;
- Test Result
result
25
- Test statement
DAYOFWEEK
- Function
Returns the day of a week (an integer between 1 and 7) from a SQL date date. The return value is of the BIGINT type.
Note that the start day of a week is Sunday.
- Description
BIGINT DAYOFWEEK(date)
- Input parameters
Parameter
Data Types
Parameters
date
DATE
SQL date
- Example
- Test statement
SELECT DAYOFWEEK(DATE '1997-04-25') AS `result` FROM testtable;
- Test Result
result
6
- Test statement
HOUR
- Function
Returns the hour of a day (an integer between 0 and 23) from SQL timestamp timestamp. The return value is of the BIGINT type.
- Description
BIGINT HOUR(timestamp)
- Input parameters
Parameter
Data Types
Parameters
timestamp
TIMESTAMP
SQL timestamp
- Example
- Test statement
SELECT HOUR(TIMESTAMP '1997-04-25 10:11:12') AS `result` FROM testtable;
- Test Result
result
10
- Test statement
MINUTE
- Function
Returns the minute of an hour (an integer between 0 and 59) from a SQL timestamp. The return value is of the BIGINT type.
- Description
BIGINT MINUTE(timestamp)
- Input parameters
Parameter
Data Types
Parameters
timestamp
TIMESTAMP
SQL timestamp
- Example
- Test statement
SELECT MINUTE(TIMESTAMP '1997-04-25 10:11:12') AS `result` FROM testtable;
- Test Result
result
11
- Test statement
SECOND
- Function
Returns the second of an hour (an integer between 0 and 59) from a SQL timestamp. The return value is of the BIGINT type.
- Description
BIGINT SECOND(timestamp)
- Input parameters
Parameter
Data Types
Parameters
timestamp
TIMESTAMP
SQL timestamp
- Example
- Test statement
SELECT SECOND(TIMESTAMP '1997-04-25 10:11:12') AS `result` FROM testtable;
- Test result
result
12
- Test statement
FLOOR
- Function
Returns a value that rounds timepoint down to the time unit timeintervalunit.
- Description
TIME/TIMESTAMP(3) FLOOR(timepoint TO timeintervalunit)
- Input parameters
Parameter
Data Types
Parameters
timepoint
TIMESTAMP/TIME
SQL time or SQL timestamp
timeintervalunit
TIMEUNIT
Time unit. The value can be YEAR, QUARTER, MONTH, WEEK, DAY, DOY, HOUR, MINUTE, or SECOND.
- Example
- Test statement
SELECT FLOOR(TIME '13:14:15' TO MINUTE) AS `result` FLOOR(TIMESTAMP '1997-04-25 13:14:15' TO MINUTE) AS `result2`, FLOOR(TIMESTAMP '1997-04-25 13:14:15' TO MINUTE) AS `result3` FROM testtable;
- Test result
message
message2
message3
13:14
13:14
1997-04-25T13:14
- Test statement
CEIL
- Function
Returns a value that rounds timepoint up to the time unit timeintervalunit.
- Description
TIME/TIMESTAMP(3) CEIL(timepoint TO timeintervalunit)
- Input parameters
Parameter
Data Types
Parameters
timepoint
TIMESTAMP/TIME
SQL time or SQL timestamp
timeintervalunit
TIMEUNIT
Time unit. The value can be YEAR, QUARTER, MONTH, WEEK, DAY, DOY, HOUR, MINUTE, or SECOND.
- Example
- Test statement
SELECT CEIL(TIME '13:14:15' TO MINUTE) AS `result` CEIL(TIMESTAMP '1997-04-25 13:14:15' TO MINUTE) AS `result2`, CEIL(TIMESTAMP '1997-04-25 13:14:15' TO MINUTE) AS `result3` FROM testtable;
- Test Result
result
result2
result3
13:15
13:15
1997-04-25T13:15
- Test statement
OVERLAPS
- Function
Returns TRUE if two time intervals overlap; returns FALSE otherwise.
- Description
BOOLEAN (timepoint1, temporal1) OVERLAPS (timepoint2, temporal2)
- Input parameters
Parameter
Data Types
Parameters
timepoint1/timepoint2
DATE/TIME/TIMESTAMP
Time point
temporal1/temporal2
DATE/TIME/TIMESTAMP/INTERVAL
Time point or interval
- (timepoint, temporal) is a closed interval.
- The temporal can be of the DATE, TIME, TIMESTAMP, or INTERVAL type.
- When th temporal is DATE, TIME, or TIMESTAMP, (timepoint, temporal) indicates an interval between timepoint and temporal. The temporal can be earlier than the value of timepoint, for example, (DATE '1997-04-25', DATE '1997-04-23').
- When the temporal is INTERVAL, (timepoint, temporal) indicates an interval between timepoint and timepoint + temporal.
- Ensure that (timepoint1, temporal1) and (timepoint2, temporal2) are intervals of the same data type.
- Example
- Test statement
SELECT (TIME '2:55:00', INTERVAL '1' HOUR) OVERLAPS (TIME '3:30:00', INTERVAL '2' HOUR) AS `result`, (TIME '2:30:00', INTERVAL '1' HOUR) OVERLAPS (TIME '3:30:00', INTERVAL '2' HOUR) AS `result2`, (TIME '2:30:00', INTERVAL '1' HOUR) OVERLAPS (TIME '3:31:00', INTERVAL '2' HOUR) AS `result3`, (TIME '9:00:00', TIME '10:00:00') OVERLAPS (TIME '10:00:00', INTERVAL '3' HOUR) AS `result4`, (TIMESTAMP '1997-04-25 12:00:00', TIMESTAMP '1997-04-25 12:20:00') OVERLAPS (TIMESTAMP '1997-04-25 13:00:00', INTERVAL '2' HOUR) AS `result5`, (DATE '1997-04-23', INTERVAL '2' DAY) OVERLAPS (DATE '1997-04-25', INTERVAL '2' DAY) AS `result6`, (DATE '1997-04-25', DATE '1997-04-23') OVERLAPS (DATE '1997-04-25', INTERVAL '2' DAY) AS `result7` FROM testtable;
- Test Result
result
result2
result3
result4
result5
result6
result7
true
true
false
true
false
true
true
- Test statement
DATE_FORMAT
- Function
Converts a timestamp to a value of string in the format specified by the date format string.
- Description
STRING DATE_FORMAT(timestamp, dateformat)
- Input parameters
Parameter
Data Types
Parameters
timestamp
TIMESTAMP/STRING
Time point
dateformat
STRING
String in the date format
- Example
- Test statement
SELECT DATE_FORMAT(TIMESTAMP '1997-04-25 10:11:12', 'yyyy-MM-dd HH:mm:ss') AS `result`, DATE_FORMAT(TIMESTAMP '1997-04-25 10:11:12', 'yyyy-MM-dd') AS `result2`, DATE_FORMAT(TIMESTAMP '1997-04-25 10:11:12', 'yy/MM/dd HH:mm') AS `result3`, DATE_FORMAT('1997-04-25 10:11:12', 'yyyy-MM-dd') AS `result4` FROM testtable;
- Test Result
result
result2
result3
result4
1997-04-25 10:11:12
1997-04-25
97/04/25 10:11
1997-04-25
- Test statement
TIMESTAMPADD
- Function
Returns the date and time by combining interval and timeintervalunit and adding the combination to timepoint.
The return value of TIMESTAMPADD is the value of timepoint. An exception is that if the input timepoint is of the TIMESTAMP type, the return value can be inserted into a table field of the DATE type.
- Description
TIMESTAMP(3)/DATE/TIME TIMESTAMPADD(timeintervalunit, interval, timepoint)
- Input parameters
Parameter
Data Types
Parameters
timeintervalunit
TIMEUNIT
Time unit.
interval
INT
Interval
timepoint
TIMESTAMP/DATE/TIME
Time point
- Example
- Test statement
SELECT TIMESTAMPADD(WEEK, 1, DATE '1997-04-25') AS `result`, TIMESTAMPADD(QUARTER, 1, TIMESTAMP '1997-04-25 10:11:12') AS `result2`, TIMESTAMPADD(SECOND, 2, TIME '10:11:12') AS `result3` FROM testtable;
- Test Result
result
result2
result3
1997-05-02
- If this field is inserted into a table field of the TIMESTAMP type, 1997-07-25T10:11:12 is returned.
- If this field is inserted into a table field of the TIMESTAMP type, 1997-07-25 is returned.
10:11:14
- Test statement
TIMESTAMPDIFF
- Function
Returns the (signed) number of timepointunit between timepoint1 and timepoint2. The unit for the interval is given by the first argument.
- Description
INT TIMESTAMPDIFF(timepointunit, timepoint1, timepoint2)
- Input parameters
Parameter
Data Types
Parameters
timepointunit
TIMEUNIT
Time unit. The value can be SECOND, MINUTE, HOUR, DAY, MONTH or YEAR.
timepoint1/timepoint2
TIMESTAMP/DATE
Time point
- Example
- Test statement
SELECT TIMESTAMPDIFF(DAY, TIMESTAMP '1997-04-25 10:00:00', TIMESTAMP '1997-04-28 10:00:00') AS `result`, TIMESTAMPDIFF(DAY, DATE '1997-04-25', DATE '1997-04-28') AS `result2`, TIMESTAMPDIFF(DAY, TIMESTAMP '1997-04-27 10:00:20', TIMESTAMP '1997-04-25 10:00:00') AS `result3` FROM testtable;
- Test result
result
result2
result3
3
3
-2
- Test statement
CONVERT_TZ
- Function
Converts a datetime string1 (with default ISO timestamp format 'yyyy-MM-dd HH:mm:ss') from time zone string2 to time zone string3.
- Description
STRING CONVERT_TZ(string1, string2, string3)
- Input parameters
Parameter
Data Types
Parameters
string1
STRING
SQL timestamp. If the value does not meet the format requirements, NULL is returned.
string2
STRING
Time zone before conversion. The format of time zone should be either an abbreviation such as PST, a full name such as America/Los_Angeles, or a custom ID such as GMT-08:00.
string3
STRING
Time zone after conversion. The format of time zone should be either an abbreviation such as PST, a full name such as America/Los_Angeles, or a custom ID such as GMT-08:00.
- Example
- Test statement
SELECT CONVERT_TZ(1970-01-01 00:00:00, UTC, America/Los_Angeles) AS `result`, CONVERT_TZ(1997-04-25 10:00:00, UTC, GMT-08:00) AS `result2` FROM testtable;
- Test Result
result
result2
1969-12-31 16:00:00
1997-04-25 02:00:00
- Test statement
FROM_UNIXTIME
- Function
Returns a representation of the numeric argument as a value in string format.
- Description
STRING FROM_UNIXTIME(numeric[, string])
- Input parameters
Parameter
Data Types
Parameters
numeric
BIGINT
An internal timestamp representing the number of seconds since 1970-01-01 00:00:00 UTC. The value can be generated by the UNIX_TIMESTAMP() function.
string
STRING
Time. If this parameter is not specified, the default time format is yyyy-MM-dd HH:mm:ss format.
- Example
- Test statement
SELECT FROM_UNIXTIME(44) AS `result`, FROM_UNIXTIME(44, 'yyyy:MM:dd') AS `result2` FROM testtable;
- Test Result
result
result2
1970-01-01 08:00:44
1970:01:01
- Test statement
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
- Test statement
UNIX_TIMESTAMP(string1[, string2])
- Function
Converts date time string1 in format string2 to Unix timestamp (in seconds). The return value is of the BIGINT type.
- Description
BIGINT UNIX_TIMESTAMP(string1[, string2])
- Input parameters
Parameter
Data Types
Parameters
string1
STRING
SQL timestamp string. An error is reported if the value does not comply with the string2 format.
string2
STRING
Time. If this parameter is not specified, the default time format is yyyy-MM-dd HH:mm:ss.
- Example
- Test statement
SELECT UNIX_TIMESTAMP('1997-04-25', 'yyyy-MM-dd') AS `result`, UNIX_TIMESTAMP('1997-04-25 00:00:10', 'yyyy-MM-dd HH:mm:ss') AS `result2`, UNIX_TIMESTAMP('1997-04-25 00:00:00') AS `result3` FROM testtable;
- Test result
result
result2
result3
861897600
861897610
861897600
- Test statement
TO_DATE
- Function
Converts a date string1 with format string2 to a date.
- Description
DATE TO_DATE(string1[, string2])
- Input parameters
Parameter
Data Types
Parameters
string1
STRING
SQL timestamp string. If the value is not in the required format, an error is reported.
string2
STRING
Format. If this parameter is not specified, the default time format is yyyy-MM-dd.
- Example
- Test statement
SELECT TO_DATE('1997-04-25') AS `result`, TO_DATE('1997:04:25', 'yyyy-MM-dd') AS `result2`, TO_DATE('1997-04-25 00:00:00', 'yyyy-MM-dd HH:mm:ss') AS `result3` FROM testtable;
- Test result
result
result2
result3
1997-04-25
1997-04-25
1997-04-25
- Test statement
TO_TIMESTAMP
- Function
Converts date time string1 with format string2 to a timestamp.
- Description
TIMESTAMP TO_TIMESTAMP(string1[, string2])
- Input parameters
Parameter
Data Types
Parameters
string1
STRING
SQL timestamp string. If the value is not in the required format, NULL is returned.
string2
STRING
Date format. If this parameter is not specified, the default format is yyyy-MM-dd HH:mm:ss.
- Example
- Test statement
SELECT TO_TIMESTAMP('1997-04-25', 'yyyy-MM-dd') AS `result`, TO_TIMESTAMP('1997-04-25 00:00:00') AS `result2`, TO_TIMESTAMP('1997-04-25 00:00:00', 'yyyy-MM-dd HH:mm:ss') AS `result3` FROM testtable;
- Test result
result
result2
result3
1997-04-25 00:00
1997-04-25 00:00
1997-04-25 00:00
- Test statement
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.