September 26, 2023
The following page shows the most commonly used PostgreSQL date functions that allow you to manipulate date and time values effectively.
| Function | Return Type | Description |
|---|---|---|
| AGE | INTERVAL | Calculate ages between two timestamps and returns a “symbolic” result which uses years and months |
| AGE | INTERVAL | Calculate ages between current date (at midnight) and a timestamp and returns a “symbolic” result which uses years and months |
| CLOCK_TIMESTAMP | TIMESTAMPTZ | Return the current date and time which changes during statement execution |
| CURRENT_DATE | DATE | Return the current date |
| CURRENT_TIME | TIMESTAMPTZ | Return the current time |
| CURRENT_TIMESTAMP | TIMESTAMPTZ | Return the current date and time with time zone at which the current transaction starts |
| DATE_PART | DOUBLE PRECISION | Get a field of a timestamp or an interval e.g., year, month, day, etc. |
| DATE_TRUNC | TIMESTAMP | Return a timestamp truncated to a specified precision |
| EXTRACT | DOUBLE PRECISION | Same as DATE_PART() function |
| ISFINITE | BOOLEAN | Check if a date, a timestamp, or an interval is finite or not (not +/-infinity) |
| JUSTIFY_DAYS | INTERVAL | Adjust interval so 30-day time periods are represented as months |
| JUSTIFY_HOURS | INTERVAL | Adjust interval so 24-hour time periods are represented as days |
| JUSTIFY_INTERVAL | INTERVAL | Adjust interval using justify_days and justify_hours, with additional sign adjustments |
| LOCALTIME | TIME | Return the time at which the current transaction start |
| LOCALTIMESTAMP | TIMESTAMP | Return the date and time at which the current transaction start |
| NOW | TIMESTAMPTZ | Return the date and time with time zone at which the current transaction start |
| STATEMENT_TIMESTAMP | TIMESTAMPTZ | Return the current date and time at which the current statement executes |
| TIMEOFDAY | TEXT | Return the current date and time, like clock_timestamp, as a text string |
| TRANSACTION_TIMESTAMP | TIMESTAMPTZ | Same as NOW() function |
| TO_DATE | DATE | Convert a string to a date |
| TO_TIMESTAMP | TIMESTAMPTZ | Convert a string to a timestamp |