Datetime Functions
ADDDATE
- ADDDATE(date, INTERVAL expr unit)
Description: Adds an interval on a specified date and returns the calculation result. The function and usage of this form of function is the same as those of DATE_ADD.
For details about the parameters, see the following table.
Parameter
Type
Description
Value Range
date
Time expression, TEXT, DATETIME, DATE, or TIME.
Specifies the datetime reference.
See the value ranges of the corresponding types.
expr
Integer, floating-point number, string, or expression.
Specifies the time interval, which can be a negative value.
See the value ranges of the corresponding types.
unit
Keyword.
Specifies the unit of the interval.
YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, or MICROSECOND. For details, see Time Interval Expressions.
Examples:
m_db=# SELECT ADDDATE('2018-05-01', INTERVAL 1 DAY); adddate ------------ 2018-05-02 (1 row)
- ADDDATE(date, days)
Description: Adds the number of days specified by days to the date specified by date and returns the calculation result.
For details about the parameters, see the following table.
Parameter
Type
Description
Value Range
date
Time expression, TEXT, DATETIME, DATE, or TIME.
Specifies the datetime reference.
See the value ranges of the corresponding types.
days
Integer, floating-point number, or string.
Specifies the time interval, which can be a negative value.
See the value ranges of the corresponding types.
Return value type: TEXT, DATE, DATETIME, or TIME.
Examples:
m_db=# SELECT ADDDATE('2018-05-01', 1); adddate ------------ 2018-05-02 (1 row)
ADDTIME
ADDTIME(expr1, expr2)
Description: Returns the sum of expr1 and expr2. The format of the return value is the same as that of expr1.
- expr1: specifies the time or datetime expression.
- expr2: specifies the time expression.
Return value type: TEXT, DATETIME, or TIME. The return value type is related to the expr1 type. If expr1 is parsed as DATETIME, DATETIME is returned. If expr1 is parsed as TIME, TIME is returned.
Examples:
m_db=# SELECT ADDTIME('2000-03-01 20:59:59', '00:00:01');
addtime
---------------------
2000-03-01 21:00:00
(1 row)
m_db=# SELECT ADDTIME('20:59:59', '00:00:01');
addtime
----------
21:00:00
(1 row)
CONVERT_TZ
CONVERT_TZ(dt, from_tz, to_tz)
Description: The CONVERT_TZ function is used to convert the datetime value dt from from_tz time zone to to_tz time zone. If the dt parameter is invalid, null is returned. If dt is out of the range supported by the TIMESTAMP type, no conversion occurs.
For details about the parameters, see the following table.
|
Parameter |
Type |
Description |
Value Range |
|---|---|---|---|
|
dt |
TEXT, DATETIME, DATE, TIME, and other types that can be implicitly converted to DATETIME. |
Specifies the datetime. |
For details, see Table 1. |
|
from_tz/to_tz |
A string in the format of ±hh:mm. |
Offset compared with the UTC time, for example, '+08:00'. |
[-12:59, +13:00]. The plus and minus signs are required. |
|
Named time zone. |
For example, 'MET' and 'UTC'. |
For details, see the PG_TIMEZONE_NAMES system view. |
Return type: DATETIME
Examples:
1 2 3 4 5 |
m_db=# SELECT CONVERT_TZ('2023-01-01 10:10:10', '+00:00', '+08:00'); convert_tz --------------------- 2023-01-01 18:10:10 (1 row) |
CURDATE
CURDATE()
Description: Returns the system date when the local function calling starts. The time zone can be changed within the same connection. The returned date is affected by the time zone.
Return value type: DATE
Examples:
1 2 3 4 5 |
m_db=# SELECT CURDATE(); curdate ------------ 2023-12-09 (1 row) |
CURRENT_DATE
CURRENT_DATE/CURRENT_DATE()
Description: Returns the system date when the local function calling starts. The time zone can be changed within the same connection. The returned date is affected by the time zone. This function is an alias of CURDATE.
Return value type: DATE
Examples:
1 2 3 4 5 6 7 8 9 10 11 |
m_db=# SELECT CURRENT_DATE; current_date -------------- 2023-12-09 (1 row) m_db=# SELECT CURRENT_DATE(); current_date -------------- 2023-12-09 (1 row) |
CURRENT_TIME
CURRENT_TIME/CURRENT_TIME([scale])
Description: Returns the start time of the local function calling, excluding the date.
The parameter scale specifies the precision of the microsecond part. The value is an integer ranging from 0 to 6. The default value is 0.
Return type: TIME
Examples:
1 2 3 4 5 6 7 8 9 10 11 |
m_db=# SELECT CURRENT_TIME; current_time -------------- 10:14:12 (1 row) m_db=# SELECT CURRENT_TIME(6); current_time ----------------- 10:14:15.512064 (1 row) |
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP/CURRENT_TIMESTAMP([scale])
Description: Returns the datetime when the local function calling starts. This function is an alias of NOW.
The parameter scale specifies the precision of the microsecond part. The value is an integer ranging from 0 to 6. The default value is 0.
Return type: DATETIME
Examples:
1 2 3 4 5 6 7 8 9 10 11 |
m_db=# SELECT CURRENT_TIMESTAMP; current_timestamp --------------------- 2023-12-09 11:20:04 (1 row) m_db=# SELECT CURRENT_TIMESTAMP(6); current_timestamp ---------------------------- 2023-12-09 11:20:07.059582 (1 row) |
CURTIME
CURTIME([scale])
Description: Returns the start time of the local function calling, excluding the date. This function is an alias of CURRENT_TIME.
The parameter scale specifies the precision of the microsecond part. The value is an integer ranging from 0 to 6. The default value is 0.
Return type: TIME
Examples:
1 2 3 4 5 6 7 8 9 10 11 |
m_db=# SELECT CURTIME(); curtime ---------- 11:24:03 (1 row) m_db=# SELECT CURTIME(6); curtime ----------------- 11:24:05.590022 (1 row) |
DATE()
DATE(expr)
Description: Returns the date part of expr.
For the parameter expr, the value can be TEXT, DATETIME, DATE, TIME, or a time or date expression.
Return value type: DATE
Examples:
1 2 3 4 5 |
m_db=# SELECT DATE('2023-01-01 10:11:12'); date ------------ 2023-01-01 (1 row) |
DATEDIFF
DATEDIFF(date1, date2)
Description: Returns the number of days between date1 and date2. Only the date part of the parameter is used in the calculation. The time part is ignored.
Parameters:
- date1: time expression, TEXT, DATETIME, DATE, or TIME.
- date2: time expression, TEXT, DATETIME, DATE, or TIME.
Return type: INT
Example:
m_db=# SELECT DATEDIFF('2018-05-01', '2018-04-01');
datediff
----------
30
(1 row)
DATE_ADD
DATE_ADD(date, INTERVAL expr unit)
Description: Adds an interval on a specified date and returns the calculation result.
For details about the parameters, see the following table.
|
Parameter |
Type |
Description |
Value Range |
|---|---|---|---|
|
date |
Time expression, TEXT, DATETIME, DATE, or TIME. |
Specifies the datetime reference. |
See the value ranges of the corresponding types. |
|
expr |
Integer, floating-point number, string, or expression. |
Specifies the time interval, which can be a negative value. |
See the value ranges of the corresponding types. |
|
unit |
Keyword. |
Specifies the unit of the interval. |
YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, and MICROSECOND. For details, see Time Interval Expressions. |
Return value type: TEXT, DATE, DATETIME, or TIME.
Examples:
m_db=# SELECT DATE_ADD('2018-05-01', INTERVAL 1 DAY);
adddate
------------
2018-05-02
(1 row)
DATE_FORMAT
DATE_FORMAT(date, format)
Description: Outputs the datetime in the specified format.
For details about the parameters, see the following table.
|
Parameter |
Type |
Description |
Value Range |
|---|---|---|---|
|
date |
Time expression, TEXT, DATETIME, DATE, or TIME. |
Date to be formatted. |
See the value ranges of the corresponding types. |
|
format |
TEXT. |
Formatted string. |
For details, see Table 1. |
|
Format Character |
Description |
|
%a |
Abbreviation of a week (Sun, ..., Sat). |
|
%b |
Abbreviation of a month (Jan, ..., Dec). |
|
%c |
Month number (0, ..., 12). |
|
%D |
Every day in a month with an English prefix (0th, 1st, 2nd, 3rd, ...). |
|
%d |
Two-digit representation of every day in a month (00, ..., 31). |
|
%e |
Sequence number of every day in a month (0, ..., 31). |
|
%f |
Microsecond (000000, ..., 999999). |
|
%H |
Hour (00, ..., 23). |
|
%h |
Hour (01, ..., 12). |
|
%I |
Hour (01, ..., 12). |
|
%i |
Minute (00, ..., 59). |
|
%j |
Every day in a year (001, ..., 366). |
|
%k |
Hour (0, ..., 23). |
|
%l |
Hour (1, ..., 12). |
|
%M |
Month name (January, ..., December). |
|
%m |
Two-digit month (00, ..., 12). |
|
%p |
AM or PM. |
|
%r |
12-hour time (hh:mm:ss followed by AM or PM). |
|
%S |
Second (00, ..., 59). |
|
%s |
Second (00, ..., 59). |
|
%T |
24-hour time (hh:mm:ss). |
|
%U |
Week of a year (00, ..., 53). Each week starts from Sunday. |
|
%u |
Week of a year (00, ..., 53). Each week starts from Monday. |
|
%V |
Week of a year (01, ..., 53). Each week starts from Sunday. |
|
%v |
Week of a year (01, ..., 53). Each week starts from Monday. |
|
%W |
Name of a week (Sunday, ..., Saturday). |
|
%w |
Every day in a week (0 = Sunday, ..., 6 = Saturday). |
|
%X |
Week of a year. Each week starts from Sunday. The value is a four-digit number and is used for %V. |
|
%x |
Week of a year. Each week starts from Monday. The value is a four-digit number and is used for %v. |
|
%Y |
Four-digit year. |
|
%y |
Two-digit year. |
|
%% |
Alphanumeric character %. |
|
%x |
Any x not listed above. |
Return value type: TEXT
Examples:
m_db=# SELECT DATE_FORMAT('2023-10-11 12:13:14.151617','%b %c %M %m');
date_format
-------------------
Oct 10 October 10
(1 row)
DATE_SUB
DATE_SUB(date, INTERVAL expr unit)
Description: Subtracts a period of time from a specified date and returns the calculation result.
For details about the parameters, see the following table.
|
Parameter |
Type |
Description |
Value Range |
|---|---|---|---|
|
date |
Time expression, TEXT, DATETIME, DATE, or TIME. |
Specifies the datetime reference. |
See the value ranges of the corresponding types. |
|
expr |
Integer, floating-point number, string, or expression. |
Specifies the time interval, which can be a negative value. |
See the value ranges of the corresponding types. |
|
unit |
Keyword. |
Specifies the unit of the interval. |
YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, and MICROSECOND. For details, see Time Interval Expressions. |
Return value type: TEXT, DATE, DATETIME, or TIME.
Examples:
m_db=# SELECT DATE_SUB('2018-05-01', INTERVAL 1 DAY);
date_sub
------------
2018-04-30
(1 row)
DAY
DAY(date)
Description: Extracts the number of days in a datetime expression and returns the result. This function is an alias of DAYOFMONTH.
The parameter date specifies the datetime to be extracted. The value can be a time type expression, TEXT, DATETIME, DATE, or TIME.
Return type: INT
Examples:
m_db=# SELECT DAY('2018-05-12');
day
-----
12
(1 row)
DAYNAME
DAYNAME(date)
Description: Returns the name of a day in a week. The language used by the return value is specified by the GUC parameter lc_time_names.
The parameter date specifies the datetime. The value can be a time type expression, TEXT, DATETIME, DATE, or TIME.
Return value type: TEXT
Examples:
m_db=# SELECT DAYNAME('2018-05-12');
dayname
----------
Saturday
(1 row)
DAYOFMONTH
DAYOFMONTH(date)
Description: Extracts the number of days in a datetime expression and returns the result. This function is an alias of DAY.
The parameter date specifies the datetime to be extracted. The value can be a time type expression, TEXT, DATETIME, DATE, or TIME.
Return type: INT
Examples:
m_db=# SELECT DAYOFMONTH('2018-05-12');
dayofmonth
------------
12
(1 row)
DAYOFWEEK
DAYOFWEEK(date)
Description: Returns the working day index of a date (1 = Sunday, 2 = Monday, ..., 7 = Saturday).
The parameter date specifies the datetime. The value can be a time type expression, TEXT, DATETIME, DATE, or TIME.
Return type: BIGINT
Examples:
m_db=# SELECT DAYOFWEEK('2023-04-16');
dayofweek
-----------
1
(1 row)
DAYOFYEAR
DAYOFYEAR(date)
Description: Returns the number of a day in the year.
The parameter date specifies the datetime. The value can be a time type expression, TEXT, DATETIME, DATE, or TIME.
Return type: integer; value range: [1,366].
Examples:
m_db=# SELECT DAYOFYEAR('2000-12-31');
dayofyear
-----------
366
(1 row)
EXTRACT
EXTRACT(unit FROM date)
Description: Returns the unit value of date as an integer. If unit specifies multiple parts, all values are concatenated in sequence.
Parameters:
- unit: specifies the unit of the time interval. For details, see Time Interval Expressions.
- The parameter date specifies the datetime. The value can be a time type expression, TEXT, DATETIME, DATE, or TIME.
Return type: BIGINT
Examples:
m_db=# SELECT EXTRACT(DAY FROM '2000-12-31');
extract
---------
31
(1 row)
m_db=# SELECT EXTRACT(WEEK FROM '2000-12-31');
extract
---------
53
(1 row)
m_db=# SELECT EXTRACT(YEAR_MONTH FROM '2000-12-31');
extract
---------
200012
(1 row)
FROM_DAYS
FROM_DAYS(days)
Description: Returns the date corresponding to days. The number of days refers to the number of days since 0000-01-01.
The parameter days specifies the specified number of days.
Return value type: DATE
Examples:
m_db=# SELECT FROM_DAYS(50000); from_days ------------ 0136-11-23 (1 row)
FROM_UNIXTIME
FROM_UNIXTIME(unix_timestamp[, format])
Description: Converts a Unix timestamp to the datetime format. A Unix timestamp is the number of seconds from 08:00:00 UTC on January 1, 1970 to a specified time.
Parameters:
- unix_timestamp: Unix timestamp.
- format: Returns a datetime string in the specified format.
- For details about the return format, see the DATE_FORMAT function.
Return value type: TEXT or DATETIME
Examples:
m_db=# SELECT FROM_UNIXTIME(1111885200);
from_unixtime
---------------------
2005-03-27 09:00:00
(1 row)
m_db=# SELECT FROM_UNIXTIME(1447430881, '%Y %D %M %h:%i:%s %x');
from_unixtime
----------------------------------
2015 14th November 12:08:01 2015
(1 row)
GET_FORMAT
GET_FORMAT({DATE | TIME | DATETIME | TIMESTAMP}, {'EUR' | 'USA' | 'JIS' | 'ISO' | 'INTERNAL'})
Description: Returns a string in a specified format, that is, the year, month, day, hour, minute, and second formats and sorting standards of different regions.
Parameters:
- DATE|TIME|DATETIME|TIMESTAMP: time types, which are keywords.
- 'EUR' | 'USA' | 'JIS' | 'ISO' | 'INTERNAL': five time formats, which are of the TEXT type.
Return value type: TEXT
Examples:
m_db=# SELECT GET_FORMAT(DATE, 'EUR'); get_format ------------ %d.%m.%Y (1 row) m_db=# SELECT GET_FORMAT(DATE, 'USA'); get_format ------------ %m.%d.%Y (1 row)
HOUR
HOUR(expr)
Description: Returns the hour part of expr.
The parameter time specifies the time. The value can be a time type expression, TEXT, DATETIME, DATE, or TIME.
Return type: INT
Examples:
m_db=# SELECT HOUR('10:11:12');
hour
------
10
(1 row)
LAST_DAY
LAST_DAY(date)
Description: Returns the date of the last day of the month specified by date.
The parameter date specifies the datetime. The value can be a time type expression, TEXT, DATETIME, DATE, or TIME.
Return value type: DATE
Examples:
m_db=# SELECT LAST_DAY('2023-01-01');
last_day
------------
2023-01-31
(1 row)
m_db=# SELECT LAST_DAY('2023-01-01 10:11:12');
last_day
------------
2023-01-31
(1 row)
LOCALTIME
LOCALTIME/LOCALTIME([scale])
Description: Returns the datetime when the local function calling starts. LOCALTIME and LOCALTIME() are synonyms of NOW.
The parameter scale specifies the precision of the microsecond part. The value is an integer ranging from 0 to 6. The default value is 0.
Return type: DATETIME
Examples:
m_db=# SELECT LOCALTIME;
localtime
---------------------
2023-12-09 17:58:18
(1 row)
m_db=# SELECT LOCALTIME(6);
localtime
----------------------------
2023-12-09 17:58:21.283227
(1 row)
LOCALTIMESTAMP
LOCALTIMESTAMP/LOCALTIMESTAMP([scale])
Description: Returns the current datetime. LOCALTIMESTAMP and LOCALTIMESTAMP() are synonyms of NOW.
The parameter scale specifies the precision of the microsecond part. The value is an integer ranging from 0 to 6. The default value is 0.
Return type: DATETIME
Examples:
m_db=# SELECT LOCALTIMESTAMP;
localtimestamp
---------------------
2023-12-09 18:00:34
(1 row)
m_db=# SELECT LOCALTIMESTAMP(6);
localtimestamp
----------------------------
2023-12-09 18:01:03.053427
(1 row)
MAKEDATE
MAKEDATE(year, dayofyear)
Description: Returns a date based on the given year and day.
Parameters:
- year: BIGINT, indicating the specified year.
- dayofyear: BIGINT, indicating the number of days in the year. The value can cross years. If the value is less than or equal to 0, null is returned.
Return value type: DATE
Examples:
m_db=# SELECT MAKEDATE(2000, 60); makedate ------------ 2000-02-29 (1 row)
MAKETIME
MAKETIME(hour, minute, second)
Description: Generates a specified time value based on the input parameters hour, minute, and second.
Parameters:
- hour: value corresponding to the hour part.
- minute: value corresponding to the minute part.
- second: value corresponding to the second part, which can contain the decimal part.
Return type: TIME
Examples:
1 2 3 4 5 6 7 8 9 10 11 |
m_db=# SELECT MAKETIME(8, 15, 26.53); maketime ------------- 08:15:26.53 (1 row) m_db=# SELECT MAKETIME(-838, 15, 26.53); maketime --------------- -838:15:26.53 (1 row) |
MICROSECOND
MICROSECOND(expr)
Description: Returns the number of microseconds in expr.
For the parameter expr, the value can be TEXT, DATETIME, DATE, TIME, or a time or date expression.
Return type: BIGINT
Examples:
m_db=# SELECT MICROSECOND('2023-5-5 10:10:10.24485');
microsecond
-------------
244850
(1 row)
MINUTE
MINUTE(expr)
Description: Returns the number of minutes in expr.
For the parameter expr, the value can be TEXT, DATETIME, DATE, TIME, or a time or date expression.
Return type: INT
Examples:
m_db=# SELECT MINUTE('10:11:12');
minute
--------
11
(1 row)
MONTH
MONTH(date)
Description: Extracts the month part of the datetime and returns the result.
The parameter date specifies the datetime to be extracted. The value can be a time type expression, TEXT, DATETIME, DATE, or TIME.
Return type: INT
Examples:
m_db=# SELECT MONTH('2018-05-12');
month
-------
5
(1 row)
MONTHNAME
MONTHNAME(date)
Description: Returns the full month name of a date.
The parameter date specifies the datetime. The value can be a time type expression, TEXT, DATETIME, DATE, or TIME.
Return value type: TEXT
Examples:
m_db=# SELECT MONTHNAME('2018-05-12');
monthname
-----------
May
(1 row)
NOW
NOW([scale])
Description: Returns the datetime when the local function calling starts. The datetime are affected by the time zone setting.
The parameter scale specifies the precision of the microsecond part. The value is an integer ranging from 0 to 6. The default value is 0.
Return type: DATETIME
Examples:
m_db=# SELECT NOW();
now
---------------------
2023-12-11 09:46:56
(1 row)
m_db=# SELECT NOW(6);
now
---------------------------
2023-12-11 09:46:58.404811
(1 row)
PERIOD_ADD
PERIOD_ADD(period, month_number)
Description: Adds a specified number of months to a date in the specified format and returns the date in YYYYMM format.
Parameters:
- period: date in the format of YYYYMM or YYMM.
- month_number: number of months to be added. The value can be a negative number.
Return type: BIGINT.
Examples:
m_db=# SELECT PERIOD_ADD(202205, -12);
period_add
------------
202105
(1 row)
PERIOD_DIFF
PERIOD_DIFF(P1, P2)
Description: Calculates the month difference between two dates.
Parameter: P1 and P2 are dates in the YYMM or YYYYMM format.
Return value type: BIGINT (month difference)
Examples:
m_db=# SELECT PERIOD_DIFF('202101', '202102');
period_diff
-------------
-1
(1 row)
QUARTER
QUARTER(date)
Description: Returns the quarter value of a specified date. The value ranges from 1 to 4
The parameter date specifies the datetime. The value can be a time type expression, TEXT, DATETIME, DATE, or TIME.
Return type: INT
Examples:
m_db=# SELECT QUARTER('2018-05-12');
quarter
---------
2
(1 row)
SECOND
SECOND(expr)
Description: Returns the number of seconds in expr.
Parameter: expr, indicating time. The value can be a time expression, TEXT, DATETIME, DATE, or TIME.
Return type: INT
Example:
m_db=# SELECT SECOND('10:11:12');
second
--------
12
(1 row)
SEC_TO_TIME
SEC_TO_TIME(seconds)
Description: Converts the number of seconds into a time of the TIME type.
The parameter seconds indicates the specified number of seconds. The value range is [-3020399, +3020399].
Return type: TIME
Example:
m_db=# SELECT SEC_TO_TIME(3020399); sec_to_time ------------------ 838:59:59.000000 (1 row)
STR_TO_DATE
STR_TO_DATE(str, format)
Description: Converts a specified string to a date or time based on the specified date format.
Parameters:
- str: string to be formatted into a date.
- format: formatted string. For details, see Table 1.
Return value type: DATETIME, DATE, or TIME
Example:
m_db=# SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');
str_to_date
-------------
2013-05-01
(1 row)
SUBDATE
- SUBDATE(date, INTERVAL expr unit)
Description: Subtracts a period of time from a specified date and returns the calculation result. The function and usage of this form of function is the same as those of DATE_SUB.
For details about the parameters, see the following table.
Parameter
Type
Description
Value Range
date
Time expression, TEXT, DATETIME, DATE, or TIME.
Specifies the datetime reference.
See the value ranges of the corresponding types.
expr
Integer, floating-point number, string, or expression.
Specifies the time interval, which can be a negative value.
See the value ranges of the corresponding types.
unit
Keyword.
Specifies the unit of the interval.
YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, or MICROSECOND. For details, see Time Interval Expressions.
Return value type: TEXT, DATE, DATETIME, or TIME.
Example:
m_db=# SELECT SUBDATE('2018-05-01', INTERVAL 1 DAY); subdate ------------ 2018-04-30 (1 row)
- SUBDATE(date, days)
Description: Subtracts the number of days specified by days from the date specified by date and returns the calculation result.
For details about the parameters, see the following table.
Parameter
Type
Description
Value Range
date
Time expression, TEXT, DATETIME, DATE, or TIME.
Specifies the datetime reference.
See the value ranges of the corresponding types.
days
Integer, floating-point number, or string.
Specifies the time interval, which can be a negative value.
See the value ranges of the corresponding types.
Return value type: TEXT, DATE, DATETIME, or TIME.
Example:
m_db=# SELECT SUBDATE('2018-05-01', 1); subdate ------------ 2018-04-30 (1 row)
SUBTIME
SUBTIME(expr1, expr2)
Description: Returns the value obtained by subtracting expr2 from expr1. The format of the return value is the same as that of expr1.
- expr1: specifies the time or datetime expression.
- expr2: specifies the time expression.
Return value type: TEXT, DATETIME, or TIME. The return value type is related to the expr1 type. If expr1 is parsed as DATETIME, DATETIME is returned. If expr1 is parsed as TIME, TIME is returned.
Example:
m_db=# SELECT SUBTIME('2000-03-01 20:59:59', '00:00:01');
subtime
---------------------
2000-03-01 20:59:58
(1 row)
m_db=# SELECT SUBTIME('20:59:59', '00:00:01');
subtime
----------
20:59:58
(1 row)
SYSDATE
SYSDATE([precision])
Description: Returns the system date and time when a function is executed.
The parameter precision specifies the precision of the microsecond part. The value is an integer ranging from 0 to 6. The default value is 0.
Return type: DATETIME
Example:
1 2 3 4 5 6 7 8 9 10 11 |
m_db=# SELECT SYSDATE(); sysdate --------------------- 2023-12-11 11:28:29 (1 row) m_db=# SELECT SYSDATE(6); sysdate ---------------------------- 2023-12-11 11:28:31.535374 (1 row) |
TIME
TIME(expr)
Description: Returns the time part of expr.
For the parameter expr, the value can be TEXT, DATETIME, DATE, TIME, or a time or date expression.
Return type: TIME
Example:
1 2 3 4 5 |
m_db=# SELECT TIME('2023-01-01 10:11:12'); time ---------- 10:11:12 (1 row) |
TIMEDIFF
TIMEDIFF(date1, date2)
Description: Returns the interval between two dates in TIME type.
Parameter: date1 or date2, which can be a time expression, TEXT, DATETIME, DATE, or TIME.
Return type: TIME
Example:
m_db=# SELECT TIMEDIFF(date'2022-12-30',20221229); timediff ---------- 24:00:00 (1 row)
TIMESTAMP
- TIMESTAMP(expr)
Description: Returns the datetime value of expr.
Parameter: time type expression, TEXT, DATETIME, DATE, or TIME.
Return type: DATETIME
Example:
m_db=# SELECT TIMESTAMP('2022-12-30'); timestamp --------------------- 2022-12-30 00:00:00 (1 row)
- TIMESTAMP(expr1, expr2)
Description: Returns the sum of expr1 and expr2. NULL is returned if expr2 contains a date.
Parameter: time type expression, TEXT, DATETIME, DATE, or TIME.
Return type: DATETIME
Example:
m_db=# SELECT TIMESTAMP('2022-12-30', '12:00:00'); timestamp --------------------- 2022-12-30 12:00:00 (1 row)
TIMESTAMPADD
TIMESTAMPADD(unit, interval, datetime_expr)
Description: Returns a new datetime, which is calculated by adding multiple intervals of a unit to datetime_expr.
Parameters: The following table describes the parameters.
|
Parameter |
Type |
Description |
Value Range |
|---|---|---|---|
|
unit |
Keyword. |
Unit of the interval. |
YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, or MICROSECOND. |
|
interval |
Numeric expression. |
Interval. |
See the value ranges of the corresponding types. |
|
datetime_expr |
Time expression, TEXT, DATETIME, DATE, or TIME. |
Reference value of the time and date. |
See the value ranges of the corresponding types. |
Return value type: TEXT, DATE, DATETIME, or TIME.
Example:
m_db=# SELECT TIMESTAMPADD(DAY,-2,'2022-07-27'); timestampadd -------------- 2022-07-25 (1 row)
TIMESTAMPDIFF
TIMESTAMPDIFF(unit, date1, date2)
Description: Returns the interval between two dates in the form of unit.
Parameters: The following table describes the parameters.
|
Parameter |
Type |
Description |
Value Range |
|---|---|---|---|
|
unit |
Keyword. |
Unit of the interval. |
YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, or MICROSECOND. |
|
date1 |
Time expression, TEXT, DATETIME, DATE, or TIME. |
Datetime. |
See the value ranges of the corresponding types. |
|
date2 |
Time expression, TEXT, DATETIME, DATE, or TIME. |
Datetime. |
See the value ranges of the corresponding types. |
Return type: BIGINT
Example:
m_db=# SELECT TIMESTAMPDIFF(SECOND, '2023-01-01 10:11:12', '2023-01-01 05:00:00');
timestampdiff
---------------
-18672
(1 row)
TIME_FORMAT
TIME_FORMAT(time, format)
Description: Formats the time input parameter based on the format specified by format.
Parameters:
- time: time expression, TEXT, DATETIME, DATE, or TIME.
- format: formatted string. The following table describes the values and meanings.
Format Character
Description
%f
Microsecond (000000–999999)
%H
Hour (00 to 23)
%h, %I
Hour (00 to 12)
%l
Hour (0 to 12)
%k
Hour (0 to 838)
%i
Minute (00 to 59)
%p
AM or PM
%r
Time in 12-hour AM or PM format (hh:mm:ss AM/PM)
%S, %s
Second (00 to 59)
%T
Time in 24-hour format (hh:mm:ss)
%a, %b, %D, %j, %M, %U, %u, %V, %v, %W, %w, %X, %x
Null
%c, %e
0
%d, %m, %y
00
%Y
0000
%Other characters/Other characters, for example, %A/A
The character itself is returned, for example, A.
%Single character + string s
%Single character is parsed and then concatenated with s.
Return value type: TEXT
Example:
m_db=# SELECT TIME_FORMAT('25:30:30', '%T|%r|%H|%h|%I|%i|%S|%f|%p|%k');
time_format
--------------------------------------------------
25:30:30|01:30:30 AM|25|01|01|30|30|000000|AM|25
(1 row)
TIME_TO_SEC
TIME_TO_SEC(time)
Description: Converts the input parameter of the TIME type to the number of seconds.
The parameter time is a time type expression, TEXT, DATETIME, DATE, or TIME. The time expression is parsed as TIME.
Return type: INT
Example:
m_db=# SELECT TIME_TO_SEC('10:11:12');
time_to_sec
-------------
36672
(1 row)
TO_DAYS
TO_DAYS(date)
Description: Returns the number of days between a specified date and 0000-01-01.
Parameter: The input parameter is a time expression, TEXT, DATETIME, DATE, or TIME. The time expression is parsed as DATE.
Return type: INT
Example:
m_db=# SELECT TO_DAYS('2000-1-1');
to_days
---------
730485
(1 row)
TO_SECONDS
TO_SECONDS(expr)
Description: Returns the number of seconds since year 0 to expr.
Parameter: The input parameter is a time or datetime expression, TEXT, DATETIME, DATE, or TIME type. The time expression is parsed as DATETIME.
Return type: BIGINT
Example:
m_db=# SELECT TO_SECONDS('2009-11-29 13:43:32');
to_seconds
-------------
63426721412
(1 row)
UNIX_TIMESTAMP
UNIX_TIMESTAMP([date])
Description:
- If date is not specified, the number of seconds between the current time and 1970-01-01 00:00:00 is returned, which is affected by the setting of the time zone parameter.
- If date is specified, the number of seconds between the specified time and 1970-01-01 00:00:00 is returned, which is affected by the setting of the time zone parameter.
Parameter: time type expression, TEXT, DATETIME, DATE, or TIME.
Return value type: DECIMAL or BIGINT. If the input parameter contains a decimal part or the input parameter is an invalid string of the time type, DECIMAL is returned. Otherwise, BIGINT is returned.
Example:
m_db=# SELECT UNIX_TIMESTAMP('2022-12-22');
unix_timestamp
----------------
1671638400
(1 row)
UTC_DATE
UTC_DATE/UTC_DATE()
Description: Returns the current UTC date of function execution as a value in YYYY-MM-DD format.
Return value type: DATE
Example:
m_db=# SELECT UTC_DATE; utc_date ------------ 2023-12-11 (1 row) m_db=# SELECT UTC_DATE(); utc_date ------------ 2023-12-11 (1 row)
UTC_TIME
UTC_TIME/UTC_TIME([scale])
Description: Returns the current UTC time of function execution as a value in HH:MM:SS format.
The parameter scale specifies the precision of the microsecond part. The value is an integer ranging from 0 to 6. The default value is 0.
Return type: TIME
Example:
m_db=# SELECT UTC_TIME;
utc_time
----------
07:10:49
(1 row)
m_db=# SELECT UTC_TIME();
utc_time
----------
07:10:49
(1 row)
m_db=# SELECT UTC_TIME(6);
utc_time
-----------------
07:10:51.445213
(1 row)
UTC_TIMESTAMP
UTC_TIMESTAMP/UTC_TIMESTAMP([scale])
Description: Returns the current UTC timestamp of function execution as a value in YYYY-MM-DD HH:MM:SS format.
The parameter scale specifies the precision of the microsecond part. The value is an integer ranging from 0 to 6. The default value is 0.
Return type: DATETIME
Example:
m_db=# SELECT UTC_TIMESTAMP;
utc_timestamp
---------------------
2023-12-11 07:12:02
(1 row)
m_db=# SELECT UTC_TIMESTAMP();
utc_timestamp
---------------------
2023-12-11 07:12:04
(1 row)
m_db=# SELECT UTC_TIMESTAMP(3);
utc_timestamp
-------------------------
2023-12-11 07:12:06.413
(1 row)
WEEK
WEEK(date[, mode])
Description: Returns the number of weeks of a date. The number of weeks is calculated according to ISO 8601:1988. In the ISO definition, the first few days of January may be the 52nd or 53rd week of the previous year, and the last few days of December may be the first week of the following year.
Parameters:
- date: specifies the datetime, which is a time type expression, TEXT, DATETIME, DATE, or TIME.
- When mode parameters are omitted, the value of the system variable default_week_format is used. The default value is 0. When mode parameters are specified, you can specify the start of a week and the range of return values. The following table describes the meanings of optional mode parameters. The default value is 0.
Table 2 Values and meanings of mode parameters mode
First Day of a Week
Return Value Range
First Week in January
0
Sunday
0~53
The first week with Sunday.
1
Monday
0~53
The first week with four days or more.
2
Sunday
1~53
The first week with Sunday.
3
Monday
1~53
The first week with four days or more.
4
Sunday
0~53
The first week with four days or more.
5
Monday
0~53
The first week with Monday.
6
Sunday
1~53
The first week with four days or more.
7
Monday
1~53
The first week with Monday.
Return type: BIGINT
Example:
m_db=# SELECT WEEK('2000-01-01');
week
------
0
(1 row)
m_db=# SELECT WEEK('2000-01-01', 2);
week
------
52
(1 row)
WEEKDAY
WEEKDAY(date)
Description: Returns the working day index of a date, that is, Monday is 0, Tuesday is 1, Wednesday is 2, Thursday is 3, Friday is 4, Saturday is 5, and Sunday is 6.
Parameter: time type expression, TEXT, DATETIME, DATE, or TIME.
Return type: INT
Example:
m_db=# SELECT WEEKDAY('1970-01-01 12:00:00');
weekday
---------
3
(1 row)
WEEKOFYEAR
WEEKOFYEAR(date)
Description: Returns the calendar week of the datetime. The value range is [1, 53]. It is equivalent to WEEK(date, 3).
Parameter: time type expression, TEXT, DATETIME, DATE, or TIME.
Return type: INT
Example:
m_db=# SELECT WEEKOFYEAR('1970-05-22');
weekofyear
------------
21
(1 row)
YEAR
YEAR(date)
Description: Extracts the year part of the datetime and returns the result.
The parameter date specifies the datetime to be extracted. The value can be a time type expression, TEXT, DATETIME, DATE, or TIME.
Return type: INT.
Example:
m_db=# SELECT YEAR('2018-05-12');
year
------
2018
(1 row)
YEARWEEK
YEARWEEK(date[, mode])
Description: Returns the year and week of a date.
Parameters:
- date: specifies the datetime, which is a time type expression, TEXT, DATETIME, DATE, or TIME.
- For details about the optional mode parameters, see Table 2.
Return type: BIGINT
Example:
m_db=# SELECT YEARWEEK('2000-01-01', 3);
yearweek
----------
199952
(1 row)
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot
