Dates and times can be complicated. [[Postgres]] has a number of utilities that help store dates and times correctly and calculate time intervals or age. [Read the docs](https://www.postgresql.org/docs/current/functions-datetime.html) for full details.
---
## quick reference
```SQL
-- Format a date
TO_CHAR(my_date, 'YYYY-MM')
-- Start of the current month
DATE_TRUNC('month', CURRENT_DATE)
-- All rows from this year
EXTRACT(YEAR FROM my_date) = EXTRACT(YEAR FROM CURRENT_DATE)
-- Days between two dates
my_date_2 - my_date_1
-- Add a year
my_date + INTERVAL '1 year'
```
### tips
- Use TIMESTAMPTZ when dealing with multiple time zones.
- Use [[AGE]]() for human-readable differences, subtraction for numeric day counts.
- [[DATE_TRUNC]]() is great for grouping by month, week, or year in reports.
- [[TO_CHAR]]() for display formatting.
- `::DATE` or `DATE()` to cast a string to date in a query
---
## data types
| type | description | example |
| ------------- | ------------------------- | -------------------------- |
| `DATE` | Calendar date | `'2025-10-28'` |
| `TIME` | Time of day (no timezone) | `'09:42:00'` |
| `TIMESTAMP` | Date + time (no timezone) | `'2025-10-28 09:42:00'` |
| `TIMESTAMPTZ` | Date + time with timezone | `'2025-10-28 09:42:00-06'` |
| `INTERVAL` | Time span | `'2 days 3 hours'` |
## dates in DML
```SQL
CREATE TABLE dates (
date DATE,
timestamp TIMESTAMP,
time_with_timezone TIMESTAMPTZ,
time TIME,
interval INTERVAL -- unique to Postgres
);
INSERT INTO dates (
date,
timestamp,
timestamp_with_timezone,
time,
interval
) VALUES (
DATE '2022-11-02',
'1999-01-08:04:05:06',
'1999-01-08:04:05:06',
'04:05:06')
'0000-00-07:00:00:00';
```
## current date & time
Postgres stores date/time values as [[UTC]]. If you run `SELECT NOW()` you will see the time is UTC, not local time. To change this, see timezones below.
| function | description | example output |
| ----------------------------- | ---------------------- | ------------------------------ |
| `CURRENT_DATE` | Date only | `2025-10-28` |
| `CURRENT_TIME` | Time only | `09:42:31.12345-06` |
| `CURRENT_TIMESTAMP` / `NOW()` | Date + time | `2025-10-28 09:42:31.12345-06` |
| `CLOCK_TIMESTAMP()` | Actual wall-clock time | |
| `TRANSACTION_TIMESTAMP()` | Fixed per transaction | |
## timezones
| Function | Description | Example |
| ---------------------- | --------------------- | --------------------------------------------- |
| `SET TIME ZONE 'UTC';` | Set session time zone | |
| `AT TIME ZONE` | Convert between zones | `SELECT NOW() AT TIME ZONE 'UTC';` |
| `TIMEZONE(tz, ts)` | Explicit conversion | `SELECT TIMEZONE('America/New_York', NOW());` |
## extracting & formatting
Use [[EXTRACT]] to get a day, week, month, year, decade or century from a date. An alternative is [[DATE_PART]] or [[TO_CHAR]]. [[DATE_TRUNC]] is great for grouping by month, week, or year in reports.
| function | description | example | result |
| ---------------------------- | ------------------ | ---------------------------------------------------- | ------ |
| `EXTRACT(field FROM source)` | Get part of a date | `EXTRACT(YEAR FROM NOW())` | `2025` |
| `DATE_PART(field, source)` | Same as `EXTRACT` | `DATE_PART('month', NOW())` | `10` |
| `DATE_TRUNC(field, source)` | Truncate to unit | `DATE_TRUNC('month', NOW())` → `2025-10-01 00:00:00` | |
| `TO_CHAR(timestamp, format)` | Format as text | `TO_CHAR(NOW(), 'YYYY-MM-DD')` → `'2025-10-28'` | |
**Common format patterns:**
`YYYY` year · `MM` month · `DD` day · `HH24:MI:SS` time
## arithmetic
See the [docs](https://www.postgresql.org/docs/current/datatype-datetime.html) for more detail.
- Use [[INTERVAL]] followed by a duration to add or
- [[AGE]]
```sql
-- Add or subtract intervals
SELECT CURRENT_DATE + INTERVAL '7 days';
SELECT CURRENT_DATE - INTERVAL '1 month';
-- Difference between dates
SELECT AGE('2025-10-28', '2020-10-28');
SELECT '2025-10-28'::DATE - '2025-10-20'::DATE; -- 8
-- Multiply intervals
SELECT INTERVAL '1 day' * 7; -- 1 week
```
## conversion
| Function | Description | Example |
| ---------------------------- | ------------------ | -------------------------------------------------------- |
| `TO_DATE(text, format)` | Text → Date | `TO_DATE('28-10-2025', 'DD-MM-YYYY')` |
| `TO_TIMESTAMP(text, format)` | Text → Timestamp | `TO_TIMESTAMP('2025-10-28 14:30', 'YYYY-MM-DD HH24:MI')` |
| `CAST(value AS DATE)` | Convert to date | `CAST('2025-10-28' AS DATE)` |
| `::DATE` | Shorthand for cast | `'2025-10-28'::DATE` |