Dates, Times & Time Zones

Orders have dates, trains have departure times, subscriptions last for a period. Getting dates right looks easy until two servers in different countries disagree about what "today" means. PostgreSQL has excellent date and time support, and one rule that avoids most problems: store moments in time as timestamptz.

In this lesson you will learn the date and time types, how time zones work, date arithmetic with intervals, and the functions you need for reports: date_trunc, extract, age and to_char.

The date and time types

timestamptz is short for timestamp with time zone. The name is misleading: it does not store a time zone. It converts the value you give it to UTC (Coordinated Universal Time, the world's reference clock), stores that, and converts back to your session's time zone when you read it. So one stored moment shows as 10:30 in India and 05:00 in London, and both are correct.

timestamp (without time zone) stores the digits exactly as given, with no idea where in the world they belong. That is fine for things like "the shop opens at 09:00 every day", but for events that happened at a moment (an order, a login, a payment), use timestamptz.

Time zones in action

Your session has a TimeZone setting. India uses Asia/Kolkata (IST, UTC+05:30). Watch the same stored moment display differently:

SET TimeZone = 'Asia/Kolkata';
SELECT timestamptz '2025-03-15 05:00:00+00' AS payment_time;

SET TimeZone = 'Europe/London';
SELECT timestamptz '2025-03-15 05:00:00+00' AS payment_time;

SELECT timestamptz '2025-03-15 05:00:00+00' AT TIME ZONE 'Asia/Kolkata' AS india_wall_clock;

Query OK
payment_time
-------------------------
2025-03-15 10:30:00+05:30
(1 row)

Query OK
payment_time
----------------------
2025-03-15 05:00:00+00
(1 row)

india_wall_clock
-------------------
2025-03-15 10:30:00
(1 row)

AT TIME ZONE converts a timestamptz into the local wall-clock time of a named zone, giving a plain timestamp. Always use zone names such as Asia/Kolkata rather than fixed offsets, because names know about daylight saving rules in countries that have them.

Set the time zone once per database or user (ALTER DATABASE shop SET TimeZone = 'Asia/Kolkata';) instead of repeating it in every query. The browser examples set it explicitly so that everyone sees the same output.

Getting the current date and time

now() stays the same for the whole transaction, which is useful: every row inserted by one transaction gets the same time. These values change every second, so examples in this course use fixed dates instead to keep outputs stable.

SELECT current_date, now();

 current_date |               now
--------------+----------------------------------
 2025-06-01   | 2025-06-01 11:42:07.512834+05:30
(1 row)

Your result will show your own date and time, of course.

Date arithmetic

Subtracting two dates gives a number of days. Adding a whole number to a date adds days. For anything else, use an interval:

SELECT order_id,
       order_date,
       order_date + 5 AS promised_by,
       date '2025-06-01' - order_date AS days_since_order
FROM orders
WHERE customer_id = 1
ORDER BY order_date;

SELECT date '2025-01-31' + interval '1 month' AS one_month_later,
       interval '1 day 2 hours' * 3 AS three_shifts,
       age(date '2025-06-01', date '2024-01-15') AS member_for;

order_id | order_date | promised_by | days_since_order
---------+------------+-------------+-----------------
101      | 2025-01-05 | 2025-01-10  | 147
104      | 2025-02-02 | 2025-02-07  | 119
115      | 2025-05-20 | 2025-05-25  | 12
(3 rows)

one_month_later     | three_shifts    | member_for
--------------------+-----------------+----------------------
2025-02-28 00:00:00 | 3 days 06:00:00 | 1 year 4 mons 17 days
(1 row)

Adding one month to 31 January gives 28 February, because February has no 31st. age() returns a human-style interval in years, months and days, which is good for "member for 1 year 4 months". The result type of date + interval is a timestamp; cast it with ::date if you only want the day.

Functions for reports

Three functions do most of the work in date reports:

• date_trunc('month', value) cuts a date or timestamp down to the start of its month (or 'day', 'week', 'year' and so on), which is perfect for grouping.
• extract(field FROM value) pulls out one part as a number: year, month, day, dow (day of week, Sunday is 0), hour and more.
• to_char(value, 'pattern') formats a date as text, such as 'DD Mon YYYY'.

SELECT date_trunc('month', order_date)::date AS month,
       count(*) AS orders,
       to_char(date_trunc('month', order_date), 'Mon YYYY') AS label
FROM orders
GROUP BY 1, 3
ORDER BY 1;

SELECT order_id, to_char(order_date, 'DD Mon YYYY, Dy') AS nice_date,
       extract(dow FROM order_date) AS weekday_number
FROM orders
WHERE order_id IN (101, 107, 115);

month      | orders | label
-----------+--------+---------
2025-01-01 | 3      | Jan 2025
2025-02-01 | 3      | Feb 2025
2025-03-01 | 3      | Mar 2025
2025-04-01 | 3      | Apr 2025
2025-05-01 | 3      | May 2025
(5 rows)

order_id | nice_date        | weekday_number
---------+------------------+---------------
101      | 05 Jan 2025, Sun | 0
107      | 09 Mar 2025, Sun | 0
115      | 20 May 2025, Tue | 2
(3 rows)

GROUP BY 1, 3 means "group by the first and third columns of the SELECT list". To go the other way, from text to a date, use to_date('15-03-2025', 'DD-MM-YYYY').

Common mistakes

• Storing dates as text. '05/01/2025' sorts and compares as text, and nobody knows whether it means 5 January or 1 May. Use date.
• Typing ambiguous dates. PostgreSQL reads '05/01/2025' according to the DateStyle setting, which is month-first by default. Always type ISO dates: '2025-01-05'.
• Using timestamp for events. When servers or users are in different zones, plain timestamps become wrong. Use timestamptz.
• Comparing a timestamp with a date. WHERE created_at = '2025-03-15' only matches midnight. Use a range: created_at >= '2025-03-15' AND created_at < '2025-03-16'.

Key points

• date stores a day, timestamptz an exact moment, interval a length of time.
• timestamptz stores UTC and displays in the session TimeZone; India is Asia/Kolkata.
• now() is the transaction start time; clock_timestamp() is the real current time.
• date - date gives days; add an interval for months, hours and so on.
• date_trunc groups by period, extract gets a part, to_char formats, age gives a readable difference.
• Always write dates in ISO format: YYYY-MM-DD.

SELECT first_name,
       hire_date,
       extract(year FROM hire_date) AS join_year,
       age(date '2025-04-01', hire_date) AS service
FROM employees
ORDER BY hire_date;