Date & Time Types

Dates and times look simple until your application has users in multiple timezones, a server that runs somewhere else entirely, and a report that needs to be correct at midnight on a leap year. SQL gives you several distinct types for temporal data, and choosing the right one — especially whether it's timezone-aware — matters more than almost any other type decision you'll make.

The core types

Type	Stores	Example
DATE	Calendar date only, no time	2026-07-08
TIME	Time of day only, no date	14:30:00
TIMESTAMP	Date and time, no timezone (naive)	2026-07-08 14:30:00
TIMESTAMP WITH TIME ZONE	Date and time, timezone-aware	2026-07-08 14:30:00+00
INTERVAL	A span of time (a duration), not a point in time	3 days, '2 hours 30 minutes'

Basic usage

Declaring temporal columns

SQL

CREATE TABLE events (
    id          SERIAL PRIMARY KEY,
    event_date  DATE NOT NULL,                  -- just the calendar day
    start_time  TIME NOT NULL,                  -- just the time of day
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- timezone-aware, PostgreSQL shorthand
    duration    INTERVAL                        -- e.g. '01:30:00' for 90 minutes
);

SELECT event_date + INTERVAL '7 days' AS one_week_later FROM events;

Naive vs. timezone-aware timestamps

A plain TIMESTAMP stores a date and time with no notion of which timezone it represents — it is often called "timezone-naive." A TIMESTAMP WITH TIME ZONE (often written TIMESTAMPTZ) always normalizes and stores the instant in UTC internally, then converts to whichever timezone the querying session is configured for when it is displayed.

Warning

Timezone-naive timestamps are a frequent source of real production bugs. If your application server, database server, and end users are not all in the same timezone — which is the common case — a naive `TIMESTAMP` column is ambiguous: `2026-07-08 14:30:00` means a different real-world instant depending on who is reading it. Bugs like "an event shows up on the wrong day" or "a scheduled job runs an hour early after a daylight-saving change" almost always trace back to a naive timestamp being interpreted in the wrong timezone somewhere along the pipeline.

Tip

Store all timestamps in UTC using a timezone-aware type (`TIMESTAMPTZ` in PostgreSQL, `DATETIME2` with explicit UTC convention in SQL Server, or `TIMESTAMP` with the session/connection forced to UTC in MySQL), and only convert to the user's local timezone at the very last step — in the application or presentation layer. Never store "local time" directly unless you also store which timezone it was local to.

Working with intervals

Date arithmetic with INTERVAL

SQL

-- Rows created in the last 24 hours
SELECT * FROM events
WHERE created_at > NOW() - INTERVAL '24 hours';

-- Age between two timestamps
SELECT AGE(NOW(), created_at) AS how_long_ago FROM events;

Dialect differences

MySQL uses `DATETIME` (naive) and `TIMESTAMP` (which is timezone-aware but stored/converted relative to the server's configured timezone and has a narrower valid range than `DATETIME`). SQL Server uses `DATETIME2` (naive, higher precision than the older `DATETIME`) and `DATETIMEOFFSET` for timezone-aware values. PostgreSQL uses `TIMESTAMP` (naive) and `TIMESTAMP WITH TIME ZONE` / `TIMESTAMPTZ` (timezone-aware). The names differ, but the naive vs. aware distinction — and the advice to prefer the timezone-aware variant — holds across all three.

Use DATE for pure calendar concepts that have no time component — a birthday, a due date, a holiday.
Use TIME sparingly — it's useful for recurring daily schedules (store opening time) but rarely for one-off events.
Use a timezone-aware timestamp type for anything that records "when did this actually happen" — created_at, updated_at, logged_in_at.
Use INTERVAL for durations and offsets, not for representing a specific point in time.