Data Types for Working with Time and Dates: DATE, TIME, TIMESTAMP, TIMESTAMPTZ

When you’re dealing with data, there’s almost always something about time popping up. Think about flight schedules, order deadlines, or the date when a user signed up on a website. All of that is about time. And to work with it comfortably, you need the right tools. PostgreSQL has special data types that are perfect for storing and handling dates and times.

Sure, you could just save a date as a regular string like "2023-10-12", but that’s more of a trap than a solution. Strings can’t compare dates, don’t know what “plus three days” means, and have zero clue about time zones. But temporal data types? They know all that and more. With them, it’s easier, safer, and faster.

The DATE Type

The DATE type is used to store just the calendar date, without any specific time. It’s handy when you need to work with dates as standalone things, like a birthday, the start of a year, and so on.

Examples:

-- Example of creating a table with the `DATE` type
CREATE TABLE events (
    event_name TEXT,
    event_date DATE
);

-- Inserting data
INSERT INTO events (event_name, event_date)
VALUES ('PostgreSQL Conference', '2023-12-01'),
       ('Birthday', '2023-10-12');

-- Select query
SELECT * FROM events;

Result:

The TIME Type

The TIME type stores ONLY the time, meaning hours, minutes, and seconds. It’s perfect for stuff like schedules, for example, bus timetables or store opening hours.

Examples:

-- Example of creating a table with `TIME`
CREATE TABLE schedules (
    schedule_name TEXT,
    start_time TIME,
    end_time TIME
);

-- Inserting data
INSERT INTO schedules (schedule_name, start_time, end_time)
VALUES ('Work hours', '09:00:00', '18:00:00'),
       ('Lunch break', '13:00:00', '14:00:00');

-- Select query
SELECT schedule_name, start_time, end_time FROM schedules;

Result:

The TIMESTAMP Type

The TIMESTAMP type combines the calendar date and time in one value. But it does NOT take time zones into account. That can get confusing if your data is used by people in different time zones.

Examples:

-- Example of creating a table with `TIMESTAMP`
CREATE TABLE documents (
    document_id SERIAL PRIMARY KEY,
    created_at TIMESTAMP
);

-- Inserting data
INSERT INTO documents (created_at)
VALUES ('2023-10-12 15:30:00'),
       ('2023-12-01 08:45:15');

-- Select query
SELECT document_id, created_at FROM documents;

Result:

The TIMESTAMPTZ Type

The TIMESTAMPTZ type (where TZ means "time zone") is similar to TIMESTAMP, but it also stores info about the time zone. That makes it a must-have for apps that work with users from all over the world.

Examples:

-- Example of creating a table with `TIMESTAMPTZ`
CREATE TABLE meetings (
    meeting_id SERIAL PRIMARY KEY,
    meeting_time TIMESTAMPTZ
);

-- Inserting data (PostgreSQL saves the current time zone)
INSERT INTO meetings (meeting_time)
VALUES ('2023-10-12 15:30:00+03'),
       ('2023-12-01 08:45:15-05');

-- Select query
SELECT meeting_id, meeting_time FROM meetings;

Result:

Notice that PostgreSQL automatically converts the time to the server’s time zone.

Why Use Specialized Data Types?

Data correctness. Types like DATE and TIMESTAMP prevent you from entering bad data. For example, you can’t save a date that doesn’t exist, like "2023-02-30".

Convenience. You can compare dates, subtract them, get the current date, and even round values (we’ll talk about that later).

Performance. Temporal data types take up less space in memory and indexes than strings, which speeds up queries.

Example: Creating a Table Using All Types

Let’s make a more complex table for storing an event schedule. We’ll use several types at once: DATE, TIME, TIMESTAMP, and TIMESTAMPTZ.

CREATE TABLE event_schedule (
    event_id SERIAL PRIMARY KEY,
    event_name TEXT NOT NULL,
    event_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    full_start TIMESTAMP NOT NULL,
    full_start_with_zone TIMESTAMPTZ NOT NULL
);

-- Inserting data
INSERT INTO event_schedule (
    event_name, event_date, start_time, end_time, full_start, full_start_with_zone
)
VALUES
    ('Morning meetup', '2023-11-10', '10:00:00', '11:30:00', '2023-11-10 10:00:00', '2023-11-10 10:00:00+03'),
    ('Evening workshop', '2023-11-11', '18:00:00', '20:00:00', '2023-11-11 18:00:00', '2023-11-11 18:00:00+03');

-- Checking the data
SELECT * FROM event_schedule;

Result:

This is a real-life example of a database for managing schedules. You can see how different temporal data formats complement each other depending on the task.

Hope you remember how to use the DATE, TIME, TIMESTAMP, and TIMESTAMPTZ types in PostgreSQL. In the next lectures, we’ll dive deeper into working with temporal functions and learn how to extract, format, and manage temporal data using SQL queries.