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 a time zone.
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. This volatile function obtains the latest timestamp for each scan. Therefore, the result of each function 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 indicating the initiation of the current SQL statement.
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) |

- When sql_compatibility is 'ORA' and the GUC parameter a_format_date_timestamp is on, this function operates as follows:
- It returns a timestamp for the system time indicating the initiation of the current SQL statement.
- The return type is timestamp without time zone, accurate to seconds. The column name is current_date.
- The return type is timestamp when a_format_version is 10c and a_format_dev_version is s2.
- It returns a date for the system time indicating the initiation of the transaction, if the GUC parameter a_format_date_timestamp is disabled.
- When sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1, this function operates as follows:
- current_date can be called with parentheses.
- The current_date function works by calling the curdate function. To view the detailed input parameters and output values of curdate, run the \df curdate command.
current_time()
Description: Returns the system time indicating the initiation of the current transaction. When sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1, this function returns the system time indicating the initiation of the current SQL statement.
Return type: time with time zone (or time without time zone when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1).
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 indicating the initiation of the current SQL statement.
Parameter: precision (number of decimal places after the second). The precision is of the int type, within the range of [0,6] and with a default value of 0. Using an invalid precision will trigger an error.
Return type: time without time zone
Implementation method: 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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1. The curent_time function works by calling the curtime function. To view the detailed input parameters and output values of curtime, run the \df curtime command.
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 'MYSQL', b_format_version is '5.7', and b_format_dev_version is 's1') or timestamp with time zone (for other setups).
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) |

When sql_compatibility is set to 'ORA', or sql_compatibility is set to 'MYSQL' and the b_format_version and b_format_dev_version parameters are not set, the return result is the timestamp of the system time when the latest SQL statement is started, and the return type is timestamp with time zone. If the GUC parameter a_format_date_timestamp is set to on, the column name is current_timestamp.
In PL/SQL, a simple assignment statement, such as time1 := current_timestamp, is considered as an expression. Therefore, the return result is the timestamp of the system time when the latest SQL statement is started.
When sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1, this function operates as follows:
- The return type is timestamp without time zone.
- The precision of the returned result is 0.
- It returns a timestamp for the system time indicating the initiation of the current SQL statement.
- This function is not registered but is implemented through TYPE conversion. Therefore, running the gsql command \df+ retrieves the function information in modes other than MySQL-compatible (version 5.7).
- When the function is called multiple times in an anonymous block, the entire anonymous block is considered as a statement. Therefore, the same result is returned each time the function is called.
current_timestamp()
Description: Returns the system time when the current SQL statement 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 is available only when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1. It operates as follows:
- The return type is timestamp without time zone.
- The precision of the returned result is 0.
- It returns a timestamp for the system time indicating the initiation of the current SQL statement.
- This function is not registered but is implemented through TYPE conversion. Therefore, running the gsql command \df+ retrieves the function information in modes other than MySQL-compatible (version 5.7).
current_timestamp(precision)
Description: Obtains the current date and time, and rounds the microseconds of the result to the specified decimal place.
Parameter: precision (number of decimal places after the second). The precision is of the int type, within the range of [0,6] and with a default value of 0. Setting a value greater than 6 will trigger an alarm and return a time value with a precision of 6. Using any other invalid precision will trigger an error.
Return type: timestamp without time zone (when sql_compatibility is 'MYSQL', b_format_version is '5.7', and b_format_dev_version is 's1') or timestamp with time zone (for other setups).
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.
- When sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1, this function operates as follows:
- The return type is timestamp without time zone.
- It returns a timestamp for the system time indicating the initiation of the current SQL statement.
- This function is not registered but is implemented through TYPE conversion. Therefore, running the gsql command \df+ retrieves the function information in modes other than MySQL-compatible (version 5.7).
- When sql_compatibility is 'ORA' and the GUC parameter a_format_date_timestamp is on, this function operates as follows:
- The return type is timestamp with time zone, and the column name is current_timestamp.
- It returns a timestamp for the system time indicating the initiation of the current SQL statement.
- When a_format_version is 10c and a_format_dev_version is s2, the precision parameter accepts either the numeric type (integers) or the int type.
- 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 timestamp of the system time when the current SQL statement starts.
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) |
systimestamp()
Description: Gets the date and time of the current system.
Return type: timestamp with time zone
Example:
1 2 3 4 5 6 7 8 9 10 11 12 |
gaussdb=# SELECT systimestamp(); systimestamp ------------------------------- 2024-04-16 15:55:21.614342+08 (1 row) -- After the behavior_compat_options parameter is set to sys_function_without_brackets, calling without parentheses is supported. gaussdb=# SET behavior_compat_options='sys_function_without_brackets'; gaussdb=# SELECT systimestamp; systimestamp ------------------------------- 2024-04-16 15:55:21.614342+08 (1 row) |

This function takes effect only when sql_compatibility is 'ORA'.
systimestamp(precision)
Description: Obtains the date and time of the current system and displays the microsecond part of the result based on the specified precision.
Parameter: precision (number of decimal places after the second). INT type. Range: [0,6]. The default value is 6. When the value exceeds 6, a warning message is displayed, the time value is output with precision of 6.
Return type: timestamp with time zone
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
gaussdb=# SELECT systimestamp(6); systimestamp ------------------------------- 2024-04-16 15:55:21.614342+08 (1 row) gaussdb=# SELECT systimestamp(9); WARNING: TIMESTAMP(9) WITH TIME ZONE precision reduced to maximum allowed, 6 CONTEXT: referenced column: systimestamp systimestamp ------------------------------- 2024-04-16 15:59:49.492505+08 (1 row) gaussdb=# SELECT systimestamp(5.0); systimestamp ------------------------------ 2024-04-16 16:00:23.48079+08 (1 row) |

This function takes effect only when sql_compatibility is 'ORA'.
date_part(text, timestamp)
Description: Obtains fields such as year or hour from date/time values. It is equivalent to extract(field from timestamp).
Parameter: The timestamp type can be abstime, date, interval, reltime, time with time zone, time without time zone, timestamp with time zone, or 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.
Parameter type: timestamp with time zone, timestamp without time zone.
Return type: timestamp without time zone
Example:
1 2 3 4 5 6 7 8 9 10 |
gaussdb=# SELECT trunc(TIMESTAMP '2001-02-16 20:38:40'); trunc --------------------- 2001-02-16 00:00:00 (1 row) gaussdb=# SELECT trunc(TIMESTAMP WITH TIME ZONE '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.
Parameters:
- Type of arg1: interval, timestamp with time zone, timestamp without time zone
- Type of arg2: text. Table 1 lists the units supported by arg2.
Unit |
Description |
---|---|
mil, mils, millennia, millennium |
Millennium |
c, cc, cent, centuries, century |
Century |
dec, decade, decades, decs |
Decade |
y, year, years, yr, yrs, yyyy |
Year |
q, qtr, quarter |
Quarter |
mm, mon, mons, month, months |
Month |
w, week, weeks |
Week |
j, d, day, days, dd, ddd |
Day |
h, hh, hour, hours, hr, hrs |
Hour |
m, mi, min, mins, minute, minutes |
Minute |
s, sec, second, seconds, secs |
Second |
millisecon, ms, msec, msecond, mseconds, msecs |
Millisecond |
microsecon, us, usec, usecond, useconds, usecs |
Microsecond |
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.
Parameters:
- 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 takes effect in ORA-compatible databases where a_format_version is 10c and a_format_dev_version is s1.
daterange(arg1, arg2)
Description: Obtains time boundary information.
Parameters: The type of arg1 and arg2 is 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.
Parameters: The type of arg1 and arg2 is date, and the type of text is 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 so, the function will return t. If not, the function will return f.
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 so, the function will return t. If not, the function will return f.
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 an interval, converting 30-day time periods to 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: Adjusts an interval, converting 24-hour time periods to days.
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: Returns the system time indicating the initiation of the current transaction. When sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1, this function returns the system date and time indicating the initiation of the current SQL statement.
Return type: time (or timestamp without time zone when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1).
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 indicating the initiation of the current SQL statement.
Parameter: precision (number of decimal places after the second). The precision is of the int type, within the range of [0,6] and with a default value of 0. Using an invalid precision will trigger an error.
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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is 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) -- In MySQL-compatible mode: gaussdb_m=# SELECT localtimestamp; timestamp --------------------- 2023-08-21 15:27:55 (1 row) -- Enable the GUC parameter a_format_date_timestamp 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) |

When sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1, this function operates as follows:
- The system date and time indicating the initiation of the current SQL statement are returned.
- The return type is timestamp without time zone, and the column name is timestamp.
When sql_compatibility is 'ORA' and the GUC parameter a_format_date_timestamp is on, this function operates as follows:
- The return type is timestamp without time zone, and the column name is localtimestamp.
- It returns a timestamp for the system time indicating the initiation of the current SQL statement.
- This prevents the optimizer from obtaining the constant time in advance. As a result, the obtained time is incorrect in the gplan scenario.
- It returns the system date and time indicating the initiation of the transaction, if the GUC parameter a_format_date_timestamp is disabled.
localtimestamp[([precision])]
Description: Specifies the current date and time.
Parameter: precision (number of decimal places after the second). The precision is of the int type, within the range of [0,6] and with a default value of 0. Setting a value greater than 6 will trigger an alarm and return a time value with a precision of 6. Using any other invalid precision will trigger an error.
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 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1, this function returns the system date and time indicating the initiation of the current SQL statement. It can be called with parentheses and without input parameters.
- When sql_compatibility is 'ORA' and the GUC parameter a_format_date_timestamp is on, this function operates as follows:
- It returns a timestamp for the system time indicating the initiation of the current SQL statement.
- The return type is timestamp without time zone, and the column name is localtimestamp.
- When a_format_version is 10c and a_format_dev_version is s2, the precision parameter accepts either the numeric type (integers) or the int type.
- It returns the system date and time indicating the initiation of the transaction, if the GUC parameter a_format_date_timestamp is disabled.
maketime(hour, minute, second)
Description: Generates a value of the time type based on the input parameters hour, minute, and second, which 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 is available only when sql_compatibility is 'MYSQL', b_format_version is '5.7', and b_format_dev_version is 's1'. It operates as follows:
- It returns NULL if any of the following conditions is met:
- The input parameter minute is less than 0 or greater than or equal to 60.
- The input parameter second is less than 0 or greater than or equal to 60.
- Any parameter is NULL.
- The returned value of the time type contains six decimal places. If second contains more than six decimal places, it will be 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 will be returned based on the positive and negative values of hour.
- maketime does not support self-nesting.
now()
Description: Returns the system date and time indicating the initiation of the current transaction. The results remain consistent within the same transaction. This function returns the system date and time indicating the initiation of the current SQL statement, but only when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
Return type: timestamp with time zone (or timestamp without time zone when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1).
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) -- In MySQL-compatible mode: gaussdb_m=# SELECT now(); timestamp --------------------- 2023-08-21 17:17:42 (1 row) |
now(precision)
Description: Returns the system date and time indicating the initiation of the current SQL statement.
Parameter: precision (number of decimal places after the second). The precision is of the int type, within the range of [0,6] and with a default value of 0. Setting a value greater than 6 will trigger an integer value alarm and return a time value with a precision of 6. Using any other invalid precision will trigger an error.
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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
timenow()
Description: Returns the system date and time indicating the initiation of the current SQL statement.
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 takes effect in ORA-compatible databases where a_format_version is 10c and a_format_dev_version is s2.
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 oracle to maintain compatibility with the output format interval.
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 oracle to maintain compatibility with the output format interval.
Return type: interval
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 |
gaussdb=# \c gaussdb_o; Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "gaussdb_o" as user "omm". gaussdb_o=# SET a_format_version='10c'; SET gaussdb_o=# SET a_format_dev_version='s2'; SET gaussdb=# SELECT numtoyminterval(100, 'MONTH'); numtoyminterval ----------------- 8 years 4 mons (1 row) gaussdb_o=# SET intervalstyle = oracle; SET gaussdb_o=# SELECT numtoyminterval(100, 'MONTH'); numtoyminterval ----------------- 8-4 (1 row) gaussdb_o=# \c postgres Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "postgres" as user "omm". gaussdb=# DROP DATEBASE gaussdb_o; DROP DATABASE |

This function takes effect in ORA-compatible databases where a_format_version is 10c and a_format_dev_version is s2.
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 takes effect in ORA-compatible databases where a_format_version is 10c and a_format_dev_version is s2.
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 takes effect in ORA-compatible databases where a_format_version is 10c and a_format_dev_version is s2.
- If the value of session time zone is GMT+08:00 or GMT-08:00, the offset of the positive value is used as the position west of Greenwich Mean Time (GMT). For example, GMT+08:00 indicates GMT–08:00 and GMT-08:00 indicates GMT+08:00.
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 takes effect in ORA-compatible databases where a_format_version is 10c and a_format_dev_version is s2.
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 takes effect in ORA-compatible databases where a_format_version is 10c and a_format_dev_version is s2.
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 indicating the initiation of the current SQL statement.
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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
current_sysdate()
Description: Returns the system date and time indicating the initiation of the current SQL statement.
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 of the system time when the current function is called. It is similar to the clock_timestamp function, but the return type is text.
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: Returns the system date and time indicating the initiation of the current transaction.
Return type: timestamp with time zone (or timestamp without time zone when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1).
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) -- In MySQL-compatible mode: 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.
Parameters:
- d: indicates a value of the timestamp type or a value that can be implicitly converted into the timestamp type.
- n: indicates a value of the INTEGER type or a value that can be implicitly converted into 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) |

- If the calculation result is greater than 9999, an error will be reported.
- If the input parameter n is a decimal, it will be truncated rather than rounded off.
last_day(d)
Description: Returns the date of the last day of the month that contains time point d.
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 takes effect in ORA-compatible databases where a_format_version is 10c and a_format_dev_version is s1.
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 6 7 8 9 10 11 12 13 14 15 16 17 |
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) gaussdb=# SELECT next_day(timestamp '2024-01-17 00:00:00', 7.9999999); ERROR: Week day out of range, the range is 1 ... 7 CONTEXT: referenced column: next_day gaussdb=# SET a_format_version='10c'; SET gaussdb=# SET a_format_dev_version='s1'; SET gaussdb=# SELECT next_day(timestamp '2024-01-17 00:00:00', 7.9999999); next_day --------------------- 2024-01-20 00:00:00 (1 row) |

- If a_format_version is set to 10c, a_format_dev_version is set to s1, and the second parameter is a floating-point number, this function discards the decimal part in the ORA-compatible database. Otherwise, this function rounds off the decimal part.
- In an ORA-compatible database, the first parameter can be of TIMESTAMP WITH TIME ZONE type only when a_format_version is set to 10c and a_format_dev_version is set to s1.
- The second parameter of next_day supports abbreviations (case-insensitive), including SUN, MON, TUE, WED, THU, THUR, FRI, and SAT.
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) |
tintervalrel(tinterval)
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: Checks whether the first parameter is greater than the second.
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: Checks whether the first parameter is less than the second.
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.
Parameters: cstring, oid, and integer
Return type: smalldatetime
smalldatetime_larger(smalldatetime, smalldatetime)
Description: Returns a larger timestamp.
Parameter: smalldatetime, smalldatetime
Return type: smalldatetime
smalldatetime_le(smalldatetime, smalldatetime)
Description: Checks whether the first parameter is less than the second.
Parameter: smalldatetime, smalldatetime
Return type: Boolean
smalldatetime_lt(smalldatetime, smalldatetime)
Description: Checks whether the first parameter is greater than the second.
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 into 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 into abstime.
Parameter: smalldatetime
Return type: abstime
smalldatetime_to_time(smalldatetime)
Description: Converts smalldatetime into time.
Parameter: smalldatetime
Return type: time without time zone
smalldatetime_to_timestamp(smalldatetime)
Description: Converts smalldatetime into timestamp.
Parameter: smalldatetime
Return type: timestamp without time zone
smalldatetime_to_timestamptz(smalldatetime)
Description: Converts smalldatetime into timestamptz.
Parameter: smalldatetime
Return type: timestamp with time zone
smalldatetime_to_varchar2(smalldatetime)
Description: Converts smalldatetime into 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 of CURRENT_TIME and CURRENT_TIMESTAMP(precision) contain time zone information. The values of 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 provides the full precision that can be obtained.
These functions maintain consistent values throughout the transaction by returning results based on the start time of the current transaction. This ensures that the transaction can maintain consistency at the "current" moment and that multiple modifications within the same transaction retain 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().
Note that when a transaction starts, sql_compatibility is set to 'ORA', and the GUC parameter a_format_date_timestamp is set to on, the CURRENT_DATE, CURRENT_TIMESTAMP(precision), LOCALTIMESTAMP, and LOCALTIMESTAMP (precision) functions return the timestamp when the current SQL statement starts. If the GUC parameter a_format_date_timestamp is disabled, these functions return 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. In addition, transaction_timestamp() still indicates the transaction start time, but its 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). During the execution of the first command of a transaction, the return values of statement_timestamp() and transaction_timestamp() are the same. However, in subsequent commands, the return values of statement_timestamp() and transaction_timestamp() may be different.
- The following APIs return the actual current time when the function is called:
clock_timestamp() timeofday()
clock_timestamp() returns the actual current time. Therefore, its value changes even in the same SQL statement. Similar to clock_timestamp(), timeofday() also returns the actual current time. However, the result is a formatted text string instead of a timestamp with time zone value.
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: See Table 2.
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'. |
See the system view PG_TIMEZONE_NAMES. |
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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
sec_to_time(seconds)
Description: Converts the number of seconds into a time of the time type.
Parameters: See Table 3.
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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
adddate(date, INTERVAL val unit)
Description: Returns a new date by adding a certain interval to a date.
Parameters: See Table 4.
Parameter |
Type |
Description |
Value Range |
---|---|---|---|
date |
A time expression, or a type such as text, datetime, date, or time. |
Date to which a time interval needs to be added. |
See the value ranges of the corresponding types. |
val |
Integer, floating-point number, or character string |
Time interval to be added. |
See the value ranges of the corresponding types. |
unit |
Keyword |
Unit of the time interval. |
YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MICROSECOND, etc. 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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
- In the PREPARE statement, the second parameter of the adddate function is an interval expression. When parameter $1 is used to replace the second parameter, the result does not meet the expectation. For example, in the case PREPARE p1 AS SELECT adddate('2023-01-01 10:00:00', $1); EXECUTE p1(interval 13 hour);, the unexpected result '2023-01-01 10:00:00' is returned.
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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is 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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
curtime([precision])
Description: Returns the system time when the SQL query call starts.
Parameter: precision (number of decimal places after the second). The precision is of the int type, within the range of [0,6] and with a default value of 0. If the provided value can be converted into an integer within this range, the function will return a time value with the specified precision. Otherwise, the function will report an error.
Return type: time without time zone
Implementation method: The system function curtime is registered.
Example:
1 2 3 4 5 |
gaussdb=# SELECT curtime(3); curtime -------------- 16:59:57.203 (1 row) |

This function takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
date_add(date,INTERVAL val unit)
Description: Adds a period of time to a specified date and returns the calculation result.
Parameters: See Table 5.
Parameter |
Type |
Description |
Value Range |
---|---|---|---|
date |
A time expression, or a type such as text, datetime, date, or time. |
Date to which a time interval needs to be added. |
See the value ranges of the corresponding types. |
val |
Integer, floating-point number, or character string |
Time interval to be added. |
See the value ranges of the corresponding types. |
unit |
Keyword |
Unit of the time interval. |
YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MICROSECOND, etc. For details, see Time Interval Expressions. |
Return type: a single return value. For details, see Table 6.
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 input parameter date is of the datetime type. Alternatively, date is of the date type, and unit is less than day, such as hour or second. |
Example:
gaussdb=# SELECT DATE_ADD('2018-05-01', INTERVAL 1 DAY);
date_add
------------
2018-05-02
(1 row)

- This function takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
- In the PREPARE statement, the second parameter of date_add is an interval expression and cannot be replaced by parameter $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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
date_format(date, format)
Description: Outputs the date and time in the specified format.
Parameters: See Table 7.
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. |
See Table 8. |
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 |
Day of a year (001, ..., 366) |
%k |
Hour (0, ..., 23) |
%l |
Hour (1, ..., 12) |
%M |
Month name (January, ..., December) |
%m |
Two-digit month (00, ..., 12) |
%p |
AM or PM |
%r |
12-hour time (hh:mm:ss followed by AM or PM). |
%S |
Second (00, ..., 59) |
%s |
Second (00, ..., 59) |
%T |
24-hour time (hh:mm:ss) |
%U |
Week of a year (00, ..., 53). Each week starts from Sunday. |
%u |
Week of a year (00, ..., 53). Each week starts from Monday. |
%V |
Week of a year (01, ..., 53). Each week starts from Sunday. |
%v |
Week of a year (01, ..., 53). Each week starts from Monday. |
%W |
Name of a week (Sunday, ..., Saturday) |
%w |
Every day in a week (0 = Sunday, ..., 6 = Saturday) |
%X |
Week of a year. Each week starts from Sunday. The value is a four-digit number and is used for %V. |
%x |
Week of a year. Each week starts from Monday. The value is a four-digit number and is used for %v. |
%Y |
Four-digit year |
%y |
Two-digit year |
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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
date_sub(date, INTERVAL val unit)
Description: Returns a new date by subtracting a certain interval from a date.
Parameters: See Table 9.
Parameter |
Type |
Description |
Value Range |
---|---|---|---|
date |
A time expression, or a type such as text, datetime, date, or time. |
Date to which a time interval needs to be added. |
See the value ranges of the corresponding types. |
val |
Integer, floating-point number, or character string |
Time interval to be added. |
See the value ranges of the corresponding types. |
unit |
Keyword |
Unit of the time interval. |
YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MICROSECOND, etc. 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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
- In the PREPARE statement, the second parameter of date_sub is an interval expression and cannot be replaced by parameter $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 as well as 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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
datediff(expr1, expr2)
Description: Returns the number of days between two time expressions.
Parameter: a time expression, or a type such as text, datetime, date, or time.
Return type: int
Example:
gaussdb=# SELECT datediff('2021-11-12','2021-11-13');
datediff
----------
-1
(1 row)

This function takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is 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: date and time to be extracted, which can be represented as a time expression or a type such as text, datetime, date, or time.
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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
dayname()
Description: Returns the name of a day in a week.
Parameter: a time expression, or a type such as TEXT, DATETIME, DATE, or TIME.
Return type: text
Example:
gaussdb=# SELECT dayname('2023-10-11');
dayname
-----------
Wednesday
(1 row)

- This function takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is 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: a time expression, or a type such as text, datetime, date, or time.
Return type: bigint
Example:
gaussdb=# SELECT dayofweek('2023-04-16');
dayofweek
-----------
1
(1 row)

This function takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
dayofyear()
Description: Returns the number of a day in the year.
Parameter: a time expression, or a type such as TEXT, DATETIME, DATE, or TIME.
Return type: integer; value range: [1,366].
Example:
gaussdb=# SELECT dayofyear('2000-12-31');
dayofyear
-----------
366
(1 row)

This function takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is 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
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 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1, the called function is registered as b_extract. In other setups, date_part is registered instead. You can run commands such as \df b_extract to query the detailed input parameter and output values of the function.
The GUC parameter default_week_format is used to process special week issues. The default value is 0. For details, see Table 11.
default_week_format |
First Day of a Week |
Range |
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 date value corresponding to the specified number of days.
Parameter: a time expression, or a type such as 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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
- The function processes null and zero characters as 0. If input parameters cannot be correctly converted into bigint, the function will report an error.
- 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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is 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 types, 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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
- In the PREPARE statement, the first parameter of get_format is a keyword and cannot be replaced by parameter $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: a time expression, or a type such as text, datetime, date, or time.
Return type: bigint
Example:
gaussdb=# SELECT HOUR('10:10:10.1');
hour
------
10
(1 row)

This function takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is 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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
microsecond()
Description: Returns the microsecond part of a time after you enter a time type.
Parameter: a time expression, or a type such as 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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
minute()
Description: Returns the minute part of a time after you enter a time type.
Parameter: a time expression, or a type such as text, datetime, date, or time.
Return type: bigint
Example:
gaussdb=# SELECT MINUTE(time'10:10:10');
minute
--------
10
(1 row)

This function takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is 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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
monthname()
Description: Returns the full month name of a date.
Parameter: a time expression, or a type such as TEXT, DATETIME, DATE, or TIME.
Return type: text
Example:
gaussdb=# SELECT monthname('2023-02-28');
monthname
-----------
February
(1 row)

- This function takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is 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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
period_diff(p1,p2)
Description: Calculates the month difference between two time segments.
Parameters: The parameter type is bigint. The two parameters use the YYMM or YYYYMM format to indicate the time segment.
Return type: bigint (month difference)
Example:
gaussdb=# SELECT period_diff('202101', '202102');
period_diff
-------------
-1
(1 row)

This function takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
second()
Description: Returns the second part of a time after you enter a time type.
Parameter: a time expression, or a type such as text, datetime, date, or time.
Return type: bigint
Example:
gaussdb=# SELECT SECOND('2023-5-5 10:10:10');
second
--------
10
(1 row)

This function takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is 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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
str_to_date(str, format)
Description: Converts a specified character string to a date/time based on the specified date format.
Parameters:
- str: string of the text type, which needs to be formatted into a date.
- format: string format of the text type. 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
Day of a year (001, ..., 366)
%k
Hour (0, ..., 23)
%l
Hour (1, ..., 12)
%M
Month name (January, ..., December)
%m
Two-digit month (00, ..., 12)
%p
AM or PM
%r
12-hour time (hh:mm:ss followed by AM or PM).
%S
Second (00, ..., 59)
%s
Second (00, ..., 59)
%T
24-hour time (hh:mm:ss)
%U
Week of a year (00, ..., 53). Each week starts from Sunday.
%u
Week of a year (00, ..., 53). Each week starts from Monday.
%V
Week of a year (01, ..., 53). Each week starts from Sunday.
%v
Week of a year (01, ..., 53). Each week starts from Monday.
%W
Name of a week (Sunday, ..., Saturday)
%w
Every day in a week (0 = Sunday, ..., 6 = Saturday)
%X
Week of a year. Each week starts from Sunday. The value is a four-digit number and is used for %V.
%x
Week of a year. Each week starts from Monday. The value is a four-digit number and is used for %v.
%Y
Four-digit year
%y
Two-digit year
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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is 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 as well as 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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
subdate(expr,INTERVAL val unit)
Description: Specifies the start date and time as well as the time interval to be subtracted from the start date and time, and returns the subtraction result.
Parameters: See Table 13.
Parameter |
Type |
Description |
Value Range |
---|---|---|---|
expr |
A time expression, or a type such as 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, MICROSECOND, etc. 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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
- In the PREPARE statement, the second parameter of subdate is an interval expression. When parameter $1 is used to replace the second parameter, the result is unexpected. For example, in the case PREPARE p1 AS SELECT subdate('2023-01-01 10:00:00', $1); EXECUTE p1(interval 13 hour);, the unexpected result '2023-01-01 10:00:00' is returned.
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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is 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 type is determined by the data type of expr1. If expr1 is parsed as timestamp without time zone, the return type will also be timestamp without time zone. If it parsed as time, the return type will also be 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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
time_format(time, format)
Description: Formats the time input parameter based on the format specified by format.
Parameters:
- time: a time expression or a type such as text, datetime, date, or time.
- format: 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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
time_to_sec()
Description: Converts the input parameter of the time type to the number of seconds.
Parameter: a time expression, or a type such as 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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
timediff()
Description: Calculates the difference between two time points and returns an interval.
Parameters: two parameters, which can be represented as a time expression or a type such as text, datetime, date, or time.
Return type: TIME WITHOUT TIMEZONE
Example:
gaussdb=# SELECT timediff(date'2022-12-30',20221229);
timediff
----------
24:00:00
(1 row)

This function takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
timestampadd(unit,interval,datetime_expr)
Description: Returns a new timestamp calculated by adding multiple intervals of a unit to datetime_expr.
Parameters: See Table 15.
Parameter |
Type |
Description |
Value Range |
---|---|---|---|
unit |
Keyword |
Unit of the time 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 |
Value of the time interval. |
See the value ranges of the corresponding types. |
datetime_expr |
A time expression, or a type such as text, datetime, date, or time. |
Date and time to be changed. If the input value is text, the return type will also be text. If the input value is time, the return type will also be time. For other input types, the return type will be 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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
- The timestamp_add function is registered to implement timestampadd. You can run commands such as \df timestamp_add to query the detailed input parameter and output values of the function.
- In the PREPARE statement, the first parameter of timestampadd is a keyword and cannot be replaced by parameter $1, for example, PREPARE p1 AS SELECT timestampadd($1, -2, '2023-01-01');.
to_days()
Description: Returns the number of days since January 1, 0000.
Parameter: a time expression, or a type such as 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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
to_seconds()
Description: Returns the number of seconds since the year 0 A.D.
Parameter: a time expression, or a type such as 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 takes effect when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is 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: a time expression, or a type such as text, datetime, date, or time.
Return type: numeric
Example:
gaussdb=# SELECT UNIX_TIMESTAMP('2022-12-22');
unix_timestamp
----------------
1671638400
(1 row)

This function takes effect only when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is 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 takes effect only when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is 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 takes effect only when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is 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 takes effect only when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
week(date[, mode])
Description: Returns the week of a date.
Parameters:
- date: specifies the date and time, which can be represented as a time expression or a type such as text, datetime, date, or time.
- Table 16 explains the optional parameter mode. If not specified, it is treated as 0.
Table 16 Description of mode mode
First Day of a Week
Range
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 takes effect only when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is 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: a time expression, or a type such as text, datetime, date, or time.
Return type: bigint
Example:
gaussdb=# SELECT weekday('1970-01-01 12:00:00');
weekday
---------
3
(1 row)

This function takes effect only when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
weekofyear(date)
Description: Returns the calendar week of the date and time. The value ranges from 1 to 53. This function is equivalent to week(date, 3).
Parameters:
- date: specifies the date and time, which can be represented as a time expression or a type such as text, datetime, date, or time.
- 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 takes effect only when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
year()
Description: Extracts the year part of the date and time and returns the result.
Parameter: date and time to be extracted, which can be represented as a time expression or a type such as text, datetime, date, or time.
Return type: int
Example:
gaussdb=# SELECT year('23-05-22');
year
------
2023
(1 row)

This function takes effect only when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is 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 explains the optional parameter mode. If not specified, it is treated as 0.
Table 17 Description of mode mode
First Day of a Week
Range
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 takes effect only when sql_compatibility is 'MYSQL', b_format_version is 5.7, and b_format_dev_version is s1.
Table 18 describes the formats for truncating date/time values.
Category |
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 week). |
WEEK |
||
Month |
MM |
Truncates date/time values, accurate to the month (the first day of the month). |
MON |
||
MONTH |
||
Quarter |
Q |
Truncates date/time values, accurate to the quarter (the first day of the quarter). |
QTR |
||
QUARTER |
||
Year |
Y |
Truncates date/time values, accurate to the year (the first day of the year). |
YEAR |
||
YR |
||
YYYY |
||
Decade |
DEC |
Truncates date/time values, accurate to the decade (the first day of the decade). |
DECADE |
||
Century |
C |
Truncates date/time values, accurate to the century (the first day of the century). |
CC |
||
CENT |
||
CENTURY |
||
Millennium |
MIL |
Truncates date/time values, accurate to the millennium (the first day of the millennium). |
MILLENNIA |
||
MILLENNIUM |
Category |
Format |
Description |
---|---|---|
Minute |
M |
Truncates or rounds off date/time values, accurate to the minute (00–59). |
MI |
||
MIN |
||
MINUTE |
||
Hour |
H |
Truncates or rounds off date/time values, accurate to the hour (00–23). |
HH |
||
HOUR |
||
HR |
||
HH12 |
||
HH24 |
||
Day |
DD |
Truncates or rounds off date/time values, accurate to the day (01-01 to 12-31). |
DDD |
||
J |
||
ISO week |
IW |
Truncates or rounds off date/time values, accurate to the week (the first day of the week is Monday). |
Week |
DAY |
Truncates or rounds off date/time values, accurate to the week (the first day of the week is Sunday). |
DY |
||
D |
||
Week of the month |
W |
Truncates or rounds off date/time values, accurate to the week (the first day of the week is the first day of the month). |
Week of the year |
WW |
Truncates or rounds off date/time values, accurate to the week (the first day of the week is the first day of the year). |
Month |
MM |
Truncates or rounds off date/time values, accurate to the month (the first day of the month). |
MON |
||
MONTH |
||
RM |
||
Quarter |
Q |
Truncates or rounds off date/time values, accurate to the quarter (the first day of the quarter). |
QTR |
||
QUARTER |
||
Year |
Y |
Truncates or rounds off date/time values, accurate to the year (the first day of the year). |
YEAR |
||
YR |
||
YYYY |
||
SYYYY |
||
YYY |
||
YY |
||
SYEAR |
||
Decade |
DEC |
Truncates or rounds off date/time values, accurate to the decade (the first day of the decade). |
DECADE |
||
Century |
C |
Truncates or rounds off date/time values, accurate to the century (the first day of the century). |
CC |
||
CENT |
||
CENTURY |
||
SCC |
||
Millennium |
MIL |
Truncates or rounds off date/time values, accurate to the millennium (the first day of the millennium). |
MILLENNIA |
||
MILLENNIUM |

The processing methods described in Table 19 take effect only in ORA-compatible databases where a_format_version is 10c and a_format_dev_version is s1.
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) |
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