Date & Time Types

Ever stored a date as a VARCHAR and then spent an afternoon debugging why '2024-01-05' sorts before '2023-12-31'? Or tried to calculate the age of a user and got a cryptic error? If you've worked with dates and times in PostgreSQL without a solid mental model, you've probably hit these walls. In this lesson, you'll master PostgreSQL's native date and time types — DATE, TIME, TIMESTAMP, TIMESTAMPTZ, and INTERVAL — so you can store, query, and manipulate temporal data with confidence, avoiding the pitfalls that plague many developers.

The problem this lesson solves

Without native date and time types, you'd have to store temporal data as text or integers, leading to:

• Lexical vs. chronological sorting: '2023-09-10' sorts before '2023-09-2' because string comparison works character by character.
• Invalid dates: Nothing stops you from inserting '2023-02-30' as a string.
• Time zone madness: UTC timestamps get mixed with local times, and you end up with off-by-hours errors.
• Arithmetic pain: Calculating a difference between two dates becomes manual math, error-prone and ugly.

PostgreSQL solves this with dedicated types that bring validation, ordering, arithmetic, and time zone awareness directly into the database engine. Once you master these types, you'll write cleaner SQL and avoid a whole class of bugs.

Core concept / mental model

Think of date and time types as specialized containers:

• DATE — a calendar day (no time). Like a sticky note with "April 5, 2024".
• TIME — a time of day (no date). Like "14:30:00" — useful for opening hours or daily schedules.
• TIMESTAMP — a date + time, but no time zone. Say "2024-04-05 14:30:00" — you don't know if it's UTC, PST, or something else. It's like a note that says "the meeting is at 2:30 PM" — you need to know the context.
• TIMESTAMPTZ — a date + time + time zone. PostgreSQL stores it internally as UTC, but displays it in your session's time zone. It's like saying "meeting at 2:30 PM EST" — unambiguous.
• INTERVAL — a duration, like "2 days" or "3 hours 15 minutes". Perfect for adding or subtracting time spans.

Key mental model: TIMESTAMPTZ is the gold standard for event times. It stores an absolute moment in time, so comparisons are always correct across time zones. TIMESTAMP is a local wall-clock time — useful when you explicitly don't want time zone conversion (e.g., "opening hours in New York", where a 9:00 AM opening should remain 9:00 even if the database server is in London).

How it works step by step

Step 1: Choose the right type

Pick the type that matches your data semantics:

• Event happened at a specific moment → TIMESTAMPTZ
• You need just a day (birthday, release date) → DATE
• You need a time of day regardless of date (class schedule) → TIME
• You need a duration → INTERVAL

Step 2: Know how input works

PostgreSQL accepts a wide range of string formats for each type. The standard ISO 8601 format YYYY-MM-DD for dates, HH:MM:SS for times, and combinations for timestamps. Also, you can use the ::type cast or CAST(... AS type).

Step 3: Use date/time operators and functions

You can add INTERVALs to timestamps, subtract two timestamps to get an INTERVAL, extract parts with EXTRACT, and format with TO_CHAR. PostgreSQL provides a rich function set for all your needs.

Step 4: Handle time zones correctly

• Set your session's timezone with SET TIME ZONE 'UTC'; or the configuration parameter.
• Use AT TIME ZONE to convert between time zones.
• Always store TIMESTAMPTZ for global events.

Hands-on walkthrough

Let's get our hands dirty. We'll create a sample table and play with the types.

-- Create a table with various temporal types
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name TEXT,
    event_date DATE,
    start_time TIME,
    start_timestamp TIMESTAMP,
    start_timestamptz TIMESTAMPTZ,
    duration INTERVAL
);

-- Insert sample data
INSERT INTO events (event_name, event_date, start_time, start_timestamp, start_timestamptz, duration)
VALUES
    ('Conference Kickoff', '2024-05-01', '09:00:00', '2024-05-01 09:00:00', '2024-05-01 09:00:00+02', '8 hours'),
    ('Workshop', '2024-05-02', '10:30:00', '2024-05-02 10:30:00', '2024-05-02 10:30:00+00', '3 hours 30 minutes'),
    ('Networking', '2024-05-03', '18:00:00', '2024-05-03 18:00:00', '2024-05-03 18:00:00-05', '2 hours');

Now let's query and see how the types behave:

-- Basic select
SELECT event_name, event_date, start_time, start_timestamp, start_timestamptz, duration
FROM events;

Result might be (depending on your session time zone):

         event_name         | event_date | start_time |   start_timestamp   |   start_timestamptz   | duration 
---------------------------+------------+------------+---------------------+---------------------+----------
 Conference Kickoff        | 2024-05-01 | 09:00:00   | 2024-05-01 09:00:00 | 2024-05-01 07:00:00 | 08:00:00
 Workshop                  | 2024-05-02 | 10:30:00   | 2024-05-02 10:30:00 | 2024-05-02 10:30:00 | 03:30:00
 Networking                | 2024-05-03 | 18:00:00   | 2024-05-03 18:00:00 | 2024-05-03 23:00:00 | 02:00:00

Notice that start_timestamptz is displayed in the session time zone (here UTC). The inserted value '2024-05-01 09:00:00+02' is converted to UTC (07:00) and then shown as 07:00. start_timestamp remains as you inserted it.

Now let's perform some arithmetic:

-- Add an interval to a timestamp
SELECT event_name, start_timestamp + duration AS end_time
FROM events;

-- Calculate difference between two timestamps
SELECT event_name,
       start_timestamp - '2024-05-01 00:00:00' AS elapsed_since_month_start
FROM events;

Output:

         event_name         |      end_time      | elapsed_since_month_start 
---------------------------+--------------------+---------------------------
 Conference Kickoff        | 2024-05-01 17:00:00 | 09:00:00
 Workshop                  | 2024-05-02 14:00:00 | 1 day 10:30:00
 Networking                | 2024-05-03 20:00:00 | 2 days 18:00:00

Pro tip: You can add an INTERVAL to a DATE and get a TIMESTAMP back. For example, DATE '2024-05-01' + INTERVAL '1 day' gives 2024-05-02 00:00:00.

Extracting parts, formatting, and time zone conversion

-- Extract the year or day of week
SELECT event_name, EXTRACT(YEAR FROM start_timestamp) AS year,
       EXTRACT(DOW FROM start_timestamp) AS day_of_week
FROM events;

-- Format a timestamp using TO_CHAR
SELECT event_name, TO_CHAR(start_timestamptz, 'YYYY-MM-DD HH24:MI') AS formatted_time
FROM events;

-- Convert to a specific time zone
SELECT event_name, start_timestamptz AT TIME ZONE 'America/New_York' AS ny_time
FROM events;

These commands give you powerful ways to shape your output.

Compare options / when to choose what

Choosing the right type is crucial. Here's a comparison table:

When to choose what:

• Always use TIMESTAMPTZ for any timestamp that represents a moment in time (user signups, blog posts, transactions).
• Use DATE when you need just the day.
• Use TIME when the date is irrelevant.
• Use TIMESTAMP only if you are certain that the time zone will never be an issue — for instance, if your entire application is in a single time zone and you store only local times.
• Use INTERVAL for durations and scheduling.

Troubleshooting & edge cases

Issue 1: You get invalid input syntax for type date

Cause: You passed a string that doesn't match the expected format. PostgreSQL is strict about formats.

Fix: Use ISO 8601 format (YYYY-MM-DD). If you have a different format, use TO_DATE with a format specifier.

-- Wrong: `'01/05/2024'` is ambiguous
-- Correct:
SELECT TO_DATE('01/05/2024', 'DD/MM/YYYY');

Issue 2: Time zone surprises — your TIMESTAMPTZ shows unexpected time

Cause: Your session time zone is different from what you expect. TIMESTAMPTZ is stored UTC and displayed in the session's time zone.

Fix: Check and set the time zone explicitly.

SHOW TIME ZONE;
SET TIME ZONE 'UTC';
SELECT '2024-05-01 09:00:00+02'::timestamptz;

This will show 2024-05-01 07:00:00+00 (in UTC).

Issue 3: Subtracting timestamps gives unexpected format

Cause: When you subtract two timestamps, PostgreSQL returns an INTERVAL. If you expect a number of days, you need to extract it.

SELECT '2024-05-03'::timestamp - '2024-05-01'::timestamp;  -- returns interval '2 days'
-- To get days as a number:
SELECT EXTRACT(DAY FROM '2024-05-03'::timestamp - '2024-05-01'::timestamp);  -- returns 2

Issue 4: Date arithmetic across DST changes

Cause: Adding a day to a TIMESTAMPTZ might not result in the same wall-clock time if a DST boundary is crossed. For example, 2024-03-30 12:00:00+01 + INTERVAL '1 day' could yield 2024-03-31 12:00:00+01 or +02 depending on the time zone.

Fix: If you want to preserve the wall clock time, use TIMESTAMP instead of TIMESTAMPTZ. If you want to add a fixed duration of 24 hours, use make_interval or INTERVAL '1 day' (which is exactly 24 hours) — but note that in DST transitions, the local time may shift. For most business logic, this is fine; just be aware.

Issue 5: Leap years and invalid dates

PostgreSQL validates dates, so '2023-02-29' throws an error. Use date literals and let the database handle the calendar.

What you learned & what's next

You've learned the core date and time types in PostgreSQL: DATE, TIME, TIMESTAMP, TIMESTAMPTZ, and INTERVAL. You can now:

• Choose the right type for your data.
• Insert and query temporal data with confidence.
• Perform arithmetic using INTERVAL and functions like EXTRACT and TO_CHAR.
• Handle time zones properly with TIMESTAMPTZ and AT TIME ZONE.
• Troubleshoot common pitfalls around input formats, time zone display, and DST.

These skills are foundational for many real-world applications — from event scheduling to analytics dashboards.

Next step: In the next lesson, you'll build on this foundation to learn about date/time functions and advanced querying patterns — such as date truncation, rolling windows, and efficient date-based indexing. Keep the momentum going!