When tracking events or changes in a database, you often need to record exactly when something happened. This is where the TIMESTAMP data type becomes essential. TIMESTAMP stores both date and time information, making it ideal for logging user actions, tracking record creation, or updating times in applications. Many real-world scenarios require even more precision: global businesses, for instance, need to know not just when an event happened, but also in which time zone. For these cases, SQL provides TIMESTAMP WITH TIME ZONE (sometimes called TIMESTAMPTZ). This data type ensures that time zone information is stored alongside the date and time, allowing you to accurately compare or display times across different regions. Consider a global event logging system: a user in New York and another in Tokyo might perform the same action at the "same time" locally, but those moments are hours apart in absolute terms. By using TIMESTAMP WITH TIME ZONE, you capture this context, making your data reliable and meaningful wherever it is used.

CREATE TABLE event_log (
    event_id SERIAL PRIMARY KEY,
    description VARCHAR(255),
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE
);

In the event_log table above, the created_at column uses TIMESTAMP, which stores the date and time but does not track any time zone information. The updated_at column, on the other hand, uses TIMESTAMP WITH TIME ZONE. This means that when you insert or update a value in updated_at, SQL will store both the date/time and the time zone offset. The difference is crucial: if you insert '2024-06-01 14:30:00' into a TIMESTAMP column, the database simply records that value as-is, with no context about where in the world that time applies. If you insert the same value into a TIMESTAMP WITH TIME ZONE column and specify the time zone (like '2024-06-01 14:30:00+09'), the database will store the absolute point in time, regardless of where the user is located. This ensures that when you retrieve or compare times, you always know their true relationship, even if your application is used across multiple continents.

-- Insert with TIMESTAMP (no time zone)
INSERT INTO event_log (description, created_at, updated_at)
VALUES ('User login', '2024-06-01 14:30:00', NULL);

-- Insert with TIMESTAMP WITH TIME ZONE
INSERT INTO event_log (description, created_at, updated_at)
VALUES ('Profile updated', '2024-06-01 14:35:00', '2024-06-01 14:35:00+02');

-- Another TIMESTAMP WITH TIME ZONE example (different zone)
INSERT INTO event_log (description, created_at, updated_at)
VALUES ('Password reset', '2024-06-01 14:40:00', '2024-06-01 21:40:00+09');