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
| Function | Returns | Type |
|---|---|---|
now(), current_timestamp | Date and time the transaction started | timestamptz |
current_date | Today's date | date |
localtimestamp | Date and time without the time zone | timestamp |
clock_timestamp() | The real time at the moment of the call | timestamptz |
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 + integeranddate - integermove by days and return adate.date - datereturns the number of days as an integer.date + intervalreturns atimestamp; cast it back with::dateif you want a date.timestamp - timestampreturns aninterval.- 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:
| Code | Meaning | Code | Meaning |
|---|---|---|---|
YYYY | 4-digit year | HH24 | Hour 00 to 23 |
MM | Month 01 to 12 | HH12, AM | 12-hour clock |
DD | Day of month | MI | Minutes |
Mon, Month | Month name | SS | Seconds |
Dy, Day | Weekday name | FM | No 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.