PostgreSQL Date Functions

The following page shows the most commonly used PostgreSQL date functions that allow you to effectively manipulate date and time values.

Section 1. Getting the current date and time

This section shows you various functions for getting the current date, current date and time, current timestamp, without or without timezone.

• CURRENT_DATE – Return the current date.
• CURRENT_TIME – Return the current time without date parts.
• CURRENT_TIMESTAMP – Return the current date and time with the time zone at which the current transaction starts.
• CLOCK_TIMESTAMP – Return the current timestamp which changes during statement execution.
• STATEMENT_TIMESTAMP – Return the current timestamp and time of the current statement.
• NOW – Return the date and time with the time zone at which the current transaction starts.
• TRANSACTION_TIMESTAMP – Same as the CURRENT_TIMESTAMP or NOW() function.
• LOCALTIME – Return the time at which the current transaction starts.
• LOCALTIMESTAMP – Return the date and time at which the current transaction starts.

Section 2. Extracting date and time components

This section provides you with functions for extracting date and time components

• DATE_PART – Get a field of a timestamp or an interval e.g., year, month, day, etc.
• EXTRACT – Same as DATE_PART() function.

Section 3. Converting to date and time

This section introduces the functions that convert a string to a date and timestamp.

• TO_DATE – Convert a string to a date.
• TO_TIMESTAMP – Convert a string to a timestamp.
• MAKE_DATE – Create a date from year, month, and day.
• MAKE_TIME – Create a time from hour, minute, and second.

Section 4. Handling intervals

This section covers the function that handles intervals such as calculating age based on intervals and justifying intervals for enhanced readability.

• AGE – Calculate the age and return an interval.
• JUSTIFY_DAYS – Adjust 30-day intervals as months.
• JUSTIFY_HOURS – Adjust 24-hour intervals as days
• JUSTIFY_INTERVAL – Adjust interval using justify_days and justify_hours functions, with additional sign adjustments.
• MAKE_INTERVAL – Create an interval from the provided interval’s components.

Section 5. Operators

This section shows you how to use the date and time operators.

• AT TIME ZONE – Convert a timestamp or a timestamp with time zone to a different time zone.

Section 6. Utility functions

This section shows you various date and time utility functions.

• DATE_TRUNC – Truncate a date.
• ISFINITE – Check if a date, a timestamp, or an interval is finite or not (not +/-infinity).
• TIMEOFDAY – Return the current date and time, like clock_timestamp, as a text string).
• PG_SLEEP – Pause the execution of a statement for some seconds.