Date and Time Processing Functions and Operators
Date and Time Operators
Table 1 describes the time and date operators.
Do not use expressions similar to 'now'::date, 'now'::timestamp,'now'::timestamptz (for string constant conversion) and text_date('now') to obtain the current database time or use the current time as the input parameter of the function. In these scenarios, the optimizer calculates the constant time in advance, causing incorrect query results.
gaussdb=# EXPLAIN SELECT * FROM t1 WHERE b='now'::date;
QUERY PLAN
-----------------------------------------------------
Seq Scan on t1 (cost=0.00..13.60 rows=1 width=310)
Filter: ((b)::text = '2024-11-09 15:07:56'::text)
(2 rows)
gaussdb=# EXPLAIN SELECT * FROM t1 WHERE b=text_date('now');
QUERY PLAN
-----------------------------------------------------
Seq Scan on t1 (cost=0.00..13.60 rows=1 width=310)
Filter: ((b)::text = '2024-11-09'::text)
(2 rows)
You are advised to use the now() and currenttimestamp() functions to obtain the current time of the database.
gaussdb=# EXPLAIN SELECT * FROM t1 WHERE b=now(); QUERY PLAN ----------------------------------------------------- Seq Scan on t1 (cost=0.00..14.80 rows=1 width=310) Filter: ((b)::text = (now())::text) (2 rows) gaussdb=# EXPLAIN SELECT * FROM t1 WHERE b=text_date(now()); QUERY PLAN ---------------------------------------------------------- Seq Scan on t1 (cost=0.00..16.00 rows=1 width=310) Filter: ((b)::text = (text_date((now())::text))::text) (2 rows)
When the user uses date and time operators, explicit type prefixes are modified for corresponding operands to ensure that the operands parsed by the database are consistent with what the user expects, and no unexpected results occur.
For example, abnormal mistakes will occur in the following example without an explicit data type.
1 2 3 4 5 6 7 |
gaussdb=# SELECT date '2001-10-01' - '7' AS RESULT; ERROR: GAUSS-10416: invalid input syntax for type timestamp: "7" SQLSTATE: 22007 LINE 1: SELECT date '2001-10-01' - '7' AS RESULT; ^ CONTEXT: referenced column: result |
|
Operator |
Example |
||
|---|---|---|---|
|
+ |
NOTE:
In ORA-compatible mode, the query result is 2001-10-05 00:00:00. |
||
|
|||
|
|||
|
|||
|
|||
|
|||
|
- |
|
||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|||
|
* |
|
||
|
|||
|
|||
|
/ |
|
Time and Date Functions
- age(timestamp, timestamp)
Description: Subtracts parameters, producing a result in YYYY-MM-DD format. If the result is negative, the returned result is also negative. The input parameters may or may not contain time zones.
Return type: interval
Example:
1 2 3 4 5
gaussdb=# SELECT age(timestamp '2001-04-10', timestamp '1957-06-13'); age ------------------------- 43 years 9 mons 27 days (1 row)
- age(timestamp)
Description: Subtracts the parameter value from the system time when the current SQL statement starts to be executed. The input parameter may or may not contain a time zone.
Return type: interval
Example:
1 2 3 4 5
gaussdb=# SELECT age(timestamp '1957-06-13'); age ------------------------- 60 years 2 mons 18 days (1 row)
- clock_timestamp()
Description: Returns the timestamp of the system time when the current function is called. The volatile function obtains the latest timestamp for each scan. Therefore, the result of each call in a query is different.
Return type: timestamp with time zone
Example:
1 2 3 4 5
gaussdb=# SELECT clock_timestamp(); clock_timestamp ------------------------------- 2017-09-01 16:57:36.636205+08 (1 row)
- current_date
Description: Returns the system date when the current SQL statement starts.
Return type: date
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13
gaussdb=# SELECT current_date; date ------------ 2017-09-01 (1 row) -- When the GUC parameter a_format_date_timestamp is enabled in ORA-compatible mode: gaussdb=# SET a_format_date_timestamp=on; SET gaussdb=# SELECT current_date; current_date --------------------- 2023-11-24 11:25:09 (1 row)
- This function has the following behaviors when sql_compatibility is set to 'ORA' and a_format_date_timestamp is set to on:
- The timestamp of the system time is returned when the current SQL execution starts.
- The return value type is timestamp without time zone. The value unit is seconds. The column name is current_date.
- When a_format_version is set to 10c and a_format_dev_version is set to s2, the return value type is timestamp.
- If the GUC parameter a_format_date_timestamp is disabled, the system date when the transaction starts is returned.
- This prevents the optimizer from obtaining the constant time in advance. As a result, the obtained time is incorrect in the gplan scenario.
- This function has the following behaviors when sql_compatibility is set to 'MYSQL', b_format_version is set to 5.7, and b_format_dev_version is set to s1:
- current_date can be called with parentheses.
- The actually called function of current_date is curdate. You can run the \df curdate command to query the detailed input parameters and return values of the function.
- This function has the following behaviors when sql_compatibility is set to 'ORA' and a_format_date_timestamp is set to on:
- current_time()
Description: Specifies the system time when the current transaction starts. If sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1', the system time when the current SQL execution starts is returned.
Return type: time with time zone. When sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1', the return type is time without time zone.
Example:
1 2 3 4 5 6 7 8 9 10 11
gaussdb=# SELECT current_time; timetz -------------------- 16:58:07.086215+08 (1 row) -- When the parameter is enabled in MySQL-compatible mode: gaussdb_m=# SELECT current_time; current_time -------------- 15:14:00 (1 row)
- current_time([precision])
Description: Returns the system time when the current SQL execution starts.
Parameter: precision indicates the precision (number of decimal places after the second). The value is of the int type and in the range [0,6]. The default value is 0. If the precision is invalid, an error is reported.
Return type: time without time zone
Implementation mode: This function is mapped to the system function curtime.
Example:
gaussdb_m=# SELECT current_time(); current_time -------------- 15:14:05 (1 row) gaussdb_m=# SELECT current_time(3); current_time -------------- 15:14:08.433 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'. The actually called function of curent_time is curtime. You can run the \df curtime command to query the detailed input parameters and return values of the function.
- current_timestamp
Description: Specifies the current date and time. This is a statement-level timestamp. The returned results within the same statement remain unchanged.
Return type: timestamp without time zone when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1', or timestamp with time zone in other scenarios
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
gaussdb=# SELECT current_timestamp; pg_systimestamp ------------------------------ 2017-09-01 16:58:19.22173+08 (1 row) -- When the version is 5.7 in MySQL-compatible database: gaussdb_m=# SELECT current_timestamp; timestamp --------------------- 2023-08-21 15:08:24 (1 row) -- When the GUC parameter a_format_date_timestamp is enabled in ORA-compatible mode: gaussdb=# SET a_format_date_timestamp=on; SET gaussdb=# SELECT current_timestamp; current_timestamp ------------------------------- 2023-11-24 11:31:04.895312+08 (1 row)
This function has the following behaviors when sql_compatibility is set to 'ORA' and a_format_date_timestamp is set to on:
- The timestamp of the system time is returned when the current SQL execution starts.
- The return value type is timestamp with time zone, and the column name is current_timestamp.
- If the GUC parameter a_format_date_timestamp is disabled, the system time is returned.
This function has the following behaviors when sql_compatibility is set to 'MYSQL', b_format_version is set to 5.7, and b_format_dev_version is set to s1:
- The return type is timestamp without time zone.
- The precision of the returned result is 0.
- The timestamp of the system time is returned when the current SQL execution starts.
- This function is implemented through TYPE conversion and has no registered function. Therefore, you can run the \df+ command of gsql to view the function information in other compatible modes, not the function information in version 5.7 in the MySQL-compatible mode.
- current_timestamp()
Description: Returns the system time when the current SQL execution starts.
Return type: timestamp without time zone
Example:
1 2 3 4 5
gaussdb=# SELECT current_timestamp(); timestamp --------------------- 2023-08-21 14:34:30 (1 row)
This function can be used only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'. In addition, this function has the following behavior:
- The return type is timestamp without time zone.
- The precision of the returned result is 0.
- The timestamp of the system time is returned when the current SQL execution starts.
- This function is implemented through TYPE conversion and has no registered function. Therefore, you can run the \df+ command of gsql to view the function information in other modes, not the function information in version 5.7 in the MySQL-compatible mode.
- current_timestamp(precision)
Description: Obtains the current date and time, and rounds the microseconds of the result to the specified decimal place.
Parameter: precision indicates the precision (number of decimal places after the second). The value is of the int type and in the range [0,6]. The default value is 0. If the value is an integer greater than 6, an alarm is generated, and the maximum precision value 6 is used to output the time. If the value is invalid, an error is reported.
Return type: timestamp without time zone when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1', or timestamp with time zone in other scenarios
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
gaussdb=# SELECT current_timestamp(1); timestamptz ------------------------------ 2017-09-01 16:58:19.2+08 (1 row) -- When the version is 5.7 in MySQL-compatible database: gaussdb_m=# SELECT current_timestamp(1); timestamp ----------------------- 2023-08-21 15:09:35.3 (1 row) -- When the GUC parameter a_format_date_timestamp is enabled in ORA-compatible mode: gaussdb=# SET a_format_date_timestamp=on; SET gaussdb=# SELECT current_timestamp(6); current_timestamp ------------------------------- 2023-11-24 11:35:57.268592+08 (1 row) -- If a_format_version is set to 10c and a_format_dev_version is set to s2 in an ORA-compatible database, precision can be an integer of the numeric type. gaussdb=# SET a_format_version='10c'; SET gaussdb=# SET a_format_dev_version='s2'; SET gaussdb=# SELECT current_timestamp(6.0); current_timestamp ------------------------------ 2023-11-25 11:14:48.74763+08 (1 row)
- Zeros at the end of microseconds are not displayed. For example, 2017-09-01 10:32:19.212000 is displayed as 2017-09-01 10:32:19.212.
- This function has the following behaviors when sql_compatibility is set to 'MYSQL', b_format_version is set to 5.7, and b_format_dev_version is set to s1:
- The return type is timestamp without time zone.
- The timestamp of the system time is returned when the current SQL execution starts.
- This function is implemented through TYPE conversion and has no registered function. Therefore, you can run the \df+ command of gsql to view the function information in other modes, not the function information in version 5.7 in the MySQL-compatible mode.
- The function has the following behaviors when sql_compatibility is set to 'ORA' and a_format_date_timestamp is set to on:
- The return value type is timestamp with time zone, and the column name is current_timestamp.
- The timestamp of the system time is returned when the current SQL execution starts.
- When a_format_version is set to 10c and a_format_dev_version is set to s2, the precision parameter can be an integer of the numeric type. Otherwise, only the int type is supported.
- If the GUC parameter a_format_date_timestamp is disabled, when the input parameter is an integer without a decimal point, the returned result is the date and time of the system where the transaction is started; when the input parameter is an integer with a decimal point, the returned result is the system time.
- pg_systimestamp()
Description: Current date and time (start of the current statement).
Return type: timestamp with time zone
Example:
1 2 3 4 5
gaussdb=# SELECT pg_systimestamp(); pg_systimestamp ------------------------------- 2015-10-14 11:21:28.317367+08 (1 row)
- date_part(text, timestamp)
Retrieves fields such as year or hour from date/time values.
It is equivalent to extract(field from timestamp).
Timestamp types: abstime, date, interval, reltime, time with time zone, time without time zone, timestamp with time zone, timestamp without time zone
Return type: double precision
Example:
1 2 3 4 5
gaussdb=# SELECT date_part('hour', timestamp '2001-02-16 20:38:40'); date_part ----------- 20 (1 row)
- date_part(text, interval)
Description: Obtains the month. If the value is greater than 12, obtain the remainder after it is divided by 12. It is equivalent to extract(field from timestamp).
Return type: double precision
Example:
1 2 3 4 5
gaussdb=# SELECT date_part('month', interval '2 years 3 months'); date_part ----------- 3 (1 row)
- date_trunc(text, timestamp)
Description: Truncates to the precision specified by text.
Return type: interval, timestamp with time zone, timestamp without time zone
Example:
1 2 3 4 5
gaussdb=# SELECT date_trunc('hour', timestamp '2001-02-16 20:38:40'); date_trunc --------------------- 2001-02-16 20:00:00 (1 row)
- trunc(timestamp)
Description: Truncates to day by default.
Example:
1 2 3 4
gaussdb=# SELECT trunc(timestamp '2001-02-16 20:38:40'); trunc --------------------- 2001-02-16 00:00:00 (1 row)
- trunc(arg1, arg2)
Description: Truncates to the precision specified by arg2.
- Type of arg1: interval, timestamp with time zone, timestamp without time zone
- Type of arg2: text
Return type: interval, timestamp with time zone, timestamp without time zone
Example:
1 2 3 4
gaussdb=# SELECT trunc(timestamp '2001-02-16 20:38:40', 'hour'); trunc --------------------- 2001-02-16 20:00:00 (1 row)
- round(arg1, arg2)
Description: Rounds off to the precision specified by arg2.
Type of arg1: timestamp without time zone
Type of arg2: text
Return type: timestamp without time zone
Example:
1 2 3 4
gaussdb=# SELECT round(timestamp '2001-02-16 20:38:40', 'hour'); round --------------------- 2001-02-16 21:00:00 (1 row)
This function is valid only when the value of a_format_version is 10c and that of a_format_dev_version is s1 in an ORA-compatible database.
- daterange(arg1, arg2)
Description: Obtains time boundary information.
arg1 type: date
arg2 type: date
Return type: daterange
Example:
1 2 3 4 5
gaussdb=# SELECT daterange('2000-05-06','2000-08-08'); daterange ------------------------- [2000-05-06,2000-08-08) (1 row)
- daterange(arg1, arg2, text)
Description: Obtains time boundary information.
arg1 type: date
arg2 type: date
text type: text
Return type: daterange
Example:
1 2 3 4 5
gaussdb=# SELECT daterange('2000-05-06','2000-08-08','[]'); daterange ------------------------- [2000-05-06,2000-08-09) (1 row)
- isfinite(date)
Description: Checks whether a date is a finite value. If the date is a finite value, t is returned. Otherwise, f is returned.
Return type: Boolean
Example:
1 2 3 4 5 6 7 8 9 10
gaussdb=# SELECT isfinite(date '2001-02-16'); isfinite ---------- t (1 row) gaussdb=# SELECT isfinite(date 'infinity'); isfinite ---------- f (1 row)
- isfinite(timestamp)
Description: Checks whether a timestamp is a finite value. If the timestamp is a finite value, t is returned. Otherwise, f is returned.
Return type: Boolean
Example:
1 2 3 4 5 6 7 8 9 10
gaussdb=# SELECT isfinite(timestamp '2001-02-16 21:28:30'); isfinite ---------- t (1 row) gaussdb=# SELECT isfinite(timestamp 'infinity'); isfinite ---------- f (1 row)
- isfinite(interval)
Description: Checks whether the interval is a finite value. If the interval is a finite value, t is returned. Currently, f cannot be returned. If 'infinity' is entered, an error is reported.
Return type: Boolean
Example:
1 2 3 4 5
gaussdb=# SELECT isfinite(interval '4 hours'); isfinite ---------- t (1 row)
- justify_days(interval)
Description: Adjusts intervals to 30-day time periods, which are represented as months.
Return type: interval
Example:
1 2 3 4 5
gaussdb=# SELECT justify_days(interval '35 days'); justify_days -------------- 1 mon 5 days (1 row)
- justify_hours(interval)
Description: Sets the time interval in days (24 hours is one day).
Return type: interval
Example:
1 2 3 4 5
gaussdb=# SELECT JUSTIFY_HOURS(INTERVAL '27 HOURS'); justify_hours ---------------- 1 day 03:00:00 (1 row)
- justify_interval(interval)
Description: Adjusts interval using justify_days and justify_hours.
Return type: interval
Example:
1 2 3 4 5
gaussdb=# SELECT JUSTIFY_INTERVAL(INTERVAL '1 MON -1 HOUR'); justify_interval ------------------ 29 days 23:00:00 (1 row)
- localtime()
Description: Specifies the system time when the current transaction starts. If sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1', the system date and time when the current SQL query execution starts is returned.
Return type: time. If sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1', a timestamp without time zone is returned.
Example:
1 2 3 4 5 6 7 8 9 10 11
gaussdb=# SELECT localtime AS RESULT; result ---------------- 16:05:55.664681 (1 row) -- When the parameter is enabled in MySQL-compatible mode: gaussdb_m=# select localtime; localtime --------------------- 2023-08-21 15:21:57 (1 row)
- localtime[([precision])]
Description: Returns the system date and time when the current SQL query execution starts.
Parameter: precision indicates the precision (number of decimal places after the second). The value is of the int type and in the range [0,6]. The default value is 0. If the value is invalid, an error is reported.
Return type: timestamp without time zone
Implementation method: Register the system function localtime.
Example:
1 2 3 4 5 6 7 8 9 10
gaussdb_m=# SELECT localtime(); localtime --------------------- 2023-08-21 15:23:49 (1 row) gaussdb_m=# SELECT localtime(3); localtime ------------------------- 2023-08-21 15:23:51.965 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- localtimestamp
Description: Specifies the current date and time.
Return type: timestamp
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
gaussdb=# SELECT localtimestamp; timestamp ---------------------------- 2017-09-01 17:03:30.781902 (1 row) -- When the parameter is enabled in MySQL-compatible mode: gaussdb_m=# SELECT localtimestamp; timestamp --------------------- 2023-08-21 15:27:55 (1 row) -- When the GUC parameter a_format_date_timestamp is enabled in ORA-compatible mode: gaussdb=# SET a_format_date_timestamp=on; SET gaussdb=# SELECT localtimestamp; localtimestamp ---------------------------- 2023-11-24 11:38:25.633231 (1 row)
This function has the following behaviors when sql_compatibility is set to 'MYSQL', b_format_version is set to 5.7, and b_format_dev_version is set to s1:
- The system date and time are returned when the current SQL query execution starts.
- The return type is timestamp without time zone, and the column name is timestamp.
The function has the following behaviors when sql_compatibility is set to 'ORA' and a_format_date_timestamp is set to on:
- The return value type is timestamp without time zone, and the column name is localtimestamp.
- The timestamp of the system time is returned when the current SQL execution starts.
- If the GUC parameter a_format_date_timestamp is disabled, the system date and time when the transaction is started is returned.
- localtimestamp[([precision])]
Description: Specifies the current date and time.
Parameter: precision indicates the precision (number of decimal places after the second). The value is of the int type and in the range [0,6]. The default value is 0. If the value is an integer greater than 6, an alarm is generated, and the maximum precision value 6 is used to output the time. If the value is invalid, an error is reported.
Return type: timestamp without time zone
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
-- Calls with parentheses and without input parameters are supported only in MySQL-compatible mode. gaussdb_m=# SELECT localtimestamp(); timestamp --------------------- 2023-08-21 15:27:59 (1 row) gaussdb_m=# SELECT localtimestamp(3); timestamp ------------------------- 2023-08-21 15:28:02.445 (1 row) -- When the GUC parameter a_format_date_timestamp is enabled in ORA-compatible mode: gaussdb=# SET a_format_date_timestamp=on; SET gaussdb=# SELECT localtimestamp(6); localtimestamp ---------------------------- 2023-11-24 11:41:14.086227 (1 row) -- If a_format_version is set to 10c and a_format_dev_version is set to s2 in an ORA-compatible database, precision can be an integer of the numeric type. gaussdb=# SET a_format_version='10c'; SET gaussdb=# SET a_format_dev_version='s2'; SET gaussdb=# SELECT localtimestamp(6.0); localtimestamp ---------------------------- 2023-11-24 11:42:45.642167 (1 row)
- Zeros at the end of microseconds are not displayed. For example, 2017-09-01 10:32:19.212000 is displayed as 2017-09-01 10:32:19.212.
- When sql_compatibility is set to 'MYSQL', b_format_version is set to 5.7, and b_format_dev_version is set to s1, this function returns the system date and time when the current SQL execution starts. The function can be called with parentheses without input parameters.
- This function has the following behaviors when sql_compatibility is set to 'ORA' and a_format_date_timestamp is set to on:
- The timestamp of the system time is returned when the current SQL execution starts.
- The return value type is timestamp without time zone, and the column name is localtimestamp.
- When a_format_version is set to 10c and a_format_dev_version is set to s2, the precision parameter can be an integer of the numeric type. Otherwise, only the int type is supported.
- If the GUC parameter a_format_date_timestamp is disabled, the system date and time when the transaction is started is returned.
- maketime(hour, minute, second)
Description: Generates the time (of the time type) based on the input parameters hour, minute, and second. The three input parameters are of the bigint, bigint, and numeric types, respectively.
Return type: time
Example:
1 2 3 4 5 6 7 8 9 10 11
gaussdb=# SELECT maketime(8, 15, 26.53); maketime ------------- 08:15:26.53 (1 row) gaussdb=# SELECT maketime(-888, 15, 26.53); maketime ------------ -838:59:59 (1 row)
This function can be used only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'. In addition, this function has the following behavior:
- The function returns NULL if any of the following conditions is met:
- The value of the input parameter minute is less than 0 or greater than or equal to 60.
- The value of the input parameter second is less than 0 or greater than or equal to 60.
- The value of any parameter is NULL.
- The returned result of the time type contains six decimal places. If the value of second contains more than six decimal places, the value is rounded off.
- The returned value of the time type is in the range [–838:59:59,838:59:59]. If the value is out of the range, the specified boundary value is returned based on the positive and negative values of hour.
- maketime does not support self-nesting.
- The function returns NULL if any of the following conditions is met:
- now()
Description: Returns the system date and time when the current transaction starts. The results returned in the same transaction are the same. Only when sql_compatibility is set to 'MYSQL', b_format_version is set to 5.7, and b_format_dev_version is set to s1, the system date and time when the current SQL query execution starts is returned.
Return type: timestamp with time zone. If sql_compatibility is set to 'MYSQL', b_format_version is set to 5.7, and b_format_dev_version is set to s1, a timestamp without time zone is returned.
Example:
1 2 3 4 5 6 7 8 9 10 11
gaussdb=# SELECT now(); now ------------------------------- 2017-09-01 17:03:42.549426+08 (1 row) -- When the compatibility parameter is enabled and set to 'MYSQL': gaussdb_m=# SELECT now(); timestamp --------------------- 2023-08-21 17:17:42 (1 row)
- now(precision)
Description: Returns the system date and time when the current SQL query execution starts.
Parameter: precision indicates the precision (number of decimal places after the second). The value is of the int type and in the range [0,6]. The default value is 0. If the value is an integer greater than 6, an alarm is generated, and the maximum precision value 6 is used to output the time. If the value is invalid, an error is reported.
Return type: timestamp without time zone
Implementation mode: Obtain the value using a 'now' :: text :: timestamp without time zone expression.
Example:
1 2 3 4 5
gaussdb_m=# SELECT now(3); timestamp ------------------------- 2023-08-21 17:17:48.819 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- timenow()
Description: Returns the system date and time when the current SQL query execution starts.
Return type: abstime
Example:
1 2 3 4 5
gaussdb=# SELECT timenow(); timenow ------------------------ 2020-06-23 20:36:56+08 (1 row)
- dbtimezone
Description: Time zone of the current database.
Return type: text
Example:
1 2 3 4 5
gaussdb=# SELECT dbtimezone; dbtimezone ------------------------ PRC (1 row)
This function is valid only when the value of a_format_version is 10c and that of a_format_dev_version is s2 in an ORA-compatible database.
- numtodsinterval(num, interval_unit)
Description: Converts a number to the interval type. num is a numeric-typed number. interval_unit is a string in the following format: 'DAY' | 'HOUR' | 'MINUTE' | 'SECOND'
You can set the GUC parameter IntervalStyle to ORA to be compatible with the interval output format of the function in the Oracle database.
Return type: interval
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13
gaussdb=# SELECT numtodsinterval(100, 'HOUR'); numtodsinterval ----------------- 100:00:00 (1 row) gaussdb=# SET intervalstyle = oracle; SET gaussdb=# SELECT numtodsinterval(100, 'HOUR'); numtodsinterval ------------------------------- +000000004 04:00:00.000000000 (1 row)
When a_format_version is set to 10c and a_format_dev_version is set to s2 in an ORA-compatible database, an error is reported if interval_unit is set to 'DAY' and num is set to a value greater than 1000000000.
- numtoyminterval(num, interval_unit)
Description: Converts a number to the interval type. num is a number of the numeric type, and interval_unit is a string of the fixed format ('YEAR'|'MONTH').
You can set the GUC parameter IntervalStyle to ORA to be compatible with the interval output format of the function in the Oracle database.
Return type: interval
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13
gaussdb=# SELECT numtoyminterval(100, 'MONTH'); numtoyminterval ----------------- 8 years 4 mons (1 row) gaussdb=# SET intervalstyle = oracle; SET gaussdb=# SELECT numtodsinterval(100, 'MONTH'); numtoyminterval ----------------- 8-4 (1 row)
This function is valid only when the value of a_format_version is 10c and that of a_format_dev_version is s2 in an ORA-compatible database.
- new_time(date, timezone1,timezone2)
Description: Returns the date and time of the time zone specified by timezone2 when the date and time of the time zone specified by timezone1 are date.
Return type: timestamp
Example:
1 2 3 4 5 6 7 8 9 10
gaussdb=# SELECT new_time('1997-10-10','AST','EST'); new_time --------------------- 1997-10-09 23:00:00 (1 row) gaussdb=# SELECT NEW_TIME(TO_TIMESTAMP ('10-Sep-02 14:10:10.123000','DD-Mon-RR HH24:MI:SS.FF'), 'AST', 'PST'); new_time ------------------------- 2002-09-10 10:10:10.123 (1 row)
This function is valid only when the value of a_format_version is 10c and that of a_format_dev_version is s2 in an ORA-compatible database.
- sessiontimezone()
Description: Returns the time zone of the current session. There is no input parameter.
Return type: text
Example:
1 2 3 4 5 6 7 8 9 10
gaussdb=# SELECT SESSIONTIMEZONE; session_time_zone ------------------- PST8PDT (1 row) gaussdb=# SELECT LOWER(SESSIONTIMEZONE); lower ----------- @ 8 hours (1 row)
This function is valid only when the value of a_format_version is 10c and that of a_format_dev_version is s2 in an ORA-compatible database.
When the value of set session time zone is in the GMT+08:00/GMT-08:00 format, the verification fails and an error is reported. This behavior meets the expectation. If the value is 's2' and the "ERROR:invalid value for parameter "TimeZone" :"GMT-08:00"" error is reported when you use JDBC to create a connection, the application where the driver is located sends the same time zone parameter in GMT format to GaussDB. You can use either of the following methods to solve the problem:
Method 1: Adjust the time zone of the OS on the application side and set the local time zone to the region format, for example, Asia/Shanghai.
Method 2: Use the JDBC driver that matches the version on the application side. The JDBC driver changes the GMT time zone to a time zone format that can be identified by the database.
- sys_extract_utc(timestamp| timestamptz)
Description: Extracts Coordinated Universal Time (UTC, also formerly known as Greenwich Mean Time) from a date-time value with a time zone offset or time zone region name. If no time zone is specified, the date and time are associated with the session time zone. The input parameter can be in timestamp or timestamptz format.
Return type: timestamp
Example:
1 2 3 4 5 6 7 8 9 10
gaussdb=# SELECT SYS_EXTRACT_UTC(TIMESTAMP '2000-03-28 11:30:00.00'); sys_extract_utc --------------------- 2000-03-28 03:30:00 (1 row) gaussdb=# SELECT SYS_EXTRACT_UTC(TIMESTAMPTZ '2000-03-28 11:30:00.00 -08:00'); sys_extract_utc --------------------- 2000-03-28 19:30:00 (1 row)
This function is valid only when the value of a_format_version is 10c and that of a_format_dev_version is s2 in an ORA-compatible database.
- tz_offset('time_zone_name' | '(+/-)hh:mi' | SESSIONTIMEZONE | DBTIMEZONE)
Description: Returns the UTC offset of the time zone indicated by the input parameter. The input parameter has the preceding four formats.
Return type: text
Example:
1 2 3 4 5 6 7 8 9 10
gaussdb=# SELECT TZ_OFFSET('US/Pacific'); tz_offset ----------- -08:00 (1 row) gaussdb=# SELECT TZ_OFFSET(sessiontimezone); tz_offset ----------- +08:00 (1 row)
This function is valid only when the value of a_format_version is 10c and that of a_format_dev_version is s2 in an ORA-compatible database.
- pg_sleep(seconds)
Description: Specifies the delay time of the server thread in unit of second. Note that when the database calls this function, the corresponding transaction snapshot is obtained, which is equivalent to a long transaction. If the input parameter time is too long, the database oldestxmin may fail to be executed, affecting the table recycling and query performance.
Return type: void
Example:
1 2 3 4 5
gaussdb=# SELECT pg_sleep(10); pg_sleep ---------- (1 row)
- statement_timestamp()
Description: Current date and time (start of the current statement).
Return type: timestamp with time zone
Example:
1 2 3 4 5
gaussdb=# SELECT statement_timestamp(); statement_timestamp ------------------------------- 2017-09-01 17:04:39.119267+08 (1 row)
- sysdate
Description: Returns the system date and time when the current SQL statement is executed.
Return type: timestamp
Example:
1 2 3 4 5
gaussdb=# SELECT sysdate; sysdate --------------------- 2017-09-01 17:04:49 (1 row)
- sysdate([precision])
Description: Returns the system date and time when a function is executed.
Parameter: Indicates the time precision. The value is of the int type and in the range [0,6]. The default value is 0.
Return type: timestamp without time zone
Example:
1 2 3 4 5 6 7 8 9 10
gaussdb_m=# SELECT sysdate(); sysdate() --------------------- 2023-08-21 17:17:42 (1 row) gaussdb_m=# SELECT sysdate(3); sysdate(3) ------------------------- 2023-08-21 17:17:48.819 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- current_sysdate()
Description: Returns the system date and time when the current SQL query execution starts.
Return type: timestamp
Example:
1 2 3 4 5
gaussdb=# SELECT current_sysdate(); current_sysdate --------------------- 2023-06-20 20:09:02 (1 row)
- timeofday()
Description: Returns the timestamp (such as clock_timestamp, but the return type is text) of the system time when the current function is called.
Return type: text
Example:
1 2 3 4 5
gaussdb=# SELECT timeofday(); timeofday ------------------------------------- Fri Sep 01 17:05:01.167506 2017 CST (1 row)
- transaction_timestamp()
Description: Specifies the system date and time when the current transaction starts.
Return type: timestamp with time zone. If sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1', a timestamp without time zone is returned.
Example:
1 2 3 4 5 6 7 8 9 10 11
gaussdb=# SELECT transaction_timestamp(); transaction_timestamp ------------------------------- 2017-09-01 17:05:13.534454+08 (1 row) -- When the compatibility parameter is enabled and set to 'MYSQL': gaussdb=# SELECT transaction_timestamp(); transaction_timestamp ---------------------------- 2023-09-07 09:32:09.728998 (1 row)
- add_months(d,n)
Description: Returns the time point d plus n months.
d: indicates the value of the timestamp type and the value that can be implicitly converted to the timestamp type.
n: indicates the value of the INTEGER type and the value that can be implicitly converted to the INTEGER type.
Return type: timestamp
Example:
1 2 3 4 5
gaussdb=# SELECT add_months(to_date('2017-5-29', 'yyyy-mm-dd'), 11) FROM sys_dummy; add_months --------------------- 2018-04-29 00:00:00 (1 row)
In the scenario where this function is in an ORA-compatible database, the value of a_format_version is 10c, and that of a_format_dev_version is s1:- If the calculation result is greater than 9999, an error is reported.
- If the value of n is a decimal, the value is truncated instead of being rounded off.
- last_day(d)
Description: Returns the date of the last day of the month that contains date.
Return type: timestamp
Example:
1 2 3 4 5
gaussdb=# SELECT last_day(to_date('2017-01-01', 'YYYY-MM-DD')) AS cal_result; cal_result --------------------- 2017-01-31 00:00:00 (1 row)
When sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1', the last_day function calls the built-in function b_db_last_day. The input parameter type can be TEXT, DATE, DATETIME, or TIME. The return value is of the date type and can be a number in the datetime format.
- months_between(d1, d2)
Description: Calculates the month difference between time points d1 and d2. If both dates are the end of a month or are the same day, an integer is returned. Otherwise, the return value is a decimal and is calculated as 31 days per month.
Return type: numeric
Example:
gaussdb=# SELECT months_between(to_date('2022-10-31', 'yyyy-mm-dd'), to_date('2022-09-30', 'yyyy-mm-dd')); months_between ---------------- 1 (1 row) gaussdb=# SELECT months_between(to_date('2022-10-30', 'yyyy-mm-dd'), to_date('2022-09-30', 'yyyy-mm-dd')); months_between ---------------- 1 (1 row) gaussdb=# SELECT months_between(to_date('2022-10-29', 'yyyy-mm-dd'), to_date('2022-09-30', 'yyyy-mm-dd')); months_between ----------------------- .96774193548387096774 (1 row)
This function is valid only when the value of a_format_version is 10c and that of a_format_dev_version is s1 in an ORA-compatible database.
- next_day(x,y)
Description: Calculates the time of the next week y started from x.
Return type: timestamp
Example:
1 2 3 4 5
gaussdb=# SELECT next_day(timestamp '2017-05-25 00:00:00','Sunday')AS cal_result; cal_result --------------------- 2017-05-28 00:00:00 (1 row)
- tinterval(abstime, abstime)
Description: Creates a time interval with two pieces of absolute time.
Return type: tinterval
Example:
1 2 3 4 5
gaussdb=# CALL tinterval(abstime 'May 10, 1947 23:59:12', abstime 'Mon May 1 00:30:30 1995'); tinterval ----------------------------------------------------- ["1947-05-10 23:59:12+08" "1995-05-01 00:30:30+08"] (1 row)
- tintervalend(tinterval)
Description: Returns the end time of tinterval.
Return type: abstime
Example:
1 2 3 4 5
gaussdb=# SELECT tintervalend('["Sep 4, 1983 23:59:12" "Oct4, 1983 23:59:12"]'); tintervalend ------------------------ 1983-10-04 23:59:12+08 (1 row)
-
Description: Calculates and returns the relative time of tinterval.
Return type: reltime
Example:
1 2 3 4 5
gaussdb=# SELECT tintervalrel('["Sep 4, 1983 23:59:12" "Oct4, 1983 23:59:12"]'); tintervalrel -------------- 1 mon (1 row)
- smalldatetime_ge(smalldatetime, smalldatetime)
Description: Determines whether the first parameter is greater than the second parameter.
Parameter: smalldatetime, smalldatetime
Return type: Boolean
- smalldatetime_cmp(smalldatetime, smalldatetime)
Description: Compares two smalldatetime values to check whether they are the same.
Parameter: smalldatetime, smalldatetime
Return type: integer
- smalldatetime_eq(smalldatetime, smalldatetime)
Description: Compares two smalldatetime values to check whether they are the same.
Parameter: smalldatetime, smalldatetime
Return type: Boolean
- smalldatetime_gt(smalldatetime, smalldatetime)
Description: Determines whether the first parameter is less than the second parameter.
Parameter: smalldatetime, smalldatetime
Return type: Boolean
- smalldatetime_hash(smalldatetime)
Description: Calculates the hash value corresponding to a timestamp.
Parameter: smalldatetime
Return type: integer
- smalldatetime_in(cstring, oid, integer)
Description: Inputs a timestamp.
Parameter: cstring, oid, integer
Return type: smalldatetime
- smalldatetime_larger(smalldatetime, smalldatetime)
Description: Returns a larger timestamp.
Parameter: smalldatetime, smalldatetime
Return type: smalldatetime
- smalldatetime_le(smalldatetime, smalldatetime)
Description: Determines whether the first parameter is less than the second parameter.
Parameter: smalldatetime, smalldatetime
Return type: Boolean
- smalldatetime_lt(smalldatetime, smalldatetime)
Description: Determines whether the first parameter is greater than the second parameter.
Parameter: smalldatetime, smalldatetime
Return type: Boolean
- smalldatetime_ne(smalldatetime, smalldatetime)
Description: Compares two timestamps to check whether they are different.
Parameter: smalldatetime, smalldatetime
Return type: Boolean
- smalldatetime_out(smalldatetime)
Description: Converts a timestamp into the external form.
Parameter: smalldatetime
Return type: cstring
- smalldatetime_send(smalldatetime)
Description: Converts a timestamp to the binary format.
Parameter: smalldatetime
Return type: bytea
- smalldatetime_smaller(smalldatetime, smalldatetime)
Description: Returns a smaller smalldatetime.
Parameter: smalldatetime, smalldatetime
Return type: smalldatetime
- smalldatetime_to_abstime(smalldatetime)
Description: Converts smalldatetime to abstime.
Parameter: smalldatetime
Return type: abstime
- smalldatetime_to_time(smalldatetime)
Description: Converts smalldatetime to time.
Parameter: smalldatetime
Return type: time without time zone
- smalldatetime_to_timestamp(smalldatetime)
Description: Converts smalldatetime to timestamp.
Parameter: smalldatetime
Return type: timestamp without time zone
- smalldatetime_to_timestamptz(smalldatetime)
Description: Converts smalldatetime to timestamptz.
Parameter: smalldatetime
Return type: timestamp with time zone
- smalldatetime_to_varchar2(smalldatetime)
Description: Converts smalldatetime to varchar2.
Parameter: smalldatetime
Return type: character varying
There are multiple methods for obtaining the current time. Select an appropriate API based on the actual service scenario.
- The following APIs return values based on the start time of the current transaction:
CURRENT_DATE CURRENT_TIME CURRENT_TIME(precision) CURRENT_TIMESTAMP(precision) LOCALTIME LOCALTIMESTAMP LOCALTIME(precision) LOCALTIMESTAMP(precision) transaction_timestamp() now()
The values transferred by CURRENT_TIME and CURRENT_TIMESTAMP(precision) contain time zone information. The values transferred by LOCALTIME and LOCALTIMESTAMP do not contain time zone information. CURRENT_TIME, LOCALTIME, and LOCALTIMESTAMP can specify a precision parameter, which rounds the seconds field of the result to the decimal place. If there is no precision parameter, the result is given the full precision that can be obtained.
Because these functions all return results by the start time of the current transaction, their values do not change throughout the transaction. This can be considered as a feature with the purpose to allow a transaction to have a consistent concept at the "current" time, so that multiple modifications in the same transaction can maintain the same timestamp. transaction_timestamp() is equivalent to CURRENT_TIMESTAMP(precision), indicating the start time of the transaction where the current statement is located. now() is equivalent to transaction_timestamp().
When a transaction starts, sql_compatibility is set to 'ORA', and a_format_date_timestamp is set to on, the results returned by the CURRENT_DATE, CURRENT_TIMESTAMP(precision), LOCALTIMESTAMP and LOCALTIMESTAMP (precision) functions are the timestamp when the current SQL statement is started. If the GUC parameter a_format_date_timestamp is disabled, the returned result is the transaction start date or date and time.
When sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1', CURDATE, CURRENT_DATE, CURTIME, CURRENT_TIME, CURRENT_TIMESTAMP, NOW, LOCALTIME, LOCALTIMESTAMP, UTC_TIME and UTC_TIMESTAMP all return the start time of SQL statement execution, and SYSDATE returns the start time of function calling. transaction_timestamp() still indicates the transaction start time. The return value type is changed to timestamp without time zone.
- The following APIs return the start time of the current statement:
statement_timestamp()
statement_timestamp() returns the start time of the current statement (more accurately, the time when the last instruction is received from the client). The return values of statement_timestamp() and transaction_timestamp() are the same during the execution of the first instruction of a transaction, but may be different in subsequent instructions.
- The following APIs return the actual current time when the function is called:
clock_timestamp() timeofday()
clock_timestamp() returns the actual current time, and its value changes even in the same SQL command. Similar to clock_timestamp(), timeofday() also returns the actual current time. However, the result of timeofday() is a formatted text string instead of a timestamp with time zone information.
- The following APIs return values based on the start time of the current transaction:
- convert_tz(dt, from_tz, to_tz)
Description: Converts the date and time value dt from the from_tz time zone to the to_tz time zone.
Parameters: For details about the parameters, see Table 2.
Table 2 Parameters Parameter
Type
Description
Value Range
dt
datetime, date, text, and numeric
Time and date value.
[0000-01-01 00:00:00.000000,9999-12-31 23:59:59.999999].
from_tz/to_tz
A character string in the format of ±hh:mm
Offset compared with the UTC time, for example, '+08:00'.
[–15:59,15:00]
Named time zone
For example, 'MET' and 'UTC'.
For details, see the PG_TIMEZONE_NAMES system view.
Return type: datetime
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
gaussdb=# SET b_format_dev_version = 's1'; SET gaussdb=# SET b_format_version = '5.7'; SET gaussdb=# SELECT convert_tz(cast('2023-01-01 10:10:10' as datetime), '+00:00', '+01:00'); convert_tz --------------------- 2023-01-01 11:10:10 (1 row) gaussdb=# SELECT convert_tz(cast('2023-01-01' as date), '+00:00', '+01:00'); convert_tz --------------------- 2023-01-01 01:00:00 (1 row) gaussdb=# SELECT convert_tz('2023-01-01 10:10:10', '+00:00', '+01:00'); convert_tz --------------------- 2023-01-01 11:10:10 (1 row) gaussdb=# SELECT convert_tz('2023-01-01', '+00:00', '+01:00'); convert_tz --------------------- 2023-01-01 01:00:00 (1 row) gaussdb=# SELECT convert_tz(20230101101010, '+00:00', '+01:00'); convert_tz --------------------- 2023-01-01 11:10:10 (1 row) gaussdb=# SELECT convert_tz(20230101, '+00:00', '+01:00'); convert_tz --------------------- 2023-01-01 01:00:00 (1 row) gaussdb=# SELECT convert_tz('2023-01-01 10:10:10', 'UTC', 'PRC'); convert_tz --------------------- 2023-01-01 18:10:10 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- sec_to_time(seconds)
Description: Converts the number of seconds into a time of the time type.
Parameters: For details about the parameters, see Table 3.
Table 3 Parameters Parameter
Type
Description
Value Range
seconds
numeric and text
Number of seconds
[–3020399,+3020399], corresponding to the range of the time type [–838:59:59,838:59:59]. An out-of-bounds input will be truncated to the corresponding boundary value.
Return type: time without time zone
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14
gaussdb=# SET b_format_dev_version = 's1'; SET gaussdb=# SET b_format_version = '5.7'; SET gaussdb=# SELECT sec_to_time(2000); sec_to_time ------------- 00:33:20 (1 row) gaussdb=# SELECT sec_to_time('-2000'); sec_to_time ------------- -00:33:20 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
-
adddate(date, INTERVAL val unit)
Description: Returns a new date by adding a certain interval to a date.
Parameters: For details about the parameters, see Table 4.
Table 4 Parameters Parameter
Type
Description
Value Range
date
Expression of the time type, text, datetime, date, or time
Date to be added with an interval.
See the value ranges of the corresponding types.
val
Integer, floating-point number, or character string
Interval to be added.
See the value ranges of the corresponding types.
unit
Keyword
Unit of the interval.
YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, and MICROSECOND. For details, see Time Interval Expressions.
Return type: TEXT, DATE, DATETIME, or TIME WITHOUT TIMEZONE
Example:
1 2 3 4 5
gaussdb=# SELECT ADDDATE('2018-05-01', INTERVAL 1 DAY); adddate ------------ 2018-05-02 (1 row)
- This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- In the prepare statement, the second parameter of adddate is an interval expression. When parameter $1 is used to completely replace adddate, the result is unexpected, for example, prepare p1 as select adddate('2023-01-01 10:00:00', $1); execute p1(interval 13 hour). The unexpected result returned in this test case is '2023-01-01 10:00:00'.
- adddate(expr, days)
Description: Returns a new date by adding a certain number of days to a date.
Parameters:
- expr: specifies the start date and time. The value type can be expression of the time type, TEXT, DATE, DATETIME, or TIME.
- days: specifies the number of days to be added. The value is of the int type.
Return type: TEXT, DATE, DATETIME, or TIME WITHOUT TIMEZONE
Example:
gaussdb=# SELECT ADDDATE('2018-05-01', 1); adddate ------------ 2018-05-02 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- 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 type: date
Example:
1 2 3 4 5
gaussdb=# SELECT curdate(); curdate ------------ 2023-08-10 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- curtime([precision])
Description: Returns the system time when the SQL query call starts.
Parameter: precision indicates the precision (number of decimal places after the second). The value is of the int type and in the range [0,6]. The default value is 0. If the value can be converted into an integer within the range, the time value of the corresponding precision can be output. If the value is invalid, an error is reported.
Return type: time without time zone
Implementation method: Register the system function curtime.
Example:
1 2 3 4 5
gaussdb=# SELECT curtime(3); curtime -------------- 16:59:57.203 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- date_add(date,INTERVAL val unit)
Description: Adds a period of time to a specified date and returns the calculation result.
Parameters: For details about the parameters, see Table 5.
Table 5 Parameters Parameter
Type
Description
Value Range
date
Expression of the time type, text, datetime, date, or time
Date to be added with an interval.
See the value ranges of the corresponding types.
val
Integer, floating-point number, or character string
Interval to be added.
See the value ranges of the corresponding types.
unit
Keyword
Unit of the interval.
YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, and MICROSECOND. For details, see Time Interval Expressions.
Return value type: a single return value. For details, see Table 6.
Table 6 Return value types Return Type
Description
TEXT
The date input parameter is of the text type.
DATE
The date input parameter is of the date type, and the value of the unit input parameter is greater than or equal to day (for example, week or month).
TIME WITHOUT TIMEZONE
The date input parameter is of the time type.
DATETIME
The date input parameter is of the datetime type, or the date input parameter and the value of the unit input parameter is less than day (for example, hour or second).
Example:
gaussdb=# SELECT DATE_ADD('2018-05-01', INTERVAL 1 DAY); date_add ------------ 2018-05-02 (1 row)
- This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- In the prepare statement, the second parameter of date_add is an interval expression and cannot be replaced by $1, for example, prepare p1 as select date_add('2023-01-01 10:00:00', $1).
- date_add(expr, days)
Description: Returns a new date by adding a certain number of days to a date.
Parameters:
- expr: specifies the start date and time. The value type can be expression of the time type, TEXT, DATE, DATETIME, or TIME.
- days: specifies the number of days to be added. The value is of the int type.
Return type: TEXT, DATE, DATETIME, or TIME WITHOUT TIMEZONE
Example:
gaussdb=# SELECT DATE_ADD('2018-05-01', 1); date_add ------------ 2018-05-02 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- date_format(date, format)
Description: Outputs the date and time in the specified format.
Parameters: For details about the parameters, see Table 7.
Table 7 Parameters 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
A formatted string.
For details, see Table 8.
Table 8 lists the format parameters.Table 8 Values and meanings of format Value
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
Hours (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
Return type: text
Example:
gaussdb=# SELECT date_format('2023-10-11 12:13:14.151617','%b %c %M %m'); date_format ------------------- Oct 10 October 10 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- date_sub(date, INTERVAL val unit)
Description: Returns a new date by subtracting a certain interval from a date.
Parameters: For details about the parameters, see Table 9.
Table 9 Parameters Parameter
Type
Description
Value Range
date
Expression of the time type, text, datetime, date, or time
Date to be added with an interval.
See the value ranges of the corresponding types.
val
Integer, floating-point number, or character string
Interval to be added.
See the value ranges of the corresponding types.
unit
Keyword
Unit of the interval.
YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, and MICROSECOND. For details, see Time Interval Expressions.
Return type: TEXT, DATE, DATETIME, or TIME WITHOUT TIMEZONE
Example:
gaussdb=# SELECT DATE_SUB('2018-05-01', INTERVAL 1 YEAR); date_sub ------------ 2017-05-01 (1 row)
- This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- In the prepare statement, the second parameter of date_sub is an interval expression and cannot be replaced by $1, for example, prepare p1 as select date_sub('2023-01-01 10:00:00', $1).
- date_sub(expr, days)
Description: Specifies the start date and time and the number of days to be subtracted from the start date and time, and returns the subtraction result.
Parameters:
- expr: specifies the start date and time. The value type can be expression of the time type, TEXT, DATE, DATETIME, or TIME.
- days: specifies the number of days to be subtracted. The value is of the int type.
Return type: TEXT, DATE, DATETIME, or TIME WITHOUT TIMEZONE
Example:
gaussdb=# SELECT DATE_SUB('2023-1-1', 20); date_sub ------------ 2022-12-12 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- datediff(expr1, expr2)
Description: Returns the number of days between two time expressions.
Parameter: Expression of the time type, text, datetime, date, or time
Return type: int
Example:
gaussdb=# SELECT datediff('2021-11-12','2021-11-13'); datediff ---------- -1 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- day()/dayofmonth()
Description: Extracts the number of days in a date and time expression and returns the result. dayofmonth() is the alias of day() and has the same function.
Parameter: The input parameter is the date and time to be extracted, which can be an expression of the time type, or of the text, datetime, date, or time type.
Return type: int
Example:
gaussdb=# SELECT day('2023-01-02'); day ----- 2 (1 row) gaussdb=# SELECT dayofmonth('23-05-22'); dayofmonth ------------ 22 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- dayname()
Description: Returns the name of a day in a week.
Parameter: The input parameter is a time expression, TEXT, DATETIME, DATE, or TIME.
Return type: text
Example:
gaussdb=# SELECT dayname('2023-10-11'); dayname ----------- Wednesday (1 row)
- This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- The language used by the return value is specified by the GUC parameter lc_time_names.
- dayofweek()
Description: Returns the working day index of a date (1 = Sunday, 2 = Monday, ..., 7 = Saturday).
Parameter: Expression of the time type, text, datetime, date, or time
Return type: bigint
Example:
gaussdb=# SELECT dayofweek('2023-04-16'); dayofweek ----------- 1 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- dayofyear()
Description: Returns the number of a day in the year.
Parameter: The input parameter is a time expression, TEXT, DATETIME, DATE, or TIME.
Return type: int; value range: 1 to 366.
Example:
gaussdb=# SELECT dayofyear('2000-12-31'); dayofyear ----------- 366 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- extract(unit FROM date)
Description: Extracts part of the time. This function can be used in other modes, but the behavior varies greatly. When sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1', the function is described as follows:
Parameters:
- unit: text type. For details about the value types, see Table 10.
- date: expression of the time type, such as date, time, datetime, and text.
Return type: bigint
Table 10 Values and meanings of unit Value of UNIT
Description
MICROSECOND
Microsecond
SECOND
Second
MINUTE
Minute
HOUR
Hour
DAY
Day
WEEK
Week
MONTH
Month
QUARTER
Quarter
YEAR
Year
SECOND_MICROSECOND
Concatenated value of second and microsecond
MINUTE_MICROSECOND
Concatenated value of minute, second, and microsecond
MINUTE_SECOND
Concatenated value of minute and second
HOUR_MICROSECOND
Concatenated value of hour, minute, second, and microsecond
HOUR_SECOND
Concatenated value of hour, minute, and second
HOUR_MINUTE
Concatenated value of hour and minute
DAY_MICROSECOND
Concatenated value of day, hour, minute, second, and microsecond
DAY_SECOND
Concatenated value of day, hour, minute, and second
DAY_MINUTE
Concatenated value of day and minute
DAY_HOUR
Concatenated value of day and hour
YEAR_MONTH
Concatenated value of year and month
EPOCH
Total number of seconds or interval since 1970-01-01 00:00:00-00 UTC
Example:
gaussdb=# SELECT extract(YEAR FROM '2023-10-11'); extract --------- 2023 (1 row) gaussdb=# SELECT extract(QUARTER FROM '2023-10-11'); extract --------- 4 (1 row) gaussdb=# SELECT extract(MONTH FROM '2023-10-11'); extract --------- 10 (1 row) gaussdb=# SELECT extract(WEEK FROM '2023-10-11'); extract --------- 41 (1 row) gaussdb=# SELECT extract(DAY FROM '2023-10-11'); extract --------- 11 (1 row) gaussdb=# SELECT extract(HOUR FROM '2023-10-11 12:13:14'); extract --------- 12 (1 row)
When sql_compatibility is set to 'MYSQL', b_format_version is set to 5.7, and b_format_dev_version is set to s1, the called function is registered as b_extract. In other cases, the actually registered function is date_part. You can use commands such as \df b_extract to query the detailed input parameter and return value of a function.
The GUC parameter default_week_format is used to process special week issues. The default value is 0. For details, see Table 11.
Table 11 default_week_format default_week_format
First Day of a Week
Range
Which Week is the First Week
0
Sunday
0–53
Week containing Sunday in this year
1
Monday
0–53
Week containing four or more days in this year
2
Sunday
1–53
Week containing Sunday in this year
3
Monday
1–53
Week containing four or more days in this year
4
Sunday
0–53
Week containing four or more days in this year
5
Monday
0–53
Week containing Monday in this year
6
Sunday
1–53
Week containing four or more days in this year
7
Monday
1–53
Week containing Monday in this year
- from_days(days)
Description: Returns the corresponding date value given a number of days.
Parameter: The input parameter is a time expression, TEXT, DATETIME, DATE, or TIME.
Return type: date
Example:
gaussdb=# SELECT from_days(36524);--0099-12-31 from_days ------------ 0099-12-31 (1 row)
- This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- Null characters and zeros are processed as 0. For input parameters that cannot be correctly converted to bigint, an error is reported.
- If the input parameter is less than 366, the return date is 0000-00-00.
- from_unixtime(unix_timestamp[,format])
Description: Converts a Unix timestamp to the date and time 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, which is of the numeric type.
- format: time format. The value is of the text type.
Return type: text/datetime
Example:
gaussdb=# SELECT from_unixtime(1111885200); from_unixtime --------------------- 2005-03-27 09:00:00 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- get_format({DATE | TIME | DATETIME | TIMESTAMP}, {'EUR' | 'USA' | 'JIS' | 'ISO' | 'INTERNAL'})
Description: Converts a date, time, or datetime into a string in a specified time format, that is, the year, month, day, hour, minute, and second formats and sorting standards of different regions.
Parameters:
- DATE|TIME|DATETIME|TIMESTAMP: time type, which are keywords.
- 'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL': five time formats, which are of the text type.
Return type: text
Example:
gaussdb=# SELECT get_format(date, 'eur'); get_format ------------ %d.%m.%y (1 row) gaussdb=# SELECT get_format(date, 'usa'); get_format ------------ %m.%d.%y (1 row)
- This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- In the prepare statement, the first parameter of get_format is a keyword and cannot be replaced by $1, for example, prepare p1 as select get_format($1, 'usa').
- hour()
Description: Returns the hour part of a time after you enter a time type.
Parameter: Expression of the time type, text, datetime, date, or time
Return type: bigint
Example:
gaussdb=# SELECT HOUR('10:10:10.1'); hour ------ 10 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- makedate(year,dayofyear)
Description: Returns a date based on the given year and day.
Parameters:
- year: bigint.
- 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 type: date
Example:
gaussdb=# SELECT makedate(2000, 60); makedate ------------ 2000-02-29 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- microsecond()
Description: Returns the microsecond part of a time after you enter a time type.
Parameter: Expression of the time type, text, datetime, date, or time
Return type: bigint
Example:
gaussdb=# SELECT MICROSECOND('2023-5-5 10:10:10.24485'); microsecond ------------- 244850 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- minute()
Description: Returns the minute part of a time after you enter a time type.
Parameter: Expression of the time type, text, datetime, date, or time
Return type: bigint
Example:
gaussdb=# SELECT MINUTE(time'10:10:10'); minute -------- 10 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- month()
Description: Returns the month starting from a past date.
Parameter: The input parameter is the date and time to be extracted, which can be an expression of the time type, or of the TEXT, DATETIME, DATE, or TIME type.
Return type: int
Example:
gaussdb=# SELECT month('2021-11-30'); month ------- 11 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- monthname()
Description: Returns the full month name of a date.
Parameter: The input parameter is a time expression, TEXT, DATETIME, DATE, or TIME.
Return type: text
Example:
gaussdb=# SELECT monthname('2023-02-28'); monthname ----------- February (1 row)
- This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- The language used by the return value is controlled by the value of the lc_time_names system variable.
- period_add(period, month_number)
Description: Adds a specified number of months to a specified time segment and returns the result as a time segment.
Parameters:
- period: bigint, which is a date in the format of YYYYMM or YYMM.
- month_number: bigint, indicating the number of months to be added. The value can be a negative number.
Return type: bigint, which is a date in YYYYMM format.
Example:
gaussdb=# SELECT period_add(202205, -12); period_add ------------ 202105 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- period_diff(p1,p2)
Description: Calculates the month difference between two time points.
Parameters: P1 and P2 are periods in YYMM or YYYYMM format and are of the bigint type.
Return type: bigint (month difference)
Example:
gaussdb=# SELECT period_diff('202101', '202102'); period_diff ------------- -1 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- second()
Description: Returns the second part of a time after you enter a time type.
Parameter: Expression of the time type, text, datetime, date, or time
Return type: bigint
Example:
gaussdb=# SELECT SECOND('2023-5-5 10:10:10'); second -------- 10 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- quarter()
Description: Returns the quarter part of a date after you enter a date type.
Parameter: The input parameter is the date and time to be extracted, which can be an expression of the time type, or of the TEXT, DATETIME, DATE, or TIME type.
Return type: bigint
Example:
gaussdb=# SELECT QUARTER('2012-1-1'); quarter --------- 1 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- str_to_date(str, format)
Description: Converts a specified character string to a date/time based on the specified date format.
Parameters:
- str: text type, which is the character string to be formatted into a date. format: text type, indicating the target format.
- Table 12 lists the format parameters.
Table 12 Values and meanings of format Value
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
Hours (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
Return type: text
Example:
gaussdb=# SELECT str_to_date('May 1, 2013','%M %d,%Y');--2013-05-01 str_to_date ------------- 2013-05-01 (1 row)
- This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- Only the time in YYYY-MM-DD format can be returned.
- If the time contains 0 year, 0 month, and 0 day or contains only hour, minute, and second, an alarm is generated and NULL is returned.
- subdate(expr, days)
Description: Specifies the start date and time and the number of days to be subtracted from the start date and time, and returns the subtraction result.
Parameters:
- expr: specifies the start date and time. The value type can be expression of the time type, TEXT, DATE, DATETIME, or TIME.
- days: specifies the number of days to be subtracted. The value is of the int type.
Return type: TEXT, DATE, DATETIME, or TIME WITHOUT TIMEZONE
Example:
gaussdb=# SELECT SUBDATE('2023-1-1', 20); subdate ------------ 2022-12-12 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- subdate(expr,INTERVAL val unit)
Description: Specifies the start date and time and the interval to be subtracted from the start date and time, and returns the subtraction result.
Parameters: For details about the parameters, see Table 13.
Table 13 Parameters Parameter
Type
Description
Value Range
expr
Expression of the time type, text, datetime, date, or time
Specifies the start date and time.
See the value ranges of the corresponding types.
val
Integer, floating-point number, or character string
Specifies the interval to be subtracted.
See the value ranges of the corresponding types.
unit
Keyword
YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, and MICROSECOND. For details, see Time Interval Expressions.
Return type: TEXT, DATE, DATETIME, or TIME WITHOUT TIMEZONE
Example:
gaussdb=# SELECT SUBDATE('2018-05-01', INTERVAL 1 YEAR); subdate ------------ 2017-05-01 (1 row)
- This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- In the prepare statement, the second parameter of subdate is an interval expression. When parameter $1 is used to completely replace subdate, the result is unexpected, for example, prepare p1 as select subdate('2023-01-01 10:00:00', $1); execute p1(interval 13 hour). The unexpected result returned in this test case is '2023-01-01 10:00:00'.
- subtime(expr1,expr2)
Description: Returns the difference between expr1 and expr2.
Parameters:
- expr1 is an expression of the TIMESTAMP WITHOUT TIME ZONE or TIME type, and expr2 is a time expression.
- The return value type is related to the expr1 type. If the two input parameters are of the TEXT type, the return value type is TEXT. If the two input parameters are parsed as TIMESTAMP WITHOUT TIME ZONE, the return value type is TIMESTAMP WITHOUT TIME ZONE. If the two input parameters are parsed as TIME, the return value type is TIME.
Return type: TEXT, TIMESTAMP WITHOUT TIME ZONE, or TIME
Example:
gaussdb=# SELECT subtime('2000-03-01 20:59:59', '22:58'); subtime --------------------- 2000-02-29 22:01:59 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- addtime(expr1,expr2)
Description: Returns the sum of expr1 and expr2. The format of the return value is the same as that of expr1.
Parameters:
- expr1 is an expression of the timestamp without time zone or time type, and expr2 is a time expression.
- The return value type is related to the type of expr1. If it is parsed as timestamp without time zone, the return value type is timestamp without time zone. If it is parsed as time, the return value type is time.
Return type: text, timestamp without time zone, or time.
Example:
gaussdb=# SELECT addtime('2000-03-01 20:59:59', '00:00:01'); addtime --------------------- 2000-03-01 21:00:00 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- time_format(time, format)
Description: Formats the time input parameter based on the format specified by format.
Parameters:
- The value type of time is expression of the time type, text, datetime, date, or time
- format is of the text type. Table 14 lists the supported formats.
Table 14 Values and meanings of format Value
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 type: text
Example:
gaussdb=# 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)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- time_to_sec()
Description: Converts the input parameter of the time type to the number of seconds.
Parameter: The input parameter is a time expression, TEXT, DATETIME, DATE, or TIME. The time expression is parsed as TIME.
Return type: int
Example:
gaussdb=# SELECT time_to_sec('00:00:01'); time_to_sec ------------- 1 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- timediff()
Description: Calculates the difference between two time points and returns an interval.
Parameter: There are two parameters, which are of the time expression, text, datetime, date, or time type.
Return type: TIME WITHOUT TIMEZONE
Example:
gaussdb=# SELECT timediff(date'2022-12-30',20221229); timediff ---------- 24:00:00 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- timestampadd(unit,interval,datetime_expr)
Description: Returns a new timestamp calculated by adding multiple intervals of a unit to datetime_expr.
Parameters: For details about the parameters, see Table 15.
Table 15 Parameters Parameter
Type
Description
Value Range
unit
Keyword
Unit of the interval.
YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MICROSECOND, Y, MM (month), D, H, M (minute), S, US, YRS, QTR, MON, HRS, MIN, YEARS, WEEKS, or HOURS.
interval
numeric
Interval.
See the value ranges of the corresponding types.
datetime_expr
Expression of the time type, text, datetime, date, or time
Date and time to be changed. If the value is of the text type, the return value type is text. If the value is of the time type, the return value type is time. In other cases, the return value type is datetime.
See the value ranges of the corresponding types.
Return type: DATETIME, TEXT, TIME WITHOUT TIMEZONE
Example:
gaussdb=# SELECT TIMESTAMPADD(DAY,-2,'2022-07-27'); timestampadd -------------- 2022-07-25 (1 row)
- This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- The actually registered function of timestampadd is timestamp_add. You can run commands such as \df timestamp_add to query the detailed input parameter and return value of the function.
- In the prepare statement, the first parameter of timestampadd is a keyword and cannot be replaced by $1, for example, prepare p1 as select timestampadd($1, -2, '2023-01-01');.
- to_days()
Description: Returns the number of days from year 0 of a specified date.
Parameter: The input parameter is a time expression, TEXT, DATETIME, DATE, or TIME. The time expression is parsed as DATE.
Return type: bigint
Example:
gaussdb=# SELECT to_days('2000-1-1'); to_days --------- 730485 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- to_seconds()
Description: Returns the number of seconds since the year 0 A.D.
Parameter: The input parameter is a time expression, TEXT, DATETIME, DATE, or TIME. The time expression is parsed as datetime.
Return type: bigint
Example:
gaussdb=# SELECT TO_SECONDS('2009-11-29 13:43:32'); to_seconds ------------- 63426721412 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- unix_timestamp([date])
Description: Returns a Unix timestamp representing the number of seconds since "1970-01-01 08:00" UTC. If there is no input parameter, the default value is the datetime timestamp when the function is called.
Parameter: Expression of the time type, text, datetime, date, or time
Return type: numeric
Example:
gaussdb=# SELECT UNIX_TIMESTAMP('2022-12-22'); unix_timestamp ---------------- 1671638400 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7' and b_format_dev_version is set to 's1'.
- utc_date()
Description: Returns the current UTC date of function execution as a value in YYYY-MM-DD format.
Return type: date
Example:
gaussdb=# SELECT utc_date(); utc_date ------------ 2023-08-10 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7' and b_format_dev_version is set to 's1'.
- utc_time()
Description: Returns the current UTC time of function execution as a value in HH:MM:SS format.
Parameter: Indicates the time precision. The value is of the int type and in the range [0,6]. The default value is 0.
Return type: time without time zone
Example:
gaussdb=# SELECT utc_time(); utc_time ---------- 11:47:53 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7' and b_format_dev_version is set to 's1'.
- utc_timestamp()
Description: Returns the current UTC timestamp of function execution as a value in YYYY-MM-DD HH:MM:SS format.
Parameter: Indicates the time precision. The value is of the int type and in the range [0,6]. The default value is 0.
Return type: timestamp without time zone
Example:
gaussdb=# SELECT utc_timestamp(); utc_timestamp --------------------- 2023-08-21 11:51:19 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7' and b_format_dev_version is set to 's1'.
- week(date[, mode])
Description: Returns the number of weeks of a date.
Parameters:
- date: specifies the date and time, which is of the time expression, text, datetime, date, or time type.
- Table 16 describes the optional parameter mode. The default value is 0.
Table 16 Description of mode mode
First Day of a Week
Range
Which Week is the First Week
0
Sunday
0–53
Week containing Sunday in this year
1
Monday
0–53
Week containing four or more days in this year
2
Sunday
1–53
Week containing Sunday in this year
3
Monday
1–53
Week containing four or more days in this year
4
Sunday
0–53
Week containing four or more days in this year
5
Monday
0–53
Week containing Monday in this year
6
Sunday
1–53
Week containing four or more days in this year
7
Monday
1–53
Week containing Monday in this year
Return type: bigint
Example:
gaussdb=# SELECT week(date'2000-01-01', 1); week ------ 0 (1 row) gaussdb=# SELECT week('2000-01-01', 2); week ------ 52 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7' and b_format_dev_version is set to 's1'.
- weekday()
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: Expression of the time type, text, datetime, date, or time
Return type: bigint
Example:
gaussdb=# SELECT weekday('1970-01-01 12:00:00'); weekday --------- 3 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
- weekofyear(date)
Description: Returns the calendar week of the date and time. The value ranges from 1 to 53. It is equivalent to week(date, 3).
Parameters:
- date: specifies the date and time, which is of the time expression, text, datetime, date, or time type.
- This function is equivalent to week(date, 3). For details, see week(date[, mode]).
Return type: bigint
Example:
gaussdb=# SELECT weekofyear('1970-05-22'); weekofyear ------------ 21 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7' and b_format_dev_version is set to 's1'.
- year()
Description: Extracts the year part of the date and time and returns the result.
Parameter: The input parameter is the date and time to be extracted, which can be an expression of the time type, or of the text, datetime, date, or time type.
Return type: int
Example:
gaussdb=# SELECT year('23-05-22'); year ------ 2023 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7' and b_format_dev_version is set to 's1'.
- yearweek(date[, mode])
Description: Returns the year and week of a date.
Parameters:
- date: specifies the date and time.
- The value of date can be DATE, DATETIME, TIMESTAMP, TIME, TEXT or NUMERIC.
- Table 17 describes the optional parameter mode. The default value is 0.
Table 17 Description of mode mode
First Day of a Week
Range
Which Week is the First Week
0
Sunday
0–53
Week containing Sunday in this year
1
Monday
0–53
Week containing four or more days in this year
2
Sunday
1–53
Week containing Sunday in this year
3
Monday
1–53
Week containing four or more days in this year
4
Sunday
0–53
Week containing four or more days in this year
5
Monday
0–53
Week containing Monday in this year
6
Sunday
1–53
Week containing four or more days in this year
7
Monday
1–53
Week containing Monday in this year
Return type: bigint
Example:
gaussdb=# SELECT yearweek(datetime'2000-01-01', 3); yearweek ---------- 199952 (1 row)
This function is valid only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7' and b_format_dev_version is set to 's1'.
Table 18 shows the templates for truncating date/time values.
|
Item |
Format |
Description |
|---|---|---|
|
Microsecond |
MICROSECON |
Truncates date/time values, accurate to the microsecond (000000–999999). |
|
US |
||
|
USEC |
||
|
USECOND |
||
|
Millisecond |
MILLISECON |
Truncates date/time values, accurate to the millisecond (000–999). |
|
MS |
||
|
MSEC |
||
|
MSECOND |
||
|
Second |
S |
Truncates date/time values, accurate to the second (00–59). |
|
SEC |
||
|
SECOND |
||
|
Minute |
M |
Truncates date/time values, accurate to the minute (00–59). |
|
MI |
||
|
MIN |
||
|
MINUTE |
||
|
Hour |
H |
Truncates date/time values, accurate to the hour (00–23). |
|
HH |
||
|
HOUR |
||
|
HR |
||
|
Day |
D |
Truncates date/time values, accurate to the day (01-01 to 12–31) |
|
DAY |
||
|
DD |
||
|
DDD |
||
|
J |
||
|
Week |
W |
Truncates date/time values, accurate to the week (the first day of the current week). |
|
WEEK |
||
|
Month |
MM |
Truncates date/time values, accurate to the month (the first day of the current month). |
|
MON |
||
|
MONTH |
||
|
Quarter |
Q |
Truncates date/time values, accurate to the quarter (the first day of the current quarter). |
|
QTR |
||
|
QUARTER |
||
|
Year |
Y |
Truncates date/time values, accurate to the year (the first day of the current year). |
|
YEAR |
||
|
YR |
||
|
YYYY |
||
|
Decade |
DEC |
Truncates date/time values, accurate to the decade (the first day of the current decade). |
|
DECADE |
||
|
Century |
C |
Truncates date/time values, accurate to the century (the first day of the current century). |
|
CC |
||
|
CENT |
||
|
CENTURY |
||
|
Millennium |
MIL |
Truncates date/time values, accurate to the millennium (the first day of the current millennium). |
|
MILLENNIA |
||
|
MILLENNIUM |
|
Item |
Format |
Description |
|---|---|---|
|
Minute |
M |
Truncated or rounded off, accurate to minute (00–59). |
|
MI |
||
|
MIN |
||
|
MINUTE |
||
|
Hour |
H |
Truncated or rounded off, accurate to hour (00–23). |
|
HH |
||
|
HOUR |
||
|
HR |
||
|
HH12 |
||
|
HH24 |
||
|
Day |
DD |
Truncated or rounded off, accurate to day (01-01 to 12-31). |
|
DDD |
||
|
J |
||
|
ISO week |
IW |
Truncated or rounded off, accurate to week (the first day of the week is Monday). |
|
Week |
DAY |
Truncated or rounded off, accurate to week (the first day of the week is Sunday). |
|
DY |
||
|
D |
||
|
Week of the month |
W |
Truncated or rounded off, accurate to week (the first day of the week is the first day of the month). |
|
Week of the year |
WW |
Truncated or rounded off, accurate to week (the first day of the week is the first day of the year). |
|
Month |
MM |
Truncated or rounded off, accurate to month (the first day of the month). |
|
MON |
||
|
MONTH |
||
|
RM |
||
|
Quarter |
Q |
Truncated or rounded off, accurate to quarter (the first day of the quarter). |
|
QTR |
||
|
QUARTER |
||
|
Year |
Y |
Truncated or rounded off, accurate to year (the first day of the current year). |
|
YEAR |
||
|
YR |
||
|
YYYY |
||
|
SYYYY |
||
|
YYY |
||
|
YY |
||
|
SYEAR |
||
|
Decade |
DEC |
Truncated or rounded off, accurate to decade (the first day of the current decade). |
|
DECADE |
||
|
Century |
C |
Truncated or rounded off, accurate to the century (the first day of the century). |
|
CC |
||
|
CENT |
||
|
CENTURY |
||
|
SCC |
||
|
Millennium |
MIL |
Truncated or rounded off, accurate to millennium (the first day of the millennium). |
|
MILLENNIA |
||
|
MILLENNIUM |
The behaviors of Table 19 are valid only when the value of a_format_version is 10c and that of a_format_dev_version is s1 in an ORA-compatible database.
- timestamp_diff(text, timestamp, timestamp)
Description: Calculates the difference between two timestamps and truncates the difference to the precision specified by text.
Return type: bigint
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45
gaussdb=# SELECT timestamp_diff('year','2018-01-01','2020-04-01'); timestamp_diff ---------------- 2 (1 row) gaussdb=# SELECT timestamp_diff('month','2018-01-01','2020-04-01'); timestamp_diff ---------------- 27 (1 row) gaussdb=# SELECT timestamp_diff('quarter','2018-01-01','2020-04-01'); timestamp_diff ---------------- 9 (1 row) gaussdb=# SELECT timestamp_diff('week','2018-01-01','2020-04-01'); timestamp_diff ---------------- 117 (1 row) gaussdb=# SELECT timestamp_diff('day','2018-01-01','2020-04-01'); timestamp_diff ---------------- 821 (1 row) gaussdb=# SELECT timestamp_diff('hour','2018-01-01 10:10:10','2018-01-01 12:12:12'); timestamp_diff ---------------- 2 (1 row) gaussdb=# SELECT timestamp_diff('minute','2018-01-01 10:10:10','2018-01-01 12:12:12'); timestamp_diff ---------------- 122 (1 row) gaussdb=# SELECT timestamp_diff('second','2018-01-01 10:10:10','2018-01-01 10:12:12'); timestamp_diff ---------------- 122 (1 row) gaussdb=# SELECT timestamp_diff('microsecond','2018-01-01 10:10:10','2018-01-01 10:12:12'); timestamp_diff ---------------- 122000000 (1 row)
TIMESTAMPDIFF
- TIMESTAMPDIFF(unit , timestamp_expr1, timestamp_expr2)
Description: Returns the result of timestamp_expr2 – timestamp_expr1 in the specified unit. This function is equivalent to timestamp_diff(text, timestamp, timestamp).
Parameters: timestamp_expr1 and timestamp_expr2 are of the time expression, text, datetime, date, or time type. unit specifies the unit of the difference between two dates.
Return type: bigint
- This function takes effect only in databases in MySQL compatibility mode.
- When sql_compatibility is set to 'MYSQL', b_format_version is set to 5.7, and b_format_dev_version is set to s1, the called function is registered as b_timestampdiff. If the GUC parameter is not enabled in a database in MySQL-compatible mode, the called function is registered as timestamp_diff. You can run the \df b_timestampdiff command to query the detailed input parameter and return value of the function.
- quarter
1 2 3 4 5
gaussdb=# SELECT TIMESTAMPDIFF(QUARTER, '2018-01-01', '2020-01-01'); timestamp_diff ---------------- 8 (1 row)
- month
1 2 3 4 5
gaussdb=# SELECT TIMESTAMPDIFF(MONTH, '2018-01-01', '2020-01-01'); timestamp_diff ---------------- 24 (1 row)
- week
1 2 3 4 5
gaussdb=# SELECT TIMESTAMPDIFF(WEEK, '2018-01-01', '2020-01-01'); timestamp_diff ---------------- 104 (1 row)
- day
- hour
1 2 3 4 5
gaussdb=# SELECT TIMESTAMPDIFF(HOUR, '2020-01-01 10:10:10', '2020-01-01 11:11:11'); timestamp_diff ---------------- 1 (1 row)
- minute
1 2 3 4 5
gaussdb=# SELECT TIMESTAMPDIFF(MINUTE, '2020-01-01 10:10:10', '2020-01-01 11:11:11'); timestamp_diff ---------------- 61 (1 row)
- second
1 2 3 4 5
gaussdb=# SELECT TIMESTAMPDIFF(SECOND, '2020-01-01 10:10:10', '2020-01-01 11:11:11'); timestamp_diff ---------------- 3661 (1 row)
- microseconds
The second field, including fractional parts, is multiplied by 1,000,000.
1 2 3 4 5
gaussdb=# SELECT TIMESTAMPDIFF(MICROSECOND, '2020-01-01 10:10:10.000000', '2020-01-01 10:10:10.111111'); timestamp_diff ---------------- 111111 (1 row)
EXTRACT
- EXTRACT(field FROM source)
The extract function retrieves fields such as year or hour from date/time values. source must be a value expression of type timestamp, time, or interval. (Expressions of type date are cast to timestamp and can therefore be used as well.) field is an identifier or string that selects what field to extract from the source value. The extract function returns a value of the double precision type. The following are valid field names:
- century
The first century starts at 0001-01-01 00:00:00 AD. This definition applies to all Gregorian calendar countries. There is no century number 0. You go from -1 century to 1 century.
1 2 3 4 5
gaussdb=# SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13'); date_part ----------- 20 (1 row)
- day
- Day (1–31) of the month for timestamp.
1 2 3 4 5
gaussdb=# SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40'); date_part ----------- 16 (1 row)
- Number of days for interval.
1 2 3 4 5
gaussdb=# SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute'); date_part ----------- 40 (1 row)
- Day (1–31) of the month for timestamp.
- decade
1 2 3 4 5
gaussdb=# SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40'); date_part ----------- 200 (1 row)
- dow
Day of the week as Sunday (0) to Saturday (6)
1 2 3 4 5
gaussdb=# SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40'); date_part ----------- 5 (1 row)
- doy
Day of the year (1–365 or 366)
1 2 3 4 5
gaussdb=# SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40'); date_part ----------- 47 (1 row)
- epoch
- For timestamp with time zone values, the number of seconds since 1970-01-01 00:00:00-00 UTC (can be negative).
For date and timestamp values, the number of seconds since 1970-01-01 00:00:00-00 local time.
For interval values, the total number of seconds in the interval.
1 2 3 4 5
gaussdb=# SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08'); date_part -------------- 982384720.12 (1 row)
1 2 3 4 5
gaussdb=# SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours'); date_part ----------- 442800 (1 row)
- Way to convert an epoch value back to a timestamp
1 2 3 4 5
gaussdb=# SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1 second' AS RESULT; result --------------------------- 2001-02-17 12:38:40.12+08 (1 row)
- For timestamp with time zone values, the number of seconds since 1970-01-01 00:00:00-00 UTC (can be negative).
- hour
1 2 3 4 5
gaussdb=# SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40'); date_part ----------- 20 (1 row)
- isodow
Monday is 1 and Sunday is 7.
This is identical to dow except for Sunday.
1 2 3 4 5
gaussdb=# SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40'); date_part ----------- 7 (1 row)
- isoyear
The ISO 8601 year that the date falls in (not applicable to intervals).
Each ISO year begins with the Monday of the week containing January 4, so in early January or late December the ISO year may be different from the Gregorian year. See week for more information.
1 2 3 4 5 6 7 8 9 10
gaussdb=# SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01'); date_part ----------- 2005 (1 row) gaussdb=# SELECT EXTRACT(WEEK FROM TIMESTAMP '2006-01-01 00:00:40'); date_part ----------- 52 (1 row)
gaussdb=# SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02'); date_part ----------- 2006 (1 row) gaussdb=# SELECT EXTRACT(WEEK FROM TIMESTAMP '2006-01-02 00:00:40'); date_part ----------- 1 (1 row)
- microseconds
The second field, including fractional parts, is multiplied by 1,000,000.
1 2 3 4 5
gaussdb=# SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5'); date_part ----------- 28500000 (1 row)
- millennium
Years in the 1900s are in the second millennium. The third millennium started from January 1, 2001.
1 2 3 4 5
gaussdb=# SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40'); date_part ----------- 3 (1 row)
- milliseconds
Second field, including fractional parts, is multiplied by 1000. Note that this includes full seconds.
1 2 3 4 5
gaussdb=# SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5'); date_part ----------- 28500 (1 row)
- minute
1 2 3 4 5
gaussdb=# SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40'); date_part ----------- 38 (1 row)
- month
For timestamp values, the specific month in the year (1–12).
1 2 3 4 5
gaussdb=# SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40'); date_part ----------- 2 (1 row)
For interval values, the number of months, modulo 12 (0–11).
1 2 3 4 5
gaussdb=# SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months'); date_part ----------- 1 (1 row)
- quarter
Quarter of the year (1–4) that the date is in.
1 2 3 4 5
gaussdb=# SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40'); date_part ----------- 1 (1 row)
- second
Second field, including fractional parts (0–59).
1 2 3 4 5
gaussdb=# SELECT EXTRACT(SECOND FROM TIME '17:12:28.5'); date_part ----------- 28.5 (1 row)
- timezone
Time zone offset from UTC, measured in seconds. Positive values correspond to time zones east of UTC, negative values to zones west of UTC.
- timezone_hour
- timezone_minute
- week
Number of the week of the year that the day is in. By definition (ISO 8601), the first week of a year contains January 4 of that year. (The ISO-8601 week starts on Monday.) In other words, the first Thursday of a year is in week 1 of that year.
Because of this, it is possible for early January dates to be part of the 52nd or 53rd week of the previous year, and late December dates to be part of the 1st week of the next year. For example, 2006-01-01 is the 52nd week of 2005, and 2006-01-02 is the first week of 2006. You are advised to use the columns isoyear and week together to ensure consistency.
1 2 3 4 5 6 7 8 9 10
gaussdb=# SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01'); date_part ----------- 2005 (1 row) gaussdb=# SELECT EXTRACT(WEEK FROM TIMESTAMP '2006-01-01 00:00:40'); date_part ----------- 52 (1 row)
gaussdb=# SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02'); date_part ----------- 2006 (1 row) gaussdb=# SELECT EXTRACT(WEEK FROM TIMESTAMP '2006-01-02 00:00:40'); date_part ----------- 1 (1 row)
- year
1 2 3 4 5
gaussdb=# SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40'); date_part ----------- 2001 (1 row)
date_part
The date_part function is modeled on the traditional Ingres equivalent to the SQL-standard function extract:
date_part('field', source)
Note that here the field parameter needs to be a string value, not a name. The valid field names are the same as those for extract. For details, see EXTRACT.
Example:
1 2 3 4 5 |
gaussdb=# SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40'); date_part ----------- 16 (1 row) |
1 2 3 4 5 |
gaussdb=# SELECT date_part('hour', INTERVAL '4 hours 3 minutes'); date_part ----------- 4 (1 row) |
Table 20 specifies the formats for formatting date and time values.
|
Category |
Format |
Description |
|---|---|---|
|
Hour |
HH |
Number of hours in one day (01–12) |
|
HH12 |
Number of hours in one day (01–12) |
|
|
HH24 |
Number of hours in one day (00–23) |
|
|
Minute |
MI |
Minute (00–59) |
|
Second |
SS |
Second (00–59) |
|
FF |
Microsecond (000000–999999) |
|
|
FF1 |
Microsecond (0-9) |
|
|
FF2 |
Microsecond (00-99) |
|
|
FF3 |
Microsecond (000-999) |
|
|
FF4 |
Microsecond (0000-9999) |
|
|
FF5 |
Microsecond (00000-99999) |
|
|
FF6 |
Microsecond (000000–999999) |
|
|
SSSSS |
Second after midnight (0–86399) |
|
|
Morning and afternoon |
AM or A.M. |
Morning identifier |
|
PM or P.M. |
Afternoon identifier |
|
|
Year |
Y,YYY |
Year with comma (with four digits or more) |
|
SYYYY |
Year with four digits BC |
|
|
YYYY |
Year (with four digits or more) |
|
|
YYY |
Last three digits of a year |
|
|
YY |
Last two digits of a year |
|
|
Y |
Last one digit of a year |
|
|
IYYY |
ISO year (with four digits or more) |
|
|
IYY |
Last three digits of an ISO year |
|
|
IY |
Last two digits of an ISO year |
|
|
I |
Last one digit of an ISO year |
|
|
RR |
Last two digits of a year (A year of the 20th century can be stored in the 21st century.) |
|
|
RRRR |
Capable of receiving a year with four digits or two digits. If there are 2 digits, the value is the same as the returned value of RR. If there are 4 digits, the value is the same as YYYY. |
|
|
Era indicator BC or AD |
|
|
Month |
MONTH |
Full name of a month in uppercase (9 characters are filled in if the value is empty.) |
|
MON |
Month in abbreviated format in uppercase (with three characters) |
|
|
MM |
Month (01–12) |
|
|
RM |
Month in Roman numerals (I–XII; I=JAN) and uppercase |
|
|
Day |
DAY |
Full name of a date in uppercase (9 characters are filled in if the value is empty.) |
|
DY |
Day in abbreviated format in uppercase (with three characters) |
|
|
DDD |
Day in a year (001–366) |
|
|
DD |
Day in a month (01–31) |
|
|
D |
Day in a week (1–7). |
|
|
Week |
W |
Week in a month (1–5) (The first week starts from the first day of the month.) |
|
WW |
Week in a year (1–53) (The first week starts from the first day of the year.) |
|
|
IW |
Week in an ISO year (The first Thursday is in the first week.) |
|
|
Century |
CC |
Century (with two digits) (The 21st century starts from 2001-01-01.) |
|
Julian date |
J |
Julian date (starting from January 1 of 4712 BC) |
|
Quarter |
Q |
Quarter |
|
Item |
Format |
Description |
|---|---|---|
|
Century |
SCC |
Century. A hyphen (-) will be displayed before BC years. |
|
Year |
SYYYY |
Returns a numeric year. A hyphen (-) will be displayed before BC years. |
|
RR |
Returns the two-digit year of a date. |
|
|
RRRR |
Returns the four-digit year of a date. |
|
|
YEAR |
Returns the year of the character type. |
|
|
SYEAR |
Returns the year of the character type. A hyphen (-) will be displayed before BC years. |
|
|
Date Format |
DL |
Returns the date in the specified long date format. |
|
DS |
Returns the date in the specified short date format. |
|
|
TS |
Returns the time in the specified time format. |
|
|
Second |
FF7 |
Microsecond (0000000-9999990) |
|
FF8 |
Microsecond (00000000-99999900) |
|
|
FF9 |
Microsecond (000000000-999999000) |
The rules for RR to calculate years are as follows:
- If the range of the input two-digit year is between 00 and 49:
If the last two digits of the current year are between 00 and 49, the first two digits of the returned year are the same as the first two digits of the current year.
If the last two digits of the current year are between 50 and 99, the first two digits of the returned year equal to the first two digits of the current year plus 1.
- If the range of the input two-digit year is between 50 and 99:
If the last two digits of the current year are between 00 and 49, the first two digits of the returned year are equal to the first two digits of the current year minus 1.
If the last two digits of the current year are between 50 and 99, the first two digits of the returned year are the same as the first two digits of the current year.
- The to_date and to_timestamp functions support the FX pattern (the input strictly corresponds to a pattern) and the X pattern (decimal point).
- The input pattern can appear only once, indicating that the patterns of the same information cannot appear at the same time. For example, SYYYY and BC cannot be used together.
- The pattern is case-insensitive.
- You are advised to use a separator between the input and the pattern. Otherwise, the behavior may not be compatible with database O.
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
