DATE/TIMESTAMP
PostgreSQL has date, time, timestamp and timestamptz types with rich date arithmetic.
In this page:
Syntax
column_name date
column_name timestamp
column_name timestamptz DEFAULT now()DATE/TIMESTAMP
date stores a calendar day, timestamp stores date and time without a zone, and timestamptz stores an absolute moment (internally UTC) that is displayed in the session time zone.
Subtracting dates gives an integer number of days, and intervals such as INTERVAL '7 days' can be added. now() and CURRENT_DATE give the current time.
Note:
Use timestamptz for events so time zones are handled correctly.
Example: DATE/TIMESTAMP
shop=# SELECT DATE '2024-03-15' + 30 AS plus_30_days;
plus_30_days
--------------
2024-04-14
shop=# SELECT DATE '2024-03-15' - DATE '2024-03-01' AS days_between;
days_between
--------------
14
shop=# SELECT TIMESTAMP '2024-03-15 10:30:00' + INTERVAL '2 hours 15 minutes' AS later;
later
---------------------
2024-03-15 12:45:00
shop=# SELECT to_char(TIMESTAMPTZ '2024-03-15 10:30:00+00', 'YYYY-MM-DD HH24:MI') AS formatted;
formatted
------------------
2024-03-15 10:30⚠️ Run this in your own terminal or Node.js environment.
Related Topics
Common Mistakes
- Storing timestamps without time zones for global users
- Comparing dates as text
- Ignoring the session time zone
Chapter Summary
- date, time, timestamp, timestamptz
- Dates subtract to days
- Add INTERVAL values
- Prefer timestamptz for events
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: