SQL.What is the difference between DATE, TIME, TIMESTAMP, and TIMESTAMPTZ, and which one should be used for business events?

Short, interview-ready answer

DATE stores a calendar day, TIME stores a time of day, TIMESTAMP stores a date and time without timezone, and TIMESTAMPTZ stores an absolute moment in time.
For business events, TIMESTAMPTZ should almost always be used.

---

1️⃣ DATE

What it stores

• Calendar date only
• No time, no timezone

DATE '2026-01-28'

Use cases

• Birthdays
• Due dates
• Business days
• Holidays

Not for

❌ Events with ordering or exact time

2️⃣ TIME

What it stores

• Time of day
• No date, no timezone

TIME '14:30:00'

Use cases

• Store opening hours
• Schedules (“opens at 9:00”)

Not for

❌ Events
❌ Logging
❌ Auditing

3️⃣ TIMESTAMP (without timezone)

What it stores

• Date + time
• No timezone information
• Interpreted in session timezone

TIMESTAMP '2026-01-28 14:30:00'

The trap

• Ambiguous across timezones
• DST changes break assumptions

Example:

"2026-03-29 02:30"  -- may not exist in some timezones

Use cases

• Local wall-clock time
• Legacy systems
• Non-global apps (rare today)

4️⃣ TIMESTAMPTZ (timestamp with time zone)

What it really is (important!)

An absolute point in time stored internally in UTC.

• Timezone normalized on write
• Converted on read

TIMESTAMPTZ '2026-01-28 14:30:00+03'

Stored as:

2026-01-28 11:30:00 UTC

Use cases

✅ Business events
✅ Logs
✅ Audits
✅ Payments
✅ Distributed systems

Type	Date	Time	Timezone	Meaning
DATE	✅	❌	❌	Calendar day
TIME	❌	✅	❌	Time of day
TIMESTAMP	✅	✅	❌	Local datetime
TIMESTAMPTZ	✅	✅	✅	Absolute instant

6️⃣ Which one for business events?

✅ Correct answer

Use TIMESTAMPTZ.

Why:

• Business events happen at a real moment
• Systems are distributed
• Users are in different timezones
• DST exists

---

Example (correct modeling)

created_at TIMESTAMPTZ NOT NULL DEFAULT now()

What about reporting by day?

Still use TIMESTAMPTZ, then derive:

DATE(created_at AT TIME ZONE 'Europe/Berlin')

Never store the derived value as truth.

7️⃣ Interview-ready final answer (clean)

“DATE and TIME store partial temporal information, TIMESTAMP stores a local date-time without timezone, and TIMESTAMPTZ represents an absolute instant normalized to UTC.
For business events and distributed systems, TIMESTAMPTZ is the correct choice because it avoids ambiguity and timezone-related bugs.”

---

Senior red flags 🚩

If someone says:

• “TIMESTAMP is fine everywhere”
• “We store local time and convert later”
• “Timezone is frontend problem”