Dates, time zones and text

conceptThe concept

timestamptz is stored as UTC and converted to the session's TimeZone on output. timestamp (without time zone) is a wall-clock reading with no zone at all. ts AT TIME ZONE 'Asia/Kolkata' converts a timestamptz into the local wall-clock time there (a timestamp); applied to a timestamp it does the reverse.

date_trunc('day', ts) buckets times; date_trunc on a timestamptz truncates in the session zone, and the three-argument form date_trunc('day', ts, 'Europe/Berlin') picks the zone explicitly. Intervals do calendar arithmetic: + interval '1 month' from January 31 lands on the last day of February.

Filter time with half-open ranges: created_at >= '2025-03-01' AND created_at < '2025-04-01'. It is exact, index-friendly and never misses the last second of a day. For text, lower, trim, split_part, format, regexp_replace and ~ (regex match) cover most cleanup.

whyWhy it matters

A daily revenue report grouped by UTC day puts late-evening US orders on the wrong date. A BETWEEN on dates silently drops everything after midnight on the last day. These are real bugs that pass tests.

Use caseReal-world scenario

The Bengaluru team wants orders per local day, and the Berlin team wants the same report in their time zone. One query with the three-argument date_trunc serves both.

Try itExamples

Run and edit these in the interactive lesson.

SELECT created_at,
       created_at AT TIME ZONE 'Asia/Kolkata' AS kolkata_wall_clock,
       created_at AT TIME ZONE 'America/New_York' AS new_york_wall_clock
FROM orders
WHERE id = 1;

BEGIN;
SET LOCAL TimeZone = 'Asia/Kolkata';
SELECT created_at AS displayed_in_session_zone FROM orders WHERE id = 1;
ROLLBACK;

SELECT date_trunc('day', created_at, 'Asia/Kolkata')::date AS kolkata_day,
       count(*) AS orders,
       sum(total) AS revenue
FROM orders
WHERE created_at >= '2025-03-01' AND created_at < '2025-03-08'
GROUP BY 1
ORDER BY 1;

SELECT date '2025-01-31' + interval '1 month' AS plus_one_month,
       age(timestamp '2025-10-08', timestamp '1990-05-17') AS age,
       extract(epoch FROM interval '1 day 2 hours') AS seconds,
       now() - interval '90 days' > timestamptz '2020-01-01' AS sanity,
       make_interval(days => 10, hours => 3) AS built;

SELECT email,
       split_part(email, '@', 2) AS domain,
       initcap(replace(split_part(email, '@', 1), '.', ' ')) AS from_email,
       format('%s (%s, %s)', name, city, country) AS label,
       regexp_replace(email, '\d+', '', 'g') AS digits_removed
FROM customers
ORDER BY id
LIMIT 5;

SELECT name,
       name ~ '^(Smart|Pro) ' AS is_premium_line,
       substring(sku FROM '\d+')::int AS sku_number,
       regexp_matches(name, '(\w+)$') AS last_word
FROM products
WHERE name ~* 'headphones|speaker'
ORDER BY sku
LIMIT 6;

internalsUnder the hood

Both timestamp types take 8 bytes with microsecond precision. The session TimeZone only affects input parsing and display of timestamptz; the stored value never changes. Use IANA names such as 'America/New_York' rather than abbreviations like EST, which ignore daylight saving.

Avoid / preferDo this, not that

SELECT count(*) FROM orders WHERE created_at::date = '2025-03-01';

SELECT count(*) FROM orders
WHERE created_at >= '2025-03-01' AND created_at < '2025-03-02';

SELECT date_trunc('day', created_at), count(*) FROM orders GROUP BY 1;
-- "days" depend on whoever's session TimeZone runs it

SELECT date_trunc('day', created_at, 'Europe/London'), count(*) FROM orders GROUP BY 1;

PitfallsCommon mistakes

• Storing event times as timestamp without time zone. When servers or users span zones, you can no longer tell when something happened.
• WHERE created_at BETWEEN '2025-03-01' AND '2025-03-31' on a timestamp column: the upper bound is midnight at the start of the 31st. Use a half-open range.
• Wrapping the column in a function in WHERE (WHERE created_at::date = '2025-03-01'). It cannot use a plain index on created_at; rewrite it as a range.

LimitsWhen not to use it

Do not store dates as text or as integer epoch seconds. You lose validation, time zone handling, interval arithmetic and readable output, and every query needs conversions.

SecuritySecurity notes

Do not pass user input as a regular expression: a crafted pattern can be very slow, and wildcards change meaning. Escape user text for LIKE (with an ESCAPE clause) or use plain equality or full-text search.

ChallengeTry it yourself

SELECT extract(isodow FROM created_at AT TIME ZONE 'Asia/Kolkata') AS dow,
       to_char(created_at AT TIME ZONE 'Asia/Kolkata', 'Dy') AS day,
       count(*) AS orders
FROM orders
WHERE created_at >= '2025-01-01 00:00 Asia/Kolkata'
  AND created_at <  '2026-01-01 00:00 Asia/Kolkata'
GROUP BY 1, 2
ORDER BY 1;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Date/time functions
• String functions
• Pattern matching