Part 10: Date, Time & Timestamp Types with Timezone Handling

Handling temporal data correctly prevents time-travel bugs and scheduling errors across global users.

1. Temporal Types

• DATE: Stores date only (YYYY-MM-DD). E.g., 2026-09-12.
• TIME: Stores time only (HH:MM:SS). E.g., 14:30:00.
• TIMESTAMP: Date and time without timezone info.
• TIMESTAMPTZ (or TIMESTAMP WITH TIME ZONE): Date, time, and timezone offset.

Production Best Practice: Always store audit columns (created_at, updated_at) as TIMESTAMPTZ in UTC!

CREATE TABLE audit_log (
  id BIGINT PRIMARY KEY,
  event_name VARCHAR(100) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

Key Takeaways

• Always store timestamps in UTC using TIMESTAMPTZ.
• Convert to local user timezones in the presentation layer.

In Part 11, we enter Module 2 to master The SELECT Statement: Retrieving Columns & Aliasing!