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 |