This page provides standard PostgreSQL date functions to help you effectively handle date, time, and timestamp data.
Section 1. Getting the Current Date & Time Functions #
This section introduces various functions to get the current date, time, and timestamp.
- CURRENT_DATE – Returns the current date of the PostgreSQL server at the default time zone.
- CURRENT_TIMESTAMP – Returns the current timestamp with a time zone.
- NOW() – Returns the current date and time with the time zone.
- LOCALTIMESTAMP – Returns the current local timestamp.
- STATEMENT_TIMESTAMP() – Returns the start time of the current SQL statement as a timestamp with the time zone.
- CLOCK_TIMESTAMP() – Returns the actual current time as a timestamp with the time zone.
- TRANSACTION_TIMESTAMP() – Returns the start time of a transaction.
Section 2. Extracting Date and Time fields #
This section shows how to extract a date and time field from a timestamp value. For example, you can extract a year from a date.
- EXTRACT() – Extracts a specific date field from a date.
- DATE_PART() – Extracts a specific date/time field from a date/time value with a double precision.
Section 3. Working with intervals #
This section guides you on how to handle interval values effectively.
- JUSTIFY_DAYS() – Normalizes an interval by converting days that exceed 30 into months.
- JUSTIFY_HOURS() – Adjusts an interval by converting multiples of 24 hour to days.
- JUSTIFY_INTERVAL() – Changes an interval by converting 30-day periods to months and 24-hour periods to days with additional sign adjustments.
- AGE() – Returns the difference between two timestamps as an interval.
- MAKE_INTERVAL() – Creates an interval from arguments.
Section 5. Conversion Functions #
This section teaches you to use functions to convert a string to a date and timestamp.
- TO_DATE – Converts a string to date based on a format string such as ‘YYYY-MM-DD’.
- TO_TIMESTAMP – Converts a string to a TIMESTAMP WITH A TIME ZONE based on a format.
Was this tutorial helpful ?
