5.43.1. Date / time operator ¶
The following table shows the behavior of basic arithmetic operators (+, * , etc.):
Operator | Examples | Result |
|---|---|---|
| Date ‘2001-09-28’ + integer’ 7’ | Date ‘2001-10-05’ |
| Date ‘2001-09-28’ + interval’1 hour’ | Timestamp ‘2001-09-28 01GRV 0000’ |
| Date ‘2001-09-28’ + time’ 03purl 00’ | Timestamp ‘2001-09-28 03GRV 0000’ |
| Interval’1 day’ + interval’1 hour’ | Interval’1 day 01purl 0000’ |
| Timestamp ‘2001-09-28 01 hours’ hours’ + interval’ 23 | Timestamp ‘2001-09-29 0000Rule 0000’ |
| Time ‘01VOUR 00’ + interval’ 3 hours’ | Time ‘04VOUR 00UR 00’ |
|
| Interval’- 23PUR 0000’ |
| Date ‘2001-10-01’-date ‘2001-09-28’ | Integer’3’ (days) |
| Date ‘2001-10-01’-integer’7’ | Date ‘2001-09-24’ |
| Date ‘2001-09-28’-interval’1 hour’ | Timestamp ‘2001-09-27 2300RV 00’ |
| Time ‘05VR 00’-time’ 03VR 00’ | Interval ‘02VOUR 00UR 00’ |
| Time ‘05VOV 00’-interval’ 2 hours’ | Time ‘03VOULAR 0000’ |
| Timestamp ‘2001-09-28 2314 00’-interval’ 23 hours’ | Timestamp ‘2001-09-28 0000RV 0000’ |
| Interval’1 day’-interval’1 hour’ | Interval’1 day-01PUR 0000’ |
| Timestamp ‘2001-09-29 03VR 00’-timestamp’ 2001-09-27 12VR 00’ | Interval’1 day 15lv 0000’ |
| 900 * interval’1 second’ | Interval ‘00Rose 1500’ |
| 21 * interval’1 day’ | Interval’21 days’ |
| Double precision ‘3.5’ * interval’1 hour’ | Interval ‘03PUR 30PUR 00’ |
| Interval’1 hour’ / double precision ‘1.5’ | Interval ‘00Rose 40Rose 00’ |
5.43.2. Date / time function ¶
Function | Return type | Description | Examples | Result |
|---|---|---|---|---|
Age (timestamp, timestamp) | Interval | The result of “symbolization” after subtracting parameters, use year and month, not just days | Age (timestamp ‘2001-04-10, timestamp’ 1957-06-13’) | 43 years 9 mons 27 days |
Age (timestamp) | Interval | 从current_date减去参数后的结果（在午夜） | Age (timestamp ‘1957-06-13’) | 43 years 8 mons 3 days |
Clock_timestamp () | Timestamp with time zone | The current timestamp of the real-time clock (changes when the statement is executed) | ||
current_date | Date | Current date | ||
current_time | Time with time zone | Time of the day | ||
current_timestamp | Timestamp with time zone | Timestamp at the start of the current transaction | ||
Date_part (text, timestamp) | Double precision | Get subdomain (equivalent to extract) | Date_part (‘hour’, timestamp’ 2001-02-16 20 38 40’) | 20 |
Date_part (text, interval) | Double precision | Get subdomain (equivalent to extract) | Date_part (‘month’, interval’ 2 years 3 months’) | 3 |
Date_trunc (text, timestamp) | Timestamp | Truncated to a specified precision | Date_trunc (‘hour’, timestamp’ 2001-02-16 20 38 40’) | 2001-2-16 20:00 |
Date_trunc (text, interval) | Interval | Intercept the specified precision | Date_trunc (‘hour’, interval’ 2 days 3 hours 40 minutes’) | 2 days 03:00:00 |
Extract (field from timestamp) | Double precision | Get subdomain | Extract (hour from timestamp ‘2001-02-16 20-38-40’) | 20 |
Extract (field from interval) | Double precision | Get subdomain | Extract (month from interval’2 years 3 months’) | 3 |
Isfinite (date) | Boolean | Test whether it is a finite date (not + /-infinite) | Isfinite (date ‘2001-02-16’) | TRUE |
Isfinite (timestamp) | Boolean | Test whether it is a finite timestamp (not + /-infinite) | Isfinite (timestamp ‘2001-02-16 21) | TRUE |
Isfinite (interval) | Boolean | Test whether there is a finite time interval | Isfinite (interval’4 hours’) | TRUE |
Justify_days (interval) | Interval | Adjust the time interval according to 30 days per month | Justify_days (interval’35 days’) | 1 mon 5 days |
Justify_hours (interval) | Interval | Adjust the interval according to 24 hours a day | Justify_hours (interval’27 hours’) | 1 day 03:00:00 |
Justify_interval (interval) | Interval | 使用justify_days和justify_hours调整时间间隔的同时进行正负号调整 | Justify_interval (interval’1 mon-1 hour’) | 29 days 23:00:00 |
Localtime | Time | Time of the day | ||
Localtimestamp | Timestamp | Timestamp at the start of the current transaction | ||
Make_date (year int, month int, day int) | Date | Create a date for the year, month, and day fields | Make_date (2013, 7, 15) | 2013-7-15 |
Make_interval (years int DEFAULT 0, months int DEFAULT 0, weeks int DEFAULT 0, days int DEFAULT 0, hours int DEFAULT 0, mins int DEFAULT 0, secs double precision DEFAULT 0) | Interval | Create intervals from the year, month, week, day, hour, minute, and second fields | Make_interval (days: = 10) | 10 days |
Make_time (hour int, min int, sec double precision) | Time | Create a time from the hour, minute, and second fields | Make_time (8,15,23.5) | 15:23.5 |
Make_timestamp (year int, month int, day int, hour int, min int, sec double precision) | Timestamp | Create a timestamp from the year, month, day, hour, minute, and second fields | Make_timestamp (2013, 7, 15, 8, 15, 23.5) | 15:23.5 |
Make_timestamptz (year int, month int, day int, hour int, min int, sec double precision [ timezone text ] ) | Timestamp with time zone | Create a timestamp with a time zone from the year, month, day, hour, minute, and second fields. The current time zone is used when no timezone is specified. | Make_timestamptz (2013, 7, 15, 8, 15, 23.5) | 2013-07-15 0815 purge 15 purl 23.5mm 01 |
Now () | Timestamp with time zone | Timestamp at the start of the current transaction | ||
Statement_timestamp () | Timestamp with time zone | Current timestamp of the real-time clock | ||
Timeofday () | Text | 与clock_timestamp相同，但结果是一个text 字符串； | ||
Transaction_timestamp () | Timestamp with time zone | Timestamp at the start of the current transaction |