Date & Time Functions

Dates and timestamps show up in almost every real-world schema, and PostgreSQL has a thorough set of functions for reading the current time, pulling fields out of a date, bucketing dates for reporting, doing date arithmetic, and formatting dates as readable strings.

Getting the current date and time

Function	Returns
`CURRENT_DATE`	Today's date (no time component)
`CURRENT_TIMESTAMP` / `NOW()`	Current date and time, with time zone

SQL

SELECT CURRENT_DATE;         -- 2024-03-15
SELECT CURRENT_TIMESTAMP;    -- 2024-03-15 14:32:07.128412+00
SELECT NOW();                 -- same as CURRENT_TIMESTAMP

EXTRACT() — pulling out a field

EXTRACT(field FROM date) pulls a specific component — year, month, day, hour, day of week, and more — out of a date or timestamp value as a number.

SQL

SELECT
  EXTRACT(YEAR FROM order_date)  AS order_year,
  EXTRACT(MONTH FROM order_date) AS order_month,
  EXTRACT(DOW FROM order_date)   AS day_of_week  -- 0 = Sunday
FROM orders;

order_year | order_month | day_of_week
-----------+-------------+------------
      2024 |           3 |           5

DATE_TRUNC() — bucketing dates for reporting

DATE_TRUNC('unit', date) rounds a timestamp down to the start of the given unit — the month, the week, the day, the hour, and so on. This is one of the most useful functions for reporting queries: it turns a column of scattered timestamps into clean buckets you can GROUP BY.

Monthly revenue report

SQL

SELECT
  DATE_TRUNC('month', order_date) AS month,
  SUM(total) AS revenue
FROM orders
GROUP BY month
ORDER BY month;

month               | revenue
--------------------+---------
2024-01-01 00:00:00 | 15230.00
2024-02-01 00:00:00 | 18940.00
2024-03-01 00:00:00 | 12100.00

Every order in March, regardless of which day it happened on, gets truncated down to 2024-03-01 00:00:00 — giving you one clean group per month instead of one per exact timestamp.

Date arithmetic with INTERVAL

INTERVAL represents a span of time and can be added to or subtracted from any date or timestamp directly with +/-.

SQL

SELECT
  order_date,
  order_date + INTERVAL '1 day'    AS next_day,
  order_date + INTERVAL '3 months' AS three_months_later,
  order_date - INTERVAL '1 week'   AS one_week_before
FROM orders;

Orders placed in the last 30 days

SQL

SELECT *
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

AGE() — human-readable interval between two dates

SQL

SELECT AGE('2024-03-15'::date, '1990-07-22'::date);
-- 33 years 7 mons 21 days

SELECT AGE(hire_date) AS tenure  -- AGE with one argument compares to CURRENT_DATE
FROM employees;

Formatting dates with TO_CHAR()

TO_CHAR(date, format) formats a date or timestamp as a string using a pattern — useful anywhere you want a date displayed in a specific, human-friendly form rather than PostgreSQL's default output.

SQL

SELECT
  TO_CHAR(order_date, 'YYYY-MM-DD')      AS iso_format,
  TO_CHAR(order_date, 'Mon DD, YYYY')    AS readable_format,
  TO_CHAR(order_date, 'Day, DD Month')   AS full_format
FROM orders;

iso_format | readable_format | full_format
-----------+-----------------+---------------------------
2024-03-15 | Mar 15, 2024    | Friday   , 15 March

Pattern	Meaning
`YYYY`	Four-digit year
`MM`	Two-digit month
`DD`	Two-digit day
`Mon` / `Month`	Abbreviated / full month name
`Day`	Full day-of-week name
`HH24:MI:SS`	24-hour time with minutes and seconds

Note

TO_CHAR() uses its own set of format patterns — distinct from strftime-style patterns you may know from other languages — so it's worth keeping a reference to the pattern table handy the first few times you use it.

Tip

DATE_TRUNC() and GENERATE_SERIES() pair well for reports that must show every period even when there's no data for it — generate the full list of months, then LEFT JOIN your DATE_TRUNC('month', ...) aggregation onto it.

CURRENT_DATE / NOW() / CURRENT_TIMESTAMP give the current date and time.
EXTRACT(field FROM date) pulls out a single component like year or month as a number.
DATE_TRUNC('unit', date) rounds down to the start of a time bucket — the core tool for grouping by month/week/day.
INTERVAL supports direct date arithmetic; AGE() computes a human-readable span between two dates.
TO_CHAR(date, format) formats dates as strings using pattern codes.