Course contentsShow
← Data Science
Lesson 1042 of 2,14526. Advanced SQLFree lesson

Working with Timestamps and Time Zones

Handling timezone-aware timestamps, converting between zones, and understanding UTC storage conventions.

Working with Timestamps and Time Zones

What you'll learn: How to handle timezone-aware timestamps, convert between zones, and understand why UTC storage is the gold standard.

The Core Idea

A timestamp without timezone information is like saying "the meeting is at 3 PM" without specifying whether that's New York time or Tokyo time—it creates ambiguity. In SQL, you have timezone-naive timestamps (just date and time) and timezone-aware timestamps (date, time, plus zone information). Understanding the difference is crucial for accurate data analysis across global systems.

Why UTC Storage Matters

UTC (Coordinated Universal Time) is the universal baseline—think of it as the "source of truth" for time. The best practice is to store all timestamps in UTC in your database, then convert to local timezones only when displaying to users. This prevents confusion around daylight saving time changes, regional differences, and historical timezone shifts.

Working with Timezone-Aware Data

Most databases provide functions to:

  • Convert naive timestamps to timezone-aware: Add zone information to existing timestamps
  • Convert between zones: Transform a timestamp from one timezone to another (e.g., Pacific to Eastern)
  • Extract the timezone: Determine what zone a timestamp uses
  • Normalize to UTC: Convert any timezone-aware timestamp to UTC for storage

Common functions include AT TIME ZONE (PostgreSQL), CONVERT_TZ() (MySQL), or similar zone conversion utilities. You specify the target timezone (like 'America/New_York' or 'UTC'), and the database handles the arithmetic including DST adjustments.

Real-World Example Pattern

User logs in at 2 PM Pacific → Store as UTC (10 PM same day) → Display to European user in their local time (11 PM Central European Time). Always convert at the application/display layer, never in storage.

Key Takeaway: Always store timestamps in UTC and convert to local timezones only for display; this prevents ambiguity, handles daylight saving time automatically, and ensures consistent global data analysis.