YouTip

### Date/Time Operators The following table demonstrates the behavior of basic arithmetic operators (+, *, etc.): | Operator | Example | Result | | --- | --- | --- | | + | date '2001-09-28' + integer '7' | date '2001-10-05' | | + | date '2001-09-28' + interval '1 hour' | timestamp '2001-09-28 01:00:00' | | + | date '2001-09-28' + time '03:00' | timestamp '2001-09-28 03:00:00' | | + | interval '1 day' + interval '1 hour' | interval '1 day 01:00:00' | | + | timestamp '2001-09-28 01:00' + interval '23 hours' | timestamp '2001-09-29 00:00:00' | | + | time '01:00' + interval '3 hours' | time '04:00:00' | | - | - interval '23 hours' | interval '-23:00:00' | | - | 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 23:00:00' | | - | time '05:00' - time '03:00' | interval '02:00:00' | | - | time '05:00' - interval '2 hours' | time '03:00:00' | | - | timestamp '2001-09-28 23:00' - interval '23 hours' | timestamp '2001-09-28 00:00:00' | | - | interval '1 day' - interval '1 hour' | interval '1 day -01:00:00' | | - | timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00' | interval '1 day 15:00:00' | | * | 900 * interval '1 second' | interval '00:15:00' | | * | 21 * interval '1 day' | interval '21 days' | | * | double precision '3.5' * interval '1 hour' | interval '03:30:00' | | / | interval '1 hour' / double precision '1.5' | interval '00:40:00' | ### Date/Time Functions | Function | Return Type | Description | Example | Result | | --- | --- | --- | --- | --- | | `age(timestamp, timestamp)` | interval | "Symbolic" result of subtracting arguments, uses years and months, not just days | age(timestamp '2001-04-10', timestamp '1957-06-13') | 43 years 9 mons 27 days | | `age(timestamp)` | interval | Result of subtracting argument from `current_date` (at midnight) | age(timestamp '1957-06-13') | 43 years 8 mons 3 days | | `clock_timestamp()` | timestamp with time zone | Current timestamp of real-time clock (changes during statement execution) | | | | `current_date` | date | Current date; | | | | `current_time` | time with time zone | Current time of day; | | | | `current_timestamp` | timestamp with time zone | Timestamp of current transaction start; | | | | `date_part(text, timestamp)` | double precision | Get subfield (equivalent to `extract`); | date_part('hour', timestamp '2001-02-16 20:38:40') | 20 | | `date_part(text, interval)` | double precision | Get subfield (equivalent to `extract`); | date_part('month', interval '2 years 3 months') | 3 | | `date_trunc(text, timestamp)` | timestamp | Truncate to specified precision; | date_trunc('hour', timestamp '2001-02-16 20:38:40') | 2001-02-16 20:00:00 | | `date_trunc(text, interval)` | interval | Truncate to specified precision | date_trunc('hour', interval '2 days 3 hours 40 minutes') | 2 days 03:00:00 | | `extract`(field from timestamp) | double precision | Get subfield; | extract(hour from timestamp '2001-02-16 20:38:40') | 20 | | `extract`(field from interval) | double precision | Get subfield; | extract(month from interval '2 years 3 months') | 3 | | `isfinite(date)` | boolean | Test if date is finite (not +/-infinity) | isfinite(date '2001-02-16') | true | | `isfinite(timestamp)` | boolean | Test if timestamp is finite (not +/-infinity) | isfinite(timestamp '2001-02-16 21:28:30') | true | | `isfinite(interval)` | boolean | Test if interval is finite | isfinite(interval '4 hours') | true | | `justify_days(interval)` | interval | Adjust interval assuming 30 days per month | justify_days(interval '35 days') | 1 mon 5 days | | `justify_hours(interval)` | interval | Adjust interval assuming 24 hours per day | justify_hours(interval '27 hours') | 1 day 03:00:00 | | `justify_interval(interval)` | interval | Adjust interval using `justify_days` and `justify_hours`, also adjust sign | justify_interval(interval '1 mon -1 hour') | 29 days 23:00:00 | | `localtime` | time | Current time of day; | | | | `localtimestamp` | timestamp | Timestamp of current transaction start; | | | | ``` make_date(year int, month int, day int) ``` | date | Create date from year, month, and day fields | make_date(2013, 7, 15) | 2013-07-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.0) ``` | interval | Create interval from year,