Date and Time Data Types in SQL
You'll learn how SQL stores and represents temporal information using specialized data types: DATE, TIME, TIMESTAMP, and INTERVAL.
Why Temporal Data Types Matter
Databases don't store dates and times as simple text strings like "January 15, 2024" — they use specialized data types that enable accurate arithmetic, comparisons, and timezone handling. Understanding these types is crucial for analyzing time-series data, calculating durations, and filtering by date ranges.
The Four Core Temporal Types
DATE stores calendar dates without time information (e.g., 2024-01-15). Think of it as a calendar page — year, month, and day only.
TIME stores time-of-day without date information (e.g., 14:30:00). It's like a clock face showing hours, minutes, and seconds.
TIMESTAMP (or DATETIME in some databases) combines both date and time into a single value (e.g., 2024-01-15 14:30:00). This is your complete moment-in-time representation, often with timezone awareness.
INTERVAL represents a duration or time span (e.g., 5 days, 3 hours 20 minutes). Unlike the others, it doesn't point to a specific moment — it measures elapsed time.
How Databases Store Temporal Data
Internally, databases typically store these as integers or binary values representing offsets from a reference point (like Unix epoch: January 1, 1970). This allows efficient storage, indexing, and arithmetic operations. For example, calculating the difference between two TIMESTAMP values yields an INTERVAL.
Different database systems may have slight variations in naming (DATETIME vs TIMESTAMP) and precision (milliseconds vs microseconds), but the core concepts remain consistent.
Key Takeaway: SQL's temporal data types — DATE, TIME, TIMESTAMP, and INTERVAL — provide structured ways to store and manipulate temporal information, enabling accurate date arithmetic and comparisons beyond what simple text strings could offer.