Postgresql Date and Time Functions
From w3cyberlearnings
This function uses to manipulate date and time.
| # | Function | Return Type | Example | Result | Description |
|---|---|---|---|---|---|
| 1 | age | interval | age(timestamp '1957-06-13') | 43 years 8 mons 3 days | Subtract from current_date (at midnight) |
| 2 | clock_timestamp | timestamp with time zone | Current date and time (changes during statement execution) | ||
| 3 | current_date | date | Current date | ||
| 4 | current_time | time with time zone | Current time of day | ||
| 5 | current_timestamp | timestamp with time zone | Current date and time (start of current transaction) | ||
| 6 | date_part | double precision | date_part('hour', timestamp '2001-02-16 20:38:40') | 20 | Get subfield (equivalent to extract) |
| 7 | date_trunc | timestamp | date_trunc('hour', timestamp '2001-02-16 20:38:40') | 2001-02-16 20:00:00 | Truncate to specified precision |
| 8 | extract | double precision | extract(month from interval '2 years 3 months') | 3 | Get subfield |
| 9 | isfinite | boolean | isfinite(date '2001-02-16') | true | Test for finite date,timestamp or interval (not +/-infinity) |
| 10 | justify_days | interval | justify_days(interval '35 days') | 1 mon 5 days | Adjust interval so 30-day time periods are represented as months |
| 11 | justify_hours | interval | justify_hours(interval '27 hours') | 1 day 03:00:00 | Adjust interval so 24-hour time periods are represented as days |
| 12 | justify_interval | interval | justify_interval(interval '1 mon -1 hour') | 29 days 23:00:00 | Adjust interval using justify_days and justify_hours, with additional sign adjustments |
| 13 | localtime | time | Current time of day | ||
| 14 | localtimestamp | timestamp | Current date and time (start of current transaction) | ||
| 15 | now | timestamp with time zone | Current date and time (start of current transaction) | ||
| 16 | statement_timestamp | timestamp with time zone | Current date and time (start of current statement) | ||
| 17 | timeofday | text | Current date and time (like clock_timestamp, but as a text string) | ||
| 18 | transaction_timestamp | timestamp with time zone | Current date and time (start of current transaction) |
