SELECT

CURRENT_DATE AS today,

CURRENT_TIMESTAMP AS now;

SELECT

order_date,

EXTRACT(YEAR FROM order_date) AS year,

EXTRACT(MONTH FROM order_date) AS month,

EXTRACT(DAY FROM order_date) AS day

FROM orders;

SELECT

order_date,

order_date + INTERVAL '7 days' AS delivery_date,

CURRENT_DATE - order_date AS days_ago

FROM orders;

SELECT

created_at AT TIME ZONE 'UTC' AS utc_time,

created_at AT TIME ZONE 'America/New_York' AS ny_time

FROM events;

DATE: Calendar date only (no time)

TIMESTAMP: Date + time, no timezone info

TIMESTAMPTZ: Date + time WITH timezone (recommended)

INTERVAL: Duration (7 days, 1 month, 2 hours)

EXTRACT: Returns numeric part (year, month, day)

DATE_TRUNC: Rounds down to unit (month, day, hour)

These temporal types and functions let you store, compare, shift, and report time safely. The goal is predictable arithmetic and correct timezone behavior.

DATE

Stores a calendar date without any time-of-day component.

DATE '2026-04-18'

TIME

Stores a time-of-day value without any date component.

TIME '14:30:00'

TIMESTAMP

Stores date and time together, typically without timezone context.

TIMESTAMP '2026-04-18 14:30:00'

TIMESTAMPTZ

Timezone-aware timestamp type used to store absolute moments in time.

TIMESTAMPTZ '2026-04-18 14:30:00+05:30'

PostgreSQL stores it in UTC internally and adjusts on display.

INTERVAL

Represents a duration that can be added to or subtracted from dates and timestamps.