Menu

PostgreSQL Date Functions: now, interval, date_trunc

Work with dates in PostgreSQL: now() and current_date, adding and subtracting intervals, date_trunc for grouping, extract and age, and formatting with to_char.

This page includes runnable editors. Edit, run, and see output instantly.

now() returns the current date and time and current_date returns today's date. Shift either with an interval: now() - interval '30 days'. date_trunc('month', ts) rounds a timestamp down to the start of its month, extract(year FROM ts) pulls out one field, and to_char(ts, 'YYYY-MM-DD') formats it as text.

Orders 1 and 2 fall inside the last 30 days. The dates shown depend on when you press Run, because the rows are inserted relative to now().

Get the current date and time

FunctionReturnsType
now(), current_timestampDate and time the transaction startedtimestamptz
current_dateToday's datedate
localtimestampDate and time without the time zonetimestamp
clock_timestamp()The real time at the moment of the calltimestamptz

now() stays fixed for the whole transaction, so every row inserted in one transaction gets the same timestamp. Use clock_timestamp() to time steps inside a long transaction. The results depend on the session's TimeZone setting (see timestamp vs timestamptz).

Add and subtract dates

This returns 2026-12-17, 38, 2026-02-28 00:00:00 and 2 days 08:30:00. The rules:

  • date + integer and date - integer move by days and return a date.
  • date - date returns the number of days as an integer.
  • date + interval returns a timestamp; cast it back with ::date if you want a date.
  • timestamp - timestamp returns an interval.
  • Adding a month to January 31 gives February 28 (or 29): PostgreSQL clamps to the last day of the shorter month.

Write intervals as interval '2 hours 15 minutes' or interval '1 year'. For a number of days from a column, use interval '1 day' * n or make_interval(days => n).

Group by month, week or day with date_trunc

date_trunc(unit, value) cuts a timestamp down to the start of its year, quarter, month, week (starting Monday), day, hour or minute. Group by it to count rows per period:

September has 2 orders, October and November 1 each. Months with no orders are missing from the result; generate_series fills them in with zeros.

date_trunc on a date returns a timestamptz, so add ::date when you need a date back. For buckets of any size, such as 15 minutes, use date_bin (PostgreSQL 14+).

Extract a field and calculate age

This returns 2026, 2 (Tuesday; Sunday is 0), 7200, 36 years 3 mons 28 days and 36. Other fields include month, day, hour, quarter, week (ISO week number), isodow (Monday is 1) and epoch (seconds since 1970 for a timestamp).

Since PostgreSQL 14, extract returns numeric; the older date_part('year', d) returns double precision.

age(a, b) gives the difference in years, months and days, which is what you want for a person's age. age(ts) with one argument measures from current_date.

Format dates with to_char and parse with to_date

The first two return 2026-11-17 14:05:09 and Tuesday, 17 Nov 2026. to_date and to_timestamp read text in the pattern you give. The common pattern codes:

CodeMeaningCodeMeaning
YYYY4-digit yearHH24Hour 00 to 23
MMMonth 01 to 12HH12, AM12-hour clock
DDDay of monthMIMinutes
Mon, MonthMonth nameSSSeconds
Dy, DayWeekday nameFMNo padding (prefix)

Without FM, Day and Month are padded with spaces to 9 characters. MM is the month and MI the minutes, an easy pair to mix up. to_timestamp(1763337600), with a single number, converts a Unix epoch value.

Frequently Asked Questions

How do I get the number of days between two dates in PostgreSQL?

Subtract them as date values: date '2026-12-25' - date '2026-11-17' returns the integer 38. Subtracting two timestamps returns an interval instead, such as 2 days 08:30:00.

What is the difference between now() and current_timestamp?

None. Both return the start time of the current transaction as a timestamptz. clock_timestamp() returns the actual time at the moment it is called, which changes even within one statement.

How do I get yesterday's date in PostgreSQL?

current_date - 1. Adding or subtracting an integer to a date moves it by that many days and keeps the type date.

How do I get the last day of the month in PostgreSQL?

Truncate to the month, add a month and step back a day: (date_trunc('month', d) + interval '1 month - 1 day')::date.

Coddy programming languages illustration

Learn to code with Coddy

Get started