SQLData Types

SQL Data Types

LevelBeginner

Duration60 mins

TopicData Types

3 / 5

Date/Time Types

The Complexity of Time

Time appears simple—until you try to store it in a database. When did that order ship? What's the user's local time? Did this record change during the daylight saving transition? Was this timestamp recorded in UTC or the server's timezone?

Temporal data is deceptively complex. Time zones shift, daylight saving creates discontinuities, leap seconds exist, and different systems represent time in incompatible ways. A single 'timestamp' can be interpreted as at least three different moments depending on timezone assumptions.

This page provides exhaustive coverage of SQL date and time types, timezone handling, precision considerations, and the principles that separate robust temporal design from bug-prone implementations.

What You Will Learn

By the end of this page, you will understand all SQL temporal types (DATE, TIME, TIMESTAMP, INTERVAL), timezone-aware versus timezone-naive storage, precision and range considerations, and best practices for storing temporal data in production systems.

The Temporal Type Taxonomy

SQL defines several temporal types, each capturing different aspects of time:

Core Temporal Types:

SQL Temporal Type Overview
Type	Stores	Example	Use Case
DATE	Calendar date only	2024-03-15	Birthdays, holidays, due dates
TIME	Time of day only	14:30:00	Business hours, schedules
TIMESTAMP	Date + time	2024-03-15 14:30:00	Event timestamps, logs
TIMESTAMP WITH TIME ZONE	Date + time + timezone	2024-03-15 14:30:00+05:30	Global events, audit logs
INTERVAL	Duration/period	3 days 4 hours	Time differences, durations

The Fundamental Distinction: Point in Time vs. Calendar/Clock Reading

This distinction is crucial:

Absolute time (instant): A specific moment in the history of the universe. "The server crashed at this instant." Requires timezone context to be unambiguous.
Local/civil time: A calendar date and clock reading without timezone. "The meeting is at 2:00 PM." Interpretation depends on assumed context.

Using the wrong type causes bugs: storing a user's birthday as TIMESTAMP WITH TIME ZONE makes their birthday shift when viewed from different timezones!

The Most Common Temporal Bug

Using TIMESTAMP (without timezone) for event times. When the database server moves, restores to a different location, or replicates across regions, all timestamps are reinterpreted in the new timezone. A 2:00 PM meeting becomes 2:00 AM across a 12-hour timezone change. Always use TIMESTAMP WITH TIME ZONE for absolute moments.

DATE Type: Calendar Dates

The DATE type stores a calendar date (year, month, day) without time components. It represents a full day rather than a specific instant.

Characteristics:

Storage: Typically 4 bytes
Range: Varies by implementation (commonly 0001-01-01 to 9999-12-31)
No time component, no timezone
Represents a calendar day, not a 24-hour period

DATE is ideal for:

Birthdates (a birthday is March 15, regardless of timezone)
Holidays and calendar events
Due dates and deadlines
Historic dates without time precision
Scheduling tied to calendar days rather than specific times

date_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
-- DATE type usage examples
 
CREATE TABLE employees (
    employee_id     INTEGER PRIMARY KEY,
    first_name      VARCHAR(50) NOT NULL,
    last_name       VARCHAR(50) NOT NULL,
    birth_date      DATE NOT NULL,            -- Calendar date only
    hire_date       DATE NOT NULL,
    termination_date DATE                     -- NULL if still employed
);
 
CREATE TABLE holidays (
    holiday_id      INTEGER PRIMARY KEY,
    holiday_name    VARCHAR(100) NOT NULL,
    holiday_date    DATE NOT NULL,            -- Dec 25, 2024 (independent of timezone)
    is_paid         BOOLEAN DEFAULT TRUE
);
 
CREATE TABLE invoices (
    invoice_id      INTEGER PRIMARY KEY,
    invoice_date    DATE NOT NULL,            -- Date invoice was issued
    due_date        DATE NOT NULL,            -- Payment deadline
    customer_id     INTEGER NOT NULL
);
 
-- Date literals and insertion
INSERT INTO employees (employee_id, first_name, last_name, birth_date, hire_date)
VALUES (1, 'Alice', 'Johnson', '1990-05-20', '2020-03-15');
 
-- Standard ISO 8601 format: YYYY-MM-DD
INSERT INTO holidays (holiday_id, holiday_name, holiday_date)
VALUES (1, 'Christmas Day', '2024-12-25');
 
-- Date extraction functions
SELECT 
    birth_date,
    EXTRACT(YEAR FROM birth_date) AS birth_year,
    EXTRACT(MONTH FROM birth_date) AS birth_month,
    EXTRACT(DAY FROM birth_date) AS birth_day,
    EXTRACT(DOW FROM birth_date) AS day_of_week  -- 0=Sunday, 6=Saturday
FROM employees;
 
-- Date arithmetic
SELECT 
    hire_date,
    hire_date + INTERVAL '30 days' AS probation_end,
    hire_date + INTERVAL '1 year' AS first_anniversary,
    CURRENT_DATE - hire_date AS days_employed
FROM employees;
 
-- Date comparisons
SELECT * FROM invoices
WHERE due_date < CURRENT_DATE;  -- Overdue invoices
 
SELECT * FROM employees
WHERE hire_date BETWEEN '2020-01-01' AND '2020-12-31';  -- Hired in 2020
 
-- Age calculation
SELECT 
    first_name,
    birth_date,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) AS age
FROM employees;
 
-- First/last day of month
SELECT 
    invoice_date,
    DATE_TRUNC('month', invoice_date) AS first_of_month,
    DATE_TRUNC('month', invoice_date) + INTERVAL '1 month' - INTERVAL '1 day' AS last_of_month
FROM invoices;

Birthdays and Timezone Independence

A birthday is a calendar concept—Alice was born on May 20, regardless of whether you're viewing from New York or Tokyo. Using TIMESTAMP or TIMESTAMP WITH TIME ZONE for birthdates is incorrect; it would cause the birthday to appear as May 19 or May 21 depending on the viewer's timezone.

TIME Type: Time of Day

The TIME type stores a time of day (hours, minutes, seconds, and optionally fractional seconds) without a date component.

Variants:

TIME or TIME WITHOUT TIME ZONE: Local time reading (e.g., 14:30:00)
TIME WITH TIME ZONE: Time with timezone offset (rarely used)

Characteristics:

Storage: 8 bytes (TIME) or 12 bytes (TIME WITH TIME ZONE) in PostgreSQL
Range: 00:00:00 to 23:59:59.999999 (or 24:00:00 in some systems)
Precision: Typically up to microseconds

TIME is less commonly used because:

Most real-world scenarios involve dates ("meeting at 2 PM" implies a specific date)
Business hours and schedules often use application logic rather than database TIME types
TIME WITH TIME ZONE is paradoxical—a time zone without a date is ambiguous due to daylight saving

time_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- TIME type usage examples
 
CREATE TABLE business_hours (
    day_of_week     INTEGER NOT NULL,         -- 0=Sunday, 6=Saturday
    open_time       TIME NOT NULL,
    close_time      TIME NOT NULL,
    PRIMARY KEY (day_of_week)
);
 
CREATE TABLE class_schedule (
    schedule_id     INTEGER PRIMARY KEY,
    class_name      VARCHAR(100) NOT NULL,
    start_time      TIME NOT NULL,
    end_time        TIME NOT NULL,
    day_of_week     INTEGER NOT NULL          -- 0-6
);
 
CREATE TABLE shift_templates (
    template_id     INTEGER PRIMARY KEY,
    template_name   VARCHAR(50) NOT NULL,
    start_time      TIME NOT NULL,
    end_time        TIME NOT NULL,
    duration_minutes INTEGER GENERATED ALWAYS AS (
        EXTRACT(HOUR FROM end_time - start_time) * 60 +
        EXTRACT(MINUTE FROM end_time - start_time)
    ) STORED
);
 
-- TIME insertion
INSERT INTO business_hours (day_of_week, open_time, close_time)
VALUES 
    (1, '09:00:00', '18:00:00'),  -- Monday
    (2, '09:00:00', '18:00:00'),  -- Tuesday
    (3, '09:00:00', '18:00:00'),  -- Wednesday
    (4, '09:00:00', '18:00:00'),  -- Thursday
    (5, '09:00:00', '17:00:00'),  -- Friday (early close)
    (6, '10:00:00', '14:00:00');  -- Saturday (limited hours)
 
-- TIME with precision
CREATE TABLE race_results (
    result_id       INTEGER PRIMARY KEY,
    runner_name     VARCHAR(100),
    finish_time     TIME(3) NOT NULL          -- Millisecond precision: 01:23:45.678
);
 
INSERT INTO race_results (result_id, runner_name, finish_time)
VALUES (1, 'Usain Bolt', '00:00:09.572');      -- 100m time
 
-- TIME arithmetic
SELECT 
    open_time,
    close_time,
    close_time - open_time AS hours_open,     -- Returns INTERVAL
    EXTRACT(HOUR FROM close_time - open_time) AS hours
FROM business_hours;
 
-- Checking current time against business hours
SELECT * FROM business_hours
WHERE 
    day_of_week = EXTRACT(DOW FROM CURRENT_DATE)
    AND CURRENT_TIME BETWEEN open_time AND close_time;
 
-- TIME comparison
SELECT * FROM class_schedule
WHERE start_time >= '08:00:00' AND start_time < '12:00:00';  -- Morning classes
 
-- Midnight handling: 24:00:00 vs 00:00:00
-- Some databases allow '24:00:00' to represent end of day
-- Equivalent to '00:00:00' of next day conceptually

TIME WITH TIME ZONE Paradox

TIME WITH TIME ZONE is problematic because timezone offset can vary by date (daylight saving). '14:30:00-05:00' in New York means EDT in summer but EST in winter—the same clock time represents different UTC times. Most experts recommend avoiding TIME WITH TIME ZONE entirely.

TIMESTAMP: Combined Date and Time

TIMESTAMP combines date and time into a single value, representing either a specific moment or a local date-time reading. This is the most commonly used temporal type for event recording, logging, and audit trails.

Critical Distinction:

Type	Also Called	Behavior	Use Case
`TIMESTAMP`	TIMESTAMP WITHOUT TIME ZONE	Stores date+time as-is, no timezone conversion	Local events, UI display times
`TIMESTAMP WITH TIME ZONE`	TIMESTAMPTZ (PostgreSQL)	Stores as UTC, converts on input/output	Global events, audit logs, absolute moments

Storage and Precision:

Storage: 8 bytes for both types (PostgreSQL)
Precision: Microseconds (10^-6 seconds) by default
Range: 4713 BC to 294276 AD (PostgreSQL), varies by database

timestamp_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
-- TIMESTAMP vs TIMESTAMP WITH TIME ZONE
 
-- TIMESTAMP (WITHOUT TIME ZONE) - stores exactly what you insert
CREATE TABLE local_events (
    event_id        INTEGER PRIMARY KEY,
    event_name      VARCHAR(200) NOT NULL,
    event_time      TIMESTAMP NOT NULL         -- No timezone awareness
);
 
-- TIMESTAMP WITH TIME ZONE - stores as UTC, converts on retrieval
CREATE TABLE global_events (
    event_id        INTEGER PRIMARY KEY,
    event_name      VARCHAR(200) NOT NULL,
    event_time      TIMESTAMP WITH TIME ZONE NOT NULL  -- Timezone-aware
);
 
-- Insertion behavior comparison
SET TIME ZONE 'America/New_York';  -- Eastern Time
 
INSERT INTO local_events VALUES (1, 'Local Meeting', '2024-03-15 14:00:00');
INSERT INTO global_events VALUES (1, 'Global Meeting', '2024-03-15 14:00:00');
 
-- What's stored:
-- local_events: '2024-03-15 14:00:00' (exactly as inserted)
-- global_events: '2024-03-15 18:00:00+00' (converted to UTC internally)
 
-- Now view from different timezone
SET TIME ZONE 'Asia/Tokyo';  -- Japan Time (UTC+9)
 
SELECT event_time FROM local_events WHERE event_id = 1;
-- Returns: '2024-03-15 14:00:00' (unchanged - still shows 2 PM)
 
SELECT event_time FROM global_events WHERE event_id = 1;
-- Returns: '2024-03-16 03:00:00+09' (converted to local Tokyo time - 3 AM next day)
 
-- The global event represents the SAME INSTANT, displayed in local time
-- The local event shows the SAME CLOCK READING, which may be wrong
 
-- Real-world patterns
 
-- Audit logging: ALWAYS use TIMESTAMP WITH TIME ZONE
CREATE TABLE audit_log (
    log_id          BIGSERIAL PRIMARY KEY,
    user_id         INTEGER NOT NULL,
    action          VARCHAR(100) NOT NULL,
    logged_at       TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
 
-- Order timestamps: Use TIMESTAMP WITH TIME ZONE
CREATE TABLE orders (
    order_id        INTEGER PRIMARY KEY,
    customer_id     INTEGER NOT NULL,
    order_placed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    order_shipped_at TIMESTAMP WITH TIME ZONE,
    order_delivered_at TIMESTAMP WITH TIME ZONE
);
 
-- Scheduled recurring events (local concept): Use TIMESTAMP WITHOUT TIME ZONE
-- BUT store the timezone separately
CREATE TABLE recurring_reminders (
    reminder_id     INTEGER PRIMARY KEY,
    user_id         INTEGER NOT NULL,
    reminder_time   TIMESTAMP NOT NULL,        -- Local time as user entered
    user_timezone   VARCHAR(50) NOT NULL       -- e.g., 'America/New_York'
);

The TIMESTAMP Without Timezone Trap

If your server moves from US-East to Europe and you're using TIMESTAMP (without timezone), all your stored times will be interpreted as European times. An order placed at '2024-03-15 14:00:00' that meant 2 PM New York will now appear as 2 PM London—5 hours different! Use TIMESTAMP WITH TIME ZONE for event times.

TIMESTAMP Selection Guidelines

•Use TIMESTAMP WITH TIME ZONE for: Event logs, audit trails, order timestamps, message timestamps, any 'when did this happen' question
•Use TIMESTAMP (without timezone) for: User-entered future events in their local time (but store timezone separately), calendar displays, theatrical showtimes
•Store all absolute times as UTC internally: Even if displayed in local time, storage in UTC prevents ambiguity
•Always specify timezone in application layer: Don't rely on database session timezone for conversions
•Test timezone edge cases: Daylight saving transitions, users in different timezones, server timezone changes

Timezone Handling Deep Dive

Timezone handling is one of the most subtly complex areas of software development. Understanding how databases handle timezones is essential for correct temporal data management.

Key Concepts:

1. UTC (Coordinated Universal Time)

The global time standard, with no daylight saving adjustments
Also known as GMT (though technically slightly different)
Expressed with +00:00 offset or 'Z' suffix (Zulu time)

2. Timezone Offset

The difference from UTC: +05:30 (India), -05:00 (US Eastern)
Offsets can change based on daylight saving time
An offset alone doesn't tell you if DST is active

3. Named Timezones

IANA timezone database names: 'America/New_York', 'Europe/London'
Encode full historical and future timezone rules including DST
Preferred over offsets for future dates

timezone_handling.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
-- Timezone operations in PostgreSQL
 
-- Current time in different representations
SELECT 
    NOW(),                                     -- Current time with session timezone
    NOW() AT TIME ZONE 'UTC',                  -- Convert to UTC
    NOW() AT TIME ZONE 'America/New_York',     -- Convert to New York
    NOW() AT TIME ZONE 'Asia/Tokyo';           -- Convert to Tokyo
 
-- Input with explicit timezone
INSERT INTO global_events (event_id, event_name, event_time)
VALUES 
    (10, 'NYC Event', '2024-03-15 14:00:00-04'),              -- EDT offset
    (11, 'Tokyo Event', '2024-03-15 14:00:00+09'),            -- JST offset
    (12, 'UTC Event', '2024-03-15 14:00:00+00'),              -- UTC
    (13, 'Named TZ', '2024-03-15 14:00:00 America/Chicago');  -- Named timezone
 
-- All store the equivalent UTC instant and display in session timezone
 
-- Converting between timezones
SELECT 
    event_time,
    event_time AT TIME ZONE 'UTC' AS utc_time,
    event_time AT TIME ZONE 'America/Los_Angeles' AS la_time,
    event_time AT TIME ZONE 'Europe/London' AS london_time
FROM global_events;
 
-- Daylight Saving Time considerations
-- March 10, 2024 at 2:00 AM, US clocks spring forward
-- 2:30 AM on this date DOES NOT EXIST in Eastern Time!
 
-- This might cause issues:
SELECT '2024-03-10 02:30:00'::TIMESTAMP AT TIME ZONE 'America/New_York';
-- Behavior varies: some DBs error, some adjust
 
-- November 3, 2024 at 2:00 AM, US clocks fall back
-- 1:30 AM on this date EXISTS TWICE in Eastern Time!
 
-- Which 1:30 AM do you mean?
SELECT '2024-11-03 01:30:00'::TIMESTAMP AT TIME ZONE 'America/New_York';
-- Ambiguous! Database makes assumption (usually standard time)
 
-- Best practice: Always insert UTC or fully qualified timestamps
INSERT INTO global_events (event_id, event_name, event_time)
VALUES (14, 'DST-Safe Event', '2024-03-10 07:30:00+00');  -- UTC is unambiguous
 
-- Timezone-aware queries
-- Find all events that occur during US business hours (9 AM - 5 PM ET)
SELECT *
FROM global_events
WHERE (event_time AT TIME ZONE 'America/New_York')::TIME 
      BETWEEN '09:00:00' AND '17:00:00';
 
-- Session timezone management
SET TIME ZONE 'UTC';          -- Server-level for session
SHOW TIME ZONE;               -- View current session timezone
 
-- Database default timezone
-- PostgreSQL: Set in postgresql.conf (timezone = 'UTC')
-- Applications should set session timezone explicitly

The UTC-First Principle

Store as UTC, display in local time. This simple rule prevents most timezone bugs. Convert to local time only at the presentation layer. Your database, servers, and APIs should think in UTC; only the user interface shows local time.

Common Timezone Pitfalls

•DST transitions: 2:30 AM doesn't exist on spring-forward; 1:30 AM exists twice on fall-back
•Offset vs. name: +05:30 is always the same offset; 'America/New_York' varies between -05:00 and -04:00
•Historical rules: Timezone rules change; Russia has changed DST rules multiple times; named timezones handle this, offsets don't
•Server timezone changes: If your DB server moves or restores to a different timezone, TIMESTAMP WITHOUT TIME ZONE values are reinterpreted
•Implicit conversions: Some databases implicitly convert; others don't. Always be explicit.

INTERVAL Type: Durations and Periods

The INTERVAL type represents a duration of time—a span rather than a point. Intervals can express years, months, days, hours, minutes, seconds, and fractional seconds.

Interval Components:

INTERVAL '1 year 2 months 3 days 4 hours 5 minutes 6 seconds'
├── Year-Month component: 1 year, 2 months
└── Day-Time component: 3 days, 4:05:06

Important Subtlety:

Month lengths vary (28-31 days)
Year lengths vary (365-366 days)
Therefore, '1 month' ≠ '30 days' exactly
INTERVAL arithmetic respects calendar semantics

interval_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
-- INTERVAL type usage
 
-- Creating intervals
SELECT 
    INTERVAL '1 day',
    INTERVAL '2 hours 30 minutes',
    INTERVAL '1 year 6 months',
    INTERVAL '90 days',
    INTERVAL '1.5 hours',               -- Fractional hours
    INTERVAL '1 day 12:30:00';          -- Mixed day and time
 
-- Interval arithmetic with dates
SELECT 
    CURRENT_DATE + INTERVAL '30 days' AS thirty_days_later,
    CURRENT_DATE - INTERVAL '1 month' AS one_month_ago,
    CURRENT_TIMESTAMP + INTERVAL '2 hours' AS two_hours_later;
 
-- Subtracting timestamps yields an interval
SELECT 
    order_delivered_at - order_placed_at AS delivery_duration
FROM orders
WHERE order_delivered_at IS NOT NULL;
 
-- Subscription and expiration examples
CREATE TABLE subscriptions (
    subscription_id INTEGER PRIMARY KEY,
    user_id         INTEGER NOT NULL,
    plan_name       VARCHAR(50) NOT NULL,
    duration        INTERVAL NOT NULL,
    started_at      TIMESTAMP WITH TIME ZONE NOT NULL,
    expires_at      TIMESTAMP WITH TIME ZONE 
        GENERATED ALWAYS AS (started_at + duration) STORED
);
 
INSERT INTO subscriptions (subscription_id, user_id, plan_name, duration, started_at)
VALUES 
    (1, 100, 'Monthly', INTERVAL '1 month', NOW()),
    (2, 101, 'Annual', INTERVAL '1 year', NOW()),
    (3, 102, 'Weekly', INTERVAL '7 days', NOW());
 
-- Trial period calculations
CREATE TABLE trials (
    trial_id        INTEGER PRIMARY KEY,
    user_id         INTEGER NOT NULL,
    trial_start     TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    trial_duration  INTERVAL DEFAULT '14 days',
    trial_end       TIMESTAMP WITH TIME ZONE 
        GENERATED ALWAYS AS (trial_start + trial_duration) STORED
);
 
-- Age and duration formatting
SELECT 
    first_name,
    hire_date,
    AGE(CURRENT_DATE, hire_date) AS tenure,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, hire_date)) AS years_employed,
    EXTRACT(MONTH FROM AGE(CURRENT_DATE, hire_date)) AS additional_months
FROM employees;
 
-- Interval extraction
SELECT 
    INTERVAL '3 days 4 hours 5 minutes' AS original,
    EXTRACT(DAY FROM INTERVAL '3 days 4 hours 5 minutes') AS days,
    EXTRACT(HOUR FROM INTERVAL '3 days 4 hours 5 minutes') AS hours,
    EXTRACT(MINUTE FROM INTERVAL '3 days 4 hours 5 minutes') AS minutes,
    EXTRACT(EPOCH FROM INTERVAL '3 days 4 hours 5 minutes') AS total_seconds;
 
-- Interval comparison
SELECT * FROM subscriptions
WHERE duration >= INTERVAL '30 days';
 
-- Scheduling: events occurring every interval
CREATE TABLE scheduled_jobs (
    job_id          INTEGER PRIMARY KEY,
    job_name        VARCHAR(100) NOT NULL,
    run_interval    INTERVAL NOT NULL,
    last_run        TIMESTAMP WITH TIME ZONE,
    next_run        TIMESTAMP WITH TIME ZONE 
        GENERATED ALWAYS AS (last_run + run_interval) STORED
);
 
INSERT INTO scheduled_jobs (job_id, job_name, run_interval, last_run)
VALUES 
    (1, 'Hourly Report', INTERVAL '1 hour', NOW()),
    (2, 'Daily Cleanup', INTERVAL '1 day', NOW()),
    (3, 'Weekly Digest', INTERVAL '1 week', NOW());

Month Interval Semantics

Adding INTERVAL '1 month' to January 31 yields February 28 (or 29), not March 2. This is calendar-aware arithmetic. If you need exact 30-day periods, use INTERVAL '30 days' instead. The choice affects billing cycles, trial periods, and scheduling.

Precision and Vendor Variations

Temporal type precision and behavior vary significantly across database systems. Understanding these differences is crucial for cross-platform development and migrations.

•TIMESTAMP(p): 0-6 fractional digits (microseconds max)
•TIMESTAMPTZ: Alias for TIMESTAMP WITH TIME ZONE
•Storage: 8 bytes for both TIMESTAMP variants
•Range: 4713 BC to 294276 AD
•INTERVAL: Full year-month and day-time support
•NOW(): Returns TIMESTAMP WITH TIME ZONE
•Best Practice: PostgreSQL's temporal handling is considered gold standard

1
2
3
4
5
6
7
8
9
10
-- PostgreSQL examples
SELECT 
    NOW()::TIMESTAMP(0),                -- Truncate to seconds
    NOW()::TIMESTAMP(3),                -- Milliseconds
    NOW()::TIMESTAMP(6);                -- Microseconds (default)
 
-- TIMESTAMPTZ is the same as TIMESTAMP WITH TIME ZONE
CREATE TABLE events (
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Summary and Best Practices

Temporal data handling is subtle and error-prone. These principles will help you design robust temporal schemas:

Key Takeaways

•Use TIMESTAMP WITH TIME ZONE for events — Order times, log entries, audit timestamps. These are absolute moments that should be unambiguous.
•Use DATE for calendar concepts — Birthdays, holidays, due dates. These don't have time components and shouldn't shift with timezone.
•Store as UTC, display as local — All event timestamps should be stored in UTC. Convert to local time only in the presentation layer.
•Use named timezones, not offsets — 'America/New_York' handles DST; '-05:00' doesn't. Store timezone names for future events.
•Understand month semantics — INTERVAL '1 month' is calendar-aware. January 31 + 1 month = February 28, not March 3.
•Know your database's quirks — MySQL TIMESTAMP has 2038 limit; SQL Server TIMESTAMP isn't temporal; Oracle DATE includes time.

Quick Reference: Temporal Type Selection
Data Domain	Recommended Type	Key Consideration
Event timestamps	TIMESTAMP WITH TIME ZONE	Always use for 'when did this happen'
Birthdates	DATE	Calendar concept, no timezone
Due dates	DATE	Calendar day deadline
Business hours	TIME	Time of day only
Subscription duration	INTERVAL	Duration between events
User's scheduled event	TIMESTAMP + timezone column	Store local time + TZ separately
Audit created_at	TIMESTAMPTZ DEFAULT NOW()	Automatic UTC capture

Page Complete

You now understand the complete landscape of SQL temporal types, from basic DATE usage through complex timezone handling. Next, we'll explore the Boolean type—simpler but with its own cross-database considerations.

3 / 5

Loading learning content...

SQLData Types

SQL Data Types

LevelBeginner

Duration60 mins

TopicData Types

3 / 5

Date/Time Types

The Complexity of Time

This page provides exhaustive coverage of SQL date and time types, timezone handling, precision considerations, and the principles that separate robust temporal design from bug-prone implementations.

What You Will Learn

The Temporal Type Taxonomy

SQL defines several temporal types, each capturing different aspects of time:

Core Temporal Types:

SQL Temporal Type Overview
Type	Stores	Example	Use Case
DATE	Calendar date only	2024-03-15	Birthdays, holidays, due dates
TIME	Time of day only	14:30:00	Business hours, schedules
TIMESTAMP	Date + time	2024-03-15 14:30:00	Event timestamps, logs
TIMESTAMP WITH TIME ZONE	Date + time + timezone	2024-03-15 14:30:00+05:30	Global events, audit logs
INTERVAL	Duration/period	3 days 4 hours	Time differences, durations

The Fundamental Distinction: Point in Time vs. Calendar/Clock Reading

This distinction is crucial:

Absolute time (instant): A specific moment in the history of the universe. "The server crashed at this instant." Requires timezone context to be unambiguous.
Local/civil time: A calendar date and clock reading without timezone. "The meeting is at 2:00 PM." Interpretation depends on assumed context.

Using the wrong type causes bugs: storing a user's birthday as TIMESTAMP WITH TIME ZONE makes their birthday shift when viewed from different timezones!

The Most Common Temporal Bug

DATE Type: Calendar Dates

The DATE type stores a calendar date (year, month, day) without time components. It represents a full day rather than a specific instant.

Characteristics:

Storage: Typically 4 bytes
Range: Varies by implementation (commonly 0001-01-01 to 9999-12-31)
No time component, no timezone
Represents a calendar day, not a 24-hour period

DATE is ideal for:

Birthdates (a birthday is March 15, regardless of timezone)
Holidays and calendar events
Due dates and deadlines
Historic dates without time precision
Scheduling tied to calendar days rather than specific times

date_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
-- DATE type usage examples
 
CREATE TABLE employees (
    employee_id     INTEGER PRIMARY KEY,
    first_name      VARCHAR(50) NOT NULL,
    last_name       VARCHAR(50) NOT NULL,
    birth_date      DATE NOT NULL,            -- Calendar date only
    hire_date       DATE NOT NULL,
    termination_date DATE                     -- NULL if still employed
);
 
CREATE TABLE holidays (
    holiday_id      INTEGER PRIMARY KEY,
    holiday_name    VARCHAR(100) NOT NULL,
    holiday_date    DATE NOT NULL,            -- Dec 25, 2024 (independent of timezone)
    is_paid         BOOLEAN DEFAULT TRUE
);
 
CREATE TABLE invoices (
    invoice_id      INTEGER PRIMARY KEY,
    invoice_date    DATE NOT NULL,            -- Date invoice was issued
    due_date        DATE NOT NULL,            -- Payment deadline
    customer_id     INTEGER NOT NULL
);
 
-- Date literals and insertion
INSERT INTO employees (employee_id, first_name, last_name, birth_date, hire_date)
VALUES (1, 'Alice', 'Johnson', '1990-05-20', '2020-03-15');
 
-- Standard ISO 8601 format: YYYY-MM-DD
INSERT INTO holidays (holiday_id, holiday_name, holiday_date)
VALUES (1, 'Christmas Day', '2024-12-25');
 
-- Date extraction functions
SELECT 
    birth_date,
    EXTRACT(YEAR FROM birth_date) AS birth_year,
    EXTRACT(MONTH FROM birth_date) AS birth_month,
    EXTRACT(DAY FROM birth_date) AS birth_day,
    EXTRACT(DOW FROM birth_date) AS day_of_week  -- 0=Sunday, 6=Saturday
FROM employees;
 
-- Date arithmetic
SELECT 
    hire_date,
    hire_date + INTERVAL '30 days' AS probation_end,
    hire_date + INTERVAL '1 year' AS first_anniversary,
    CURRENT_DATE - hire_date AS days_employed
FROM employees;
 
-- Date comparisons
SELECT * FROM invoices
WHERE due_date < CURRENT_DATE;  -- Overdue invoices
 
SELECT * FROM employees
WHERE hire_date BETWEEN '2020-01-01' AND '2020-12-31';  -- Hired in 2020
 
-- Age calculation
SELECT 
    first_name,
    birth_date,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) AS age
FROM employees;
 
-- First/last day of month
SELECT 
    invoice_date,
    DATE_TRUNC('month', invoice_date) AS first_of_month,
    DATE_TRUNC('month', invoice_date) + INTERVAL '1 month' - INTERVAL '1 day' AS last_of_month
FROM invoices;

Birthdays and Timezone Independence

TIME Type: Time of Day

The TIME type stores a time of day (hours, minutes, seconds, and optionally fractional seconds) without a date component.

Variants:

TIME or TIME WITHOUT TIME ZONE: Local time reading (e.g., 14:30:00)
TIME WITH TIME ZONE: Time with timezone offset (rarely used)

Characteristics:

Storage: 8 bytes (TIME) or 12 bytes (TIME WITH TIME ZONE) in PostgreSQL
Range: 00:00:00 to 23:59:59.999999 (or 24:00:00 in some systems)
Precision: Typically up to microseconds

TIME is less commonly used because:

Most real-world scenarios involve dates ("meeting at 2 PM" implies a specific date)
Business hours and schedules often use application logic rather than database TIME types
TIME WITH TIME ZONE is paradoxical—a time zone without a date is ambiguous due to daylight saving

time_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- TIME type usage examples
 
CREATE TABLE business_hours (
    day_of_week     INTEGER NOT NULL,         -- 0=Sunday, 6=Saturday
    open_time       TIME NOT NULL,
    close_time      TIME NOT NULL,
    PRIMARY KEY (day_of_week)
);
 
CREATE TABLE class_schedule (
    schedule_id     INTEGER PRIMARY KEY,
    class_name      VARCHAR(100) NOT NULL,
    start_time      TIME NOT NULL,
    end_time        TIME NOT NULL,
    day_of_week     INTEGER NOT NULL          -- 0-6
);
 
CREATE TABLE shift_templates (
    template_id     INTEGER PRIMARY KEY,
    template_name   VARCHAR(50) NOT NULL,
    start_time      TIME NOT NULL,
    end_time        TIME NOT NULL,
    duration_minutes INTEGER GENERATED ALWAYS AS (
        EXTRACT(HOUR FROM end_time - start_time) * 60 +
        EXTRACT(MINUTE FROM end_time - start_time)
    ) STORED
);
 
-- TIME insertion
INSERT INTO business_hours (day_of_week, open_time, close_time)
VALUES 
    (1, '09:00:00', '18:00:00'),  -- Monday
    (2, '09:00:00', '18:00:00'),  -- Tuesday
    (3, '09:00:00', '18:00:00'),  -- Wednesday
    (4, '09:00:00', '18:00:00'),  -- Thursday
    (5, '09:00:00', '17:00:00'),  -- Friday (early close)
    (6, '10:00:00', '14:00:00');  -- Saturday (limited hours)
 
-- TIME with precision
CREATE TABLE race_results (
    result_id       INTEGER PRIMARY KEY,
    runner_name     VARCHAR(100),
    finish_time     TIME(3) NOT NULL          -- Millisecond precision: 01:23:45.678
);
 
INSERT INTO race_results (result_id, runner_name, finish_time)
VALUES (1, 'Usain Bolt', '00:00:09.572');      -- 100m time
 
-- TIME arithmetic
SELECT 
    open_time,
    close_time,
    close_time - open_time AS hours_open,     -- Returns INTERVAL
    EXTRACT(HOUR FROM close_time - open_time) AS hours
FROM business_hours;
 
-- Checking current time against business hours
SELECT * FROM business_hours
WHERE 
    day_of_week = EXTRACT(DOW FROM CURRENT_DATE)
    AND CURRENT_TIME BETWEEN open_time AND close_time;
 
-- TIME comparison
SELECT * FROM class_schedule
WHERE start_time >= '08:00:00' AND start_time < '12:00:00';  -- Morning classes
 
-- Midnight handling: 24:00:00 vs 00:00:00
-- Some databases allow '24:00:00' to represent end of day
-- Equivalent to '00:00:00' of next day conceptually

TIME WITH TIME ZONE Paradox

TIMESTAMP: Combined Date and Time

Critical Distinction:

Type	Also Called	Behavior	Use Case
`TIMESTAMP`	TIMESTAMP WITHOUT TIME ZONE	Stores date+time as-is, no timezone conversion	Local events, UI display times
`TIMESTAMP WITH TIME ZONE`	TIMESTAMPTZ (PostgreSQL)	Stores as UTC, converts on input/output	Global events, audit logs, absolute moments

Storage and Precision:

Storage: 8 bytes for both types (PostgreSQL)
Precision: Microseconds (10^-6 seconds) by default
Range: 4713 BC to 294276 AD (PostgreSQL), varies by database

timestamp_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
-- TIMESTAMP vs TIMESTAMP WITH TIME ZONE
 
-- TIMESTAMP (WITHOUT TIME ZONE) - stores exactly what you insert
CREATE TABLE local_events (
    event_id        INTEGER PRIMARY KEY,
    event_name      VARCHAR(200) NOT NULL,
    event_time      TIMESTAMP NOT NULL         -- No timezone awareness
);
 
-- TIMESTAMP WITH TIME ZONE - stores as UTC, converts on retrieval
CREATE TABLE global_events (
    event_id        INTEGER PRIMARY KEY,
    event_name      VARCHAR(200) NOT NULL,
    event_time      TIMESTAMP WITH TIME ZONE NOT NULL  -- Timezone-aware
);
 
-- Insertion behavior comparison
SET TIME ZONE 'America/New_York';  -- Eastern Time
 
INSERT INTO local_events VALUES (1, 'Local Meeting', '2024-03-15 14:00:00');
INSERT INTO global_events VALUES (1, 'Global Meeting', '2024-03-15 14:00:00');
 
-- What's stored:
-- local_events: '2024-03-15 14:00:00' (exactly as inserted)
-- global_events: '2024-03-15 18:00:00+00' (converted to UTC internally)
 
-- Now view from different timezone
SET TIME ZONE 'Asia/Tokyo';  -- Japan Time (UTC+9)
 
SELECT event_time FROM local_events WHERE event_id = 1;
-- Returns: '2024-03-15 14:00:00' (unchanged - still shows 2 PM)
 
SELECT event_time FROM global_events WHERE event_id = 1;
-- Returns: '2024-03-16 03:00:00+09' (converted to local Tokyo time - 3 AM next day)
 
-- The global event represents the SAME INSTANT, displayed in local time
-- The local event shows the SAME CLOCK READING, which may be wrong
 
-- Real-world patterns
 
-- Audit logging: ALWAYS use TIMESTAMP WITH TIME ZONE
CREATE TABLE audit_log (
    log_id          BIGSERIAL PRIMARY KEY,
    user_id         INTEGER NOT NULL,
    action          VARCHAR(100) NOT NULL,
    logged_at       TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
 
-- Order timestamps: Use TIMESTAMP WITH TIME ZONE
CREATE TABLE orders (
    order_id        INTEGER PRIMARY KEY,
    customer_id     INTEGER NOT NULL,
    order_placed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    order_shipped_at TIMESTAMP WITH TIME ZONE,
    order_delivered_at TIMESTAMP WITH TIME ZONE
);
 
-- Scheduled recurring events (local concept): Use TIMESTAMP WITHOUT TIME ZONE
-- BUT store the timezone separately
CREATE TABLE recurring_reminders (
    reminder_id     INTEGER PRIMARY KEY,
    user_id         INTEGER NOT NULL,
    reminder_time   TIMESTAMP NOT NULL,        -- Local time as user entered
    user_timezone   VARCHAR(50) NOT NULL       -- e.g., 'America/New_York'
);

The TIMESTAMP Without Timezone Trap

TIMESTAMP Selection Guidelines

•Use TIMESTAMP WITH TIME ZONE for: Event logs, audit trails, order timestamps, message timestamps, any 'when did this happen' question
•Use TIMESTAMP (without timezone) for: User-entered future events in their local time (but store timezone separately), calendar displays, theatrical showtimes
•Store all absolute times as UTC internally: Even if displayed in local time, storage in UTC prevents ambiguity
•Always specify timezone in application layer: Don't rely on database session timezone for conversions
•Test timezone edge cases: Daylight saving transitions, users in different timezones, server timezone changes

Timezone Handling Deep Dive

Timezone handling is one of the most subtly complex areas of software development. Understanding how databases handle timezones is essential for correct temporal data management.

Key Concepts:

1. UTC (Coordinated Universal Time)

The global time standard, with no daylight saving adjustments
Also known as GMT (though technically slightly different)
Expressed with +00:00 offset or 'Z' suffix (Zulu time)

2. Timezone Offset

The difference from UTC: +05:30 (India), -05:00 (US Eastern)
Offsets can change based on daylight saving time
An offset alone doesn't tell you if DST is active

3. Named Timezones

IANA timezone database names: 'America/New_York', 'Europe/London'
Encode full historical and future timezone rules including DST
Preferred over offsets for future dates

timezone_handling.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
-- Timezone operations in PostgreSQL
 
-- Current time in different representations
SELECT 
    NOW(),                                     -- Current time with session timezone
    NOW() AT TIME ZONE 'UTC',                  -- Convert to UTC
    NOW() AT TIME ZONE 'America/New_York',     -- Convert to New York
    NOW() AT TIME ZONE 'Asia/Tokyo';           -- Convert to Tokyo
 
-- Input with explicit timezone
INSERT INTO global_events (event_id, event_name, event_time)
VALUES 
    (10, 'NYC Event', '2024-03-15 14:00:00-04'),              -- EDT offset
    (11, 'Tokyo Event', '2024-03-15 14:00:00+09'),            -- JST offset
    (12, 'UTC Event', '2024-03-15 14:00:00+00'),              -- UTC
    (13, 'Named TZ', '2024-03-15 14:00:00 America/Chicago');  -- Named timezone
 
-- All store the equivalent UTC instant and display in session timezone
 
-- Converting between timezones
SELECT 
    event_time,
    event_time AT TIME ZONE 'UTC' AS utc_time,
    event_time AT TIME ZONE 'America/Los_Angeles' AS la_time,
    event_time AT TIME ZONE 'Europe/London' AS london_time
FROM global_events;
 
-- Daylight Saving Time considerations
-- March 10, 2024 at 2:00 AM, US clocks spring forward
-- 2:30 AM on this date DOES NOT EXIST in Eastern Time!
 
-- This might cause issues:
SELECT '2024-03-10 02:30:00'::TIMESTAMP AT TIME ZONE 'America/New_York';
-- Behavior varies: some DBs error, some adjust
 
-- November 3, 2024 at 2:00 AM, US clocks fall back
-- 1:30 AM on this date EXISTS TWICE in Eastern Time!
 
-- Which 1:30 AM do you mean?
SELECT '2024-11-03 01:30:00'::TIMESTAMP AT TIME ZONE 'America/New_York';
-- Ambiguous! Database makes assumption (usually standard time)
 
-- Best practice: Always insert UTC or fully qualified timestamps
INSERT INTO global_events (event_id, event_name, event_time)
VALUES (14, 'DST-Safe Event', '2024-03-10 07:30:00+00');  -- UTC is unambiguous
 
-- Timezone-aware queries
-- Find all events that occur during US business hours (9 AM - 5 PM ET)
SELECT *
FROM global_events
WHERE (event_time AT TIME ZONE 'America/New_York')::TIME 
      BETWEEN '09:00:00' AND '17:00:00';
 
-- Session timezone management
SET TIME ZONE 'UTC';          -- Server-level for session
SHOW TIME ZONE;               -- View current session timezone
 
-- Database default timezone
-- PostgreSQL: Set in postgresql.conf (timezone = 'UTC')
-- Applications should set session timezone explicitly

The UTC-First Principle

Common Timezone Pitfalls

•DST transitions: 2:30 AM doesn't exist on spring-forward; 1:30 AM exists twice on fall-back
•Offset vs. name: +05:30 is always the same offset; 'America/New_York' varies between -05:00 and -04:00
•Historical rules: Timezone rules change; Russia has changed DST rules multiple times; named timezones handle this, offsets don't
•Server timezone changes: If your DB server moves or restores to a different timezone, TIMESTAMP WITHOUT TIME ZONE values are reinterpreted
•Implicit conversions: Some databases implicitly convert; others don't. Always be explicit.

INTERVAL Type: Durations and Periods

The INTERVAL type represents a duration of time—a span rather than a point. Intervals can express years, months, days, hours, minutes, seconds, and fractional seconds.

Interval Components:

INTERVAL '1 year 2 months 3 days 4 hours 5 minutes 6 seconds'
├── Year-Month component: 1 year, 2 months
└── Day-Time component: 3 days, 4:05:06

Important Subtlety:

Month lengths vary (28-31 days)
Year lengths vary (365-366 days)
Therefore, '1 month' ≠ '30 days' exactly
INTERVAL arithmetic respects calendar semantics

interval_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
-- INTERVAL type usage
 
-- Creating intervals
SELECT 
    INTERVAL '1 day',
    INTERVAL '2 hours 30 minutes',
    INTERVAL '1 year 6 months',
    INTERVAL '90 days',
    INTERVAL '1.5 hours',               -- Fractional hours
    INTERVAL '1 day 12:30:00';          -- Mixed day and time
 
-- Interval arithmetic with dates
SELECT 
    CURRENT_DATE + INTERVAL '30 days' AS thirty_days_later,
    CURRENT_DATE - INTERVAL '1 month' AS one_month_ago,
    CURRENT_TIMESTAMP + INTERVAL '2 hours' AS two_hours_later;
 
-- Subtracting timestamps yields an interval
SELECT 
    order_delivered_at - order_placed_at AS delivery_duration
FROM orders
WHERE order_delivered_at IS NOT NULL;
 
-- Subscription and expiration examples
CREATE TABLE subscriptions (
    subscription_id INTEGER PRIMARY KEY,
    user_id         INTEGER NOT NULL,
    plan_name       VARCHAR(50) NOT NULL,
    duration        INTERVAL NOT NULL,
    started_at      TIMESTAMP WITH TIME ZONE NOT NULL,
    expires_at      TIMESTAMP WITH TIME ZONE 
        GENERATED ALWAYS AS (started_at + duration) STORED
);
 
INSERT INTO subscriptions (subscription_id, user_id, plan_name, duration, started_at)
VALUES 
    (1, 100, 'Monthly', INTERVAL '1 month', NOW()),
    (2, 101, 'Annual', INTERVAL '1 year', NOW()),
    (3, 102, 'Weekly', INTERVAL '7 days', NOW());
 
-- Trial period calculations
CREATE TABLE trials (
    trial_id        INTEGER PRIMARY KEY,
    user_id         INTEGER NOT NULL,
    trial_start     TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    trial_duration  INTERVAL DEFAULT '14 days',
    trial_end       TIMESTAMP WITH TIME ZONE 
        GENERATED ALWAYS AS (trial_start + trial_duration) STORED
);
 
-- Age and duration formatting
SELECT 
    first_name,
    hire_date,
    AGE(CURRENT_DATE, hire_date) AS tenure,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, hire_date)) AS years_employed,
    EXTRACT(MONTH FROM AGE(CURRENT_DATE, hire_date)) AS additional_months
FROM employees;
 
-- Interval extraction
SELECT 
    INTERVAL '3 days 4 hours 5 minutes' AS original,
    EXTRACT(DAY FROM INTERVAL '3 days 4 hours 5 minutes') AS days,
    EXTRACT(HOUR FROM INTERVAL '3 days 4 hours 5 minutes') AS hours,
    EXTRACT(MINUTE FROM INTERVAL '3 days 4 hours 5 minutes') AS minutes,
    EXTRACT(EPOCH FROM INTERVAL '3 days 4 hours 5 minutes') AS total_seconds;
 
-- Interval comparison
SELECT * FROM subscriptions
WHERE duration >= INTERVAL '30 days';
 
-- Scheduling: events occurring every interval
CREATE TABLE scheduled_jobs (
    job_id          INTEGER PRIMARY KEY,
    job_name        VARCHAR(100) NOT NULL,
    run_interval    INTERVAL NOT NULL,
    last_run        TIMESTAMP WITH TIME ZONE,
    next_run        TIMESTAMP WITH TIME ZONE 
        GENERATED ALWAYS AS (last_run + run_interval) STORED
);
 
INSERT INTO scheduled_jobs (job_id, job_name, run_interval, last_run)
VALUES 
    (1, 'Hourly Report', INTERVAL '1 hour', NOW()),
    (2, 'Daily Cleanup', INTERVAL '1 day', NOW()),
    (3, 'Weekly Digest', INTERVAL '1 week', NOW());

Month Interval Semantics

Precision and Vendor Variations

Temporal type precision and behavior vary significantly across database systems. Understanding these differences is crucial for cross-platform development and migrations.

•TIMESTAMP(p): 0-6 fractional digits (microseconds max)
•TIMESTAMPTZ: Alias for TIMESTAMP WITH TIME ZONE
•Storage: 8 bytes for both TIMESTAMP variants
•Range: 4713 BC to 294276 AD
•INTERVAL: Full year-month and day-time support
•NOW(): Returns TIMESTAMP WITH TIME ZONE
•Best Practice: PostgreSQL's temporal handling is considered gold standard

1
2
3
4
5
6
7
8
9
10
-- PostgreSQL examples
SELECT 
    NOW()::TIMESTAMP(0),                -- Truncate to seconds
    NOW()::TIMESTAMP(3),                -- Milliseconds
    NOW()::TIMESTAMP(6);                -- Microseconds (default)
 
-- TIMESTAMPTZ is the same as TIMESTAMP WITH TIME ZONE
CREATE TABLE events (
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Summary and Best Practices

Temporal data handling is subtle and error-prone. These principles will help you design robust temporal schemas:

Key Takeaways

•Use TIMESTAMP WITH TIME ZONE for events — Order times, log entries, audit timestamps. These are absolute moments that should be unambiguous.
•Use DATE for calendar concepts — Birthdays, holidays, due dates. These don't have time components and shouldn't shift with timezone.
•Store as UTC, display as local — All event timestamps should be stored in UTC. Convert to local time only in the presentation layer.
•Use named timezones, not offsets — 'America/New_York' handles DST; '-05:00' doesn't. Store timezone names for future events.
•Understand month semantics — INTERVAL '1 month' is calendar-aware. January 31 + 1 month = February 28, not March 3.
•Know your database's quirks — MySQL TIMESTAMP has 2038 limit; SQL Server TIMESTAMP isn't temporal; Oracle DATE includes time.

Quick Reference: Temporal Type Selection
Data Domain	Recommended Type	Key Consideration
Event timestamps	TIMESTAMP WITH TIME ZONE	Always use for 'when did this happen'
Birthdates	DATE	Calendar concept, no timezone
Due dates	DATE	Calendar day deadline
Business hours	TIME	Time of day only
Subscription duration	INTERVAL	Duration between events
User's scheduled event	TIMESTAMP + timezone column	Store local time + TZ separately
Audit created_at	TIMESTAMPTZ DEFAULT NOW()	Automatic UTC capture

Page Complete

3 / 5