Date and Time Functions
age(timestamp, timestamp)
Description: Subtracts arguments and returns the difference. If the result is negative, the returned result is also negative.
Return type: interval
Example:
1 2 3 4 5 |
SELECT age(timestamp '2001-04-10', timestamp '1957-06-13'); age ------------------------- 43 years 9 mons 27 days (1 row) |
age(timestamp)
Description: Subtracts the argument from the current date.
Return type: interval
Example:
1 2 3 4 5 |
SELECT age(timestamp '1957-06-13'); age ------------------------- 60 years 2 mons 18 days (1 row) |
adddate(date, interval | int)
Description: Returns the result of a given datetime plus the time interval of a specified unit. The default unit is day (when the second parameter is an integer).
Return type: timestamp
Example:
When the input parameter is of the text type:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
select adddate('2020-11-13', 10); adddate ------------ 2020-11-23 (1 row) select adddate('2020-11-13', interval '1' month); adddate ------------ 2020-12-13 (1 row) select adddate('2020-11-13 12:15:16', interval '1' month); adddate --------------------- 2020-12-13 12:15:16 (1 row) select adddate('2020-11-13', interval '1' minute); adddate --------------------- 2020-11-13 00:01:00 (1 row) |
When the input parameter is of the date type:
1 2 3 4 5 6 7 8 9 10 11 |
select adddate(current_date, 10); adddate ------------ 2021-09-24 (1 row) select adddate(date '2020-11-13', interval '1' month); adddate --------------------- 2020-12-13 00:00:00 (1 row) |
addtime(timestamp | time | text, interval | text)
Description: Returns the result of a given datetime plus the time interval of a specified unit. This function is supported by version 8.2.0 or later clusters.
Return type: same as the type of the first input parameter.
Example:
1 2 3 4 5 |
select addtime('2020-11-13 01:01:01', '23:59:59'); addtime --------------------- 2020-11-14 01:01:00 (1 row) |
subdate(date, interval | int)
Description: Returns the result of a given datetime minus the time interval of a specified unit. The default unit is day (when the second parameter is an integer).
Return type: timestamp
Example:
When the input parameter is of the text type:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
select subdate('2020-11-13', 10); subdate ------------ 2020-11-03 (1 row) select subdate('2020-11-13', interval '2' month); subdate ------------ 2020-09-13 (1 row) select subdate('2020-11-13 12:15:16', interval '1' month); subdate --------------------- 2020-10-13 12:15:16 (1 row) select subdate('2020-11-13', interval '2' minute); subdate --------------------- 2020-11-12 23:58:00 (1 row) |
When the input parameter is of the date type:
1 2 3 4 5 6 7 8 9 10 11 |
select subdate(current_date, 10); subdate ------------ 2021-09-05 (1 row) select subdate(current_date, interval '1' month); subdate --------------------- 2021-08-15 00:00:00 (1 row) |
subtime(timestamp | time | text, interval | text)
Description: Returns the result of a given datetime minus the time interval of a specified unit. This function is supported by version 8.2.0 or later clusters.
Return type: same as the type of the first input parameter.
Example:
1 2 3 4 5 |
select subtime('2020-11-13 01:01:01', '23:59:59'); addtime --------------------- 2020-11-12 01:01:02 (1 row) |
date_add(date, interval)
Description: Returns the result of a given datetime plus the time interval of a specified unit. It is equivalent to adddate(date, interval | int).
Return type: timestamp
date_sub(date, interval)
Description: Returns the result of a given datetime minus the time interval of a specified unit. It is equivalent to subdate(date, interval | int).
Return type: timestamp
timestampadd(field, numeric, timestamp)
Description: Adds an integer interval in the unit of field (the number of seconds can be a decimal) to a datetime expression. If the value is negative, the corresponding time interval is subtracted from the given datetime expression. The field can be year, month, quarter, day, week, hour, minute, second, or microsecond.
Return type: timestamp
Example:
1 2 3 4 5 6 7 8 9 10 11 |
select timestampadd(year, 1, timestamp '2020-2-29'); timestampadd --------------------- 2021-02-28 00:00:00 (1 row) select timestampadd(second, 2.354156, timestamp '2020-11-13'); timestampadd ---------------------------- 2020-11-13 00:00:02.354156 (1 row) |
timestampdiff(field, timestamp1, timestamp2)
Description: Subtracts timestamp1 from timestamp2 and returns the difference in the unit of field. If the difference is negative, this function returns it normally. The field can be year, month, quarter, day, week, hour, minute, second, or microsecond.
Return type: bigint
Example:
1 2 3 4 5 |
SELECT timestampdiff(day, timestamp '2001-02-01', timestamp '2003-05-01 12:05:55'); timestampdiff --------------- 819 (1 row) |
timediff(timestamp | time | text, timestamp | time | text)
Description: Subtracts a date from another date. If the difference is negative, this function returns it normally. The types of the two input parameters must be the same. This function is supported by version 8.2.0 or later clusters.
Return type: time
Example:
1 2 3 4 5 |
SELECT timediff('2022-7-5 1:1:1', '2021-7-5 1:1:1'); timediff --------------- 8760:00:00 (1 row) |
clock_timestamp()
Description: Returns the current timestamp of the real-time clock.
Return type: timestamp with time zone
Example:
1 2 3 4 5 |
SELECT clock_timestamp(); clock_timestamp ------------------------------- 2017-09-01 16:57:36.636205+08 (1 row) |
current_date
Description: Returns the current date.
Return type: date
Example:
1 2 3 4 5 |
SELECT current_date; date ------------ 2017-09-01 (1 row) |
curdate()
Description: Returns the current date. This function is compatible with MySQL. This function is supported by version 8.2.0 or later clusters.
Return type: date
Example:
1 2 3 4 5 |
SELECT curdate(); curdate ------------ 2022-09-19 (1 row) |
current_time
Description: Returns the current time.
Return type: time with time zone
Example:
1 2 3 4 5 |
SELECT current_time; timetz -------------------- 16:58:07.086215+08 (1 row) |
curtime([fsp])
Description: Returns the current time. fsp is an optional parameter. Its value is an integer, which indicates the precision of the returned result. This function is supported by version 8.2.0 or later clusters.
Return type: time with time zone
Example:
1 2 3 4 5 6 7 8 9 10 |
SELECT curtime(); timetz -------------------- 16:58:07.086215+08 (1 row) SELECT curtime(2); timetz -------------------- 16:58:07.08+08 (1 row) |
current_timestamp
Description: Returns the current date and time.
Return type: timestamp with time zone
Example:
1 2 3 4 5 |
SELECT current_timestamp; pg_systimestamp ------------------------------ 2017-09-01 16:58:19.22173+08 (1 row) |
convert_tz(timestamp, from_tz, to_tz)
Description: Converts the datetime value from the time zone provided by from_tz (text) to the time zone provided by to_tz (text), and returns the converted datetime. This function is supported by version 8.2.0 or later clusters.
Return type: timestamp without time zone
Example:
1 2 3 4 5 6 7 8 9 10 |
SELECT convert_tz('2018-12-25 13:25:00', '+02:00', '+08:00'); convert_tz -------------------- 2018-12-25 19:25:00 (1 row) SELECT convert_tz('2000-02-28 23:00:00', 'GMT', 'MET'); convert_tz -------------------- 2000-02-29 00:00:00 (1 row) |
datediff(date1, date2)
Description: Returns the number of days between two given dates.
Return type: integer
Example:
1 2 3 4 5 |
select datediff(date '2020-11-13', date '2012-10-16'); datediff ---------- 2950 (1 row) |
date_part(text, timestamp)
Description: Obtains the precision specified by text.
This function is equivalent to extract(field from timestamp).
Return type: double precision
Example:
1 2 3 4 5 |
SELECT date_part('hour', timestamp '2001-02-16 20:38:40'); date_part ----------- 20 (1 row) |
date_part(text, interval)
Description: Obtains the precision specified by text. If the value is greater than 12, the remainder of the value divided by 12 is returned.
This function is equivalent to extract(field from timestamp).
Return type: double precision
Example:
1 2 3 4 5 |
SELECT date_part('month', interval '2 years 3 months'); date_part ----------- 3 (1 row) |
date_trunc(text, timestamp)
Description: Truncates a timestamp to the precision specified by text.
Return type: timestamp
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 |
SELECT date_trunc('hour', timestamp '2001-02-16 20:38:40'); date_trunc --------------------- 2001-02-16 20:00:00 (1 row) -- Obtain the last day of last year. SELECT date_trunc('day', date_trunc('year',CURRENT_DATE)+ '-1'); date_trunc ------------------------ 2022-12-31 00:00:00+00 (1 row) -- Obtain the first day of this year. SELECT date_trunc('year',CURRENT_DATE); date_trunc ------------------------ 2023-01-01 00:00:00+00 (1 row) -- Obtain the first day of last year. SELECT date_trunc('year',now() + '-1 year'); date_trunc ------------------------ 2022-01-01 00:00:00+00 (1 row) |
trunc(timestamp)
Description: Truncates a timestamp to day.
Return type: timestamp
Example:
1 2 3 4 |
SELECT trunc(timestamp '2001-02-16 20:38:40'); trunc --------------------- 2001-02-16 00:00:00 (1 row) |
extract(field from timestamp)
Description: Obtains the value of field with the specified precision. For details about the valid values of field, see EXTRACT.
Return type: double precision
Example:
1 2 3 4 5 |
SELECT extract(hour from timestamp '2001-02-16 20:38:40'); date_part ----------- 20 (1 row) |
extract(field from interval)
Description: Obtains the value of field with the specified precision. If the value is greater than 12, the remainder of the value divided by 12 is returned. For details about the valid values of field, see EXTRACT.
Return type: double precision
Example:
1 2 3 4 5 |
SELECT extract(month from interval '2 years 3 months'); date_part ----------- 3 (1 row) |
day(date)
Description: Returns the number of days in the month in which date is located. This function is the same as the dayofmonth function.
Value range: 1 to 31
Return type: integer
Example:
1 2 3 4 5 |
select day('2020-06-28'); day ----- 28 (1 row) |
dayofmonth(date)
Description: Returns the number of days in the month in which date is located.
Value range: 1 to 31
Return type: integer
Example:
1 2 3 4 5 |
select dayofmonth('2020-06-28'); dayofmonth ------------ 28 (1 row) |
dayofweek(date)
Description: Returns the week index corresponding to the given date, with Sunday as the start day of the week.
Value range: 1 to 7
Return type: integer
Example:
1 2 3 4 5 |
select dayofweek('2020-11-22'); dayofweek ----------- 1 (1 row) |
dayofyear(date)
Description: Returns the number of days of a given date in the current year.
Value range: 1 to 366
Return type: integer
Example:
1 2 3 4 5 |
select dayofyear('2020-02-29'); dayofyear ----------- 60 (1 row) |
hour(timestamp with time zone)
Description: Returns the hour value in a timestamp.
Return type: integer
Example:
1 2 3 4 5 |
SELECT hour(timestamptz '2018-12-13 12:11:15+06'); hour ------ 6 (1 row) |
isfinite(date)
Description: Checks whether a date is valid.
Return type: boolean
Example:
1 2 3 4 5 6 7 8 9 10 |
SELECT isfinite(date '2001-02-16'); isfinite ---------- t (1 row) SELECT isfinite(date 'infinity'); isfinite ---------- f (1 row) |
isfinite(timestamp)
Description: Checks whether a timestamp is valid.
Return type: boolean
Example:
1 2 3 4 5 6 7 8 9 10 |
SELECT isfinite(timestamp '2001-02-16 21:28:30'); isfinite ---------- t (1 row) SELECT isfinite(timestamp 'infinity'); isfinite ---------- f (1 row) |
isfinite(interval)
Description: Checks whether an interval is valid.
Return type: boolean
Example:
1 2 3 4 5 |
SELECT isfinite(interval '4 hours'); isfinite ---------- t (1 row) |
justify_days(interval)
Description: Adjusts an interval so that 30-day time periods are represented as months.
Return type: interval
Example:
1 2 3 4 5 |
SELECT justify_days(interval '35 days'); justify_days -------------- 1 mon 5 days (1 row) |
justify_hours(interval)
Description: Adjusts an interval so that 24-hour time periods are represented as days.
Return type: interval
Example:
1 2 3 4 5 |
SELECT JUSTIFY_HOURS(INTERVAL '27 HOURS'); justify_hours ---------------- 1 day 03:00:00 (1 row) |
justify_interval(interval)
Description: Adjusts an interval using justify_days and justify_hours.
Return type: interval
Example:
1 2 3 4 5 |
SELECT JUSTIFY_INTERVAL(INTERVAL '1 MON -1 HOUR'); justify_interval ------------------ 29 days 23:00:00 (1 row) |
localtime
Description: Returns the current time.
Return type: time
Example:
1 2 3 4 5 |
SELECT localtime AS RESULT; result ---------------- 16:05:55.664681 (1 row) |
localtimestamp
Description: Returns the current date and time.
Return type: timestamp
Example:
1 2 3 4 5 |
SELECT localtimestamp; timestamp ---------------------------- 2017-09-01 17:03:30.781902 (1 row) |
makedate(year, dayofyear)
Description: Returns a date value based on the given year and the number of days in a year.
Return type: date
Example:
1 2 3 4 5 |
select makedate(2020, 60); makedate ------------ 2020-02-29 (1 row) |
maketime(hour, minute, second)
Description: Returns a value of the time type based on the given hour, minute, and second. The value of the time type in GaussDB(DWS) ranges from 00:00:00 to 24:00:00. Therefore, this function is not available when the value of hour is greater than 24 or less than 0.
Return type: time
Example:
1 2 3 4 5 |
select maketime(12, 15, 30.12); maketime ------------- 12:15:30.12 (1 row) |
microsecond(timestamp with time zone)
Description: Returns the microsecond value in the time.
Return type: integer
Example:
1 2 3 4 5 |
SELECT microsecond(timestamptz '2018-12-13 12:11:15.123634+06'); microsecond ------------- 123634 (1 row) |
minute(timestamp with time zone)
Description: Returns the minute value in the time.
Return type: integer
Example:
1 2 3 4 5 |
SELECT minute(timestamptz '2018-12-13 12:11:15+06'); minute -------- 11 (1 row) |
month(date)
Description: Returns the month of a given datetime.
Return type: integer
Example:
1 2 3 4 5 |
select month('2020-11-30'); month ------- 11 (1 row) |
now([fsp])
Description: Returns the start time of the transaction. The parameter determines the microsecond output precision. The default value is 6.
Return type: timestamp with time zone
Example:
1 2 3 4 5 |
SELECT now(); now ------------------------------- 2017-09-01 17:03:42.549426+08 (1 row) |
1 2 3 4 5 |
SELECT now(3); now ---------------------------- 2021-09-08 10:59:00.427+08 (1 row) |
numtodsinterval(num, interval_unit)
Description: Converts a number to an interval. num is a numeric-typed number. interval_unit is a string in the following format: 'DAY' | 'HOUR' | 'MINUTE' | 'SECOND'
You can set the IntervalStyle parameter to oracle to be compatible with the interval output format of the function in the Oracle database.
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT numtodsinterval(100, 'HOUR'); numtodsinterval ----------------- 100:00:00 (1 row) SET intervalstyle = oracle; SET SELECT numtodsinterval(100, 'HOUR'); numtodsinterval ------------------------------- +000000004 04:00:00.000000000 (1 row) |
pg_sleep(seconds)
Description: Returns the delay of the server thread in seconds.
Return type: void
Example:
1 2 3 4 5 |
SELECT pg_sleep(10); pg_sleep ---------- (1 row) |
period_add(P, N)
Description: Returns the date of a given period plus N months.
Return type: integer
Example:
1 2 3 4 5 |
select period_add(200801, 2); period_add ------------ 200803 (1 row) |
period_diff(P1, P2)
Description: Returns the number of months between two given dates.
Return type: integer
1 2 3 4 5 |
select period_diff(200802, 200703); period_diff ------------- 11 (1 row) |
quarter(date)
Description: Returns the quarter to which the date belongs.
Return type: integer
Example:
1 2 3 4 5 |
SELECT quarter(date '2018-12-13'); quarter --------- 4 (1 row) |
second(timestamp with time zone)
Description: Returns the second in a timestamp.
Return type: integer
Example:
1 2 3 4 5 |
SELECT second(timestamptz '2018-12-13 12:11:15+06'); second -------- 15 (1 row) |
statement_timestamp()
Description: Returns the current date and time.
Return type: timestamp with time zone
Example:
1 2 3 4 5 |
SELECT statement_timestamp(); statement_timestamp ------------------------------- 2017-09-01 17:04:39.119267+08 (1 row) |
sysdate
Description: Returns the current date and time.
Return type: timestamp
Example:
1 2 3 4 5 |
SELECT sysdate; sysdate --------------------- 2017-09-01 17:04:49 (1 row) |
timeofday()
Description: Current date and time (like clock_timestamp, but returned as a text string)
Return type: text
Example:
1 2 3 4 5 |
SELECT timeofday(); timeofday ------------------------------------- Fri Sep 01 17:05:01.167506 2017 CST (1 row) |
transaction_timestamp()
Description: Returns the current date and time. This function is equivalent to current_timestamp.
Return type: timestamp with time zone
Example:
1 2 3 4 5 |
SELECT transaction_timestamp(); transaction_timestamp ------------------------------- 2017-09-01 17:05:13.534454+08 (1 row) |
from_unixtime(unix_timestamp[,format])
Description: Converts a Unix timestamp to the datetime type when the format string is set to the default value. If the format string is specified, this function converts the Unix timestamp to a string of a specified format.
Return type: timestamp (default format string) or text (specified format string)
Example:
1 2 3 4 5 6 7 8 9 10 |
SELECT from_unixtime(875996580); from_unixtime --------------------- 1997-10-04 20:23:00 (1 row) SELECT from_unixtime(875996580, '%Y %D %M %h:%i:%s'); from_unixtime --------------------------- 1997 4th October 08:23:00 (1 row) |
unix_timestamp([timestamp with time zone])
Description: Returns the number of seconds from '1970-01-01 00:00:00'UTC to the timestamp specified by the input parameter. If no parameter is input, this function obtains the number of seconds from '1970-01-01 00:00:00'UTC to the current time.
Return type: bigint (no parameter is input) or numeric (parameter is input)
Example:
1 2 3 4 5 |
SELECT unix_timestamp(); unix_timestamp ---------------- 1693906219 (1 row) |
1 2 3 4 5 |
SELECT unix_timestamp('2018-09-08 12:11:13+06'); unix_timestamp ---------------- 1536387073.000000 (1 row) |
add_months(d,n)
Description: Adds a specified number of months to a date.
Return type: timestamp
Example:
1 2 3 4 5 |
SELECT add_months(to_date('2017-5-29', 'yyyy-mm-dd'), 11) FROM dual; add_months --------------------- 2018-04-29 00:00:00 (1 row) |
last_day(d)
Description: Returns the last day of the month of a specified date.
- In the ORA- or TD-compatible mode, a timestamp is returned.
- In the MySQL-compatible mode, a date is returned.
Example:
1 2 3 4 5 |
select last_day(to_date('2017-01-01', 'YYYY-MM-DD')) AS cal_result; cal_result --------------------- 2017-01-31 00:00:00 (1 row) |
next_day(x,y)
Description: Returns a specified day of the next week of a given date.
- In the ORA- or TD-compatible mode, a timestamp is returned.
- In the MySQL-compatible mode, a date is returned.
Example:
1 2 3 4 5 |
select next_day(timestamp '2017-05-25 00:00:00','Sunday')AS cal_result; cal_result --------------------- 2017-05-28 00:00:00 (1 row) |
from_days(days)
Description: Returns the corresponding date value based on the given number of days.
Return type: date
Example:
1 2 3 4 5 |
select from_days(730669); from_days ------------ 2000-07-03 (1 row) |
to_days(timestamp)
Description: Returns the number of days from the first day of year 0 to a specified date.
Return type: integer
Example:
1 2 3 4 5 |
SELECT to_days(timestamp '2008-10-07'); to_days --------- 733687 (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