SQL Date and Time Data Types
Introduction
Temporal data describes calendar dates, clock times, moments, schedules, and durations. SQL provides specialized date and time types so databases can validate values, perform calendar arithmetic, sort chronologically, filter efficiently, and exchange typed values with applications. Correct temporal design matters because time zones, daylight-saving transitions, leap years, month lengths, precision, and product differences make time more complex than a formatted string.
The first design question is semantic: does the value mean a date on a calendar, a time of day, a local date and time, one global instant, a recurring schedule, or an elapsed duration? Those concepts may look similar on screen but require different representations. A birthday should not shift when viewed in another time zone, while a payment event should represent the same instant everywhere.
What Is Temporal Data?
Temporal data associates facts with time. It includes birth dates, store opening times, order timestamps, appointment schedules, validity periods, expiration moments, and measured durations. Some values describe human calendars, while others identify a point on a global timeline.
A database type should preserve that meaning. Storing every temporal value as one generic timestamp can be as misleading as storing every number as text. The type, column name, time-zone policy, precision, and constraints together form the temporal contract.
Why Dates Should Not Be VARCHAR
Text columns accept impossible dates, ambiguous orders such as 03/04/2026, inconsistent separators, and unpadded components. Lexical sorting works chronologically only for carefully normalized formats, and every arithmetic or comparison operation requires parsing.
Native temporal types enforce calendar validity and support date functions and indexes. Formatting belongs at input and presentation boundaries, while the database stores a typed value.
Main SQL Temporal Types
| Concept | Common SQL type | Example meaning |
|---|---|---|
| Calendar date | DATE | Birth date or business day |
| Time of day | TIME | Store opens at 09:00 |
| Local date and time | DATETIME or timestamp without zone | Wall-clock appointment |
| Global instant | Zone-aware timestamp or UTC convention | Payment received moment |
| Elapsed or calendar amount | INTERVAL or documented numeric unit | Two hours or three months |
Type names and exact semantics differ among databases, especially for TIMESTAMP and time-zone support. Always interpret a declaration in its product context.
DATE for Calendar Values
DATE stores a calendar date without a time of day. It is appropriate for birthdays, holidays, accounting dates, due dates, and other values whose meaning is the same calendar label regardless of viewer location.
CREATE TABLE customers (
customer_id BIGINT PRIMARY KEY,
birth_date DATE
);
Do not add midnight merely because an application API requires a date-time object. Converting a date-only value through UTC can shift it to the previous or next calendar day in another zone.
TIME for Time-of-Day Values
TIME stores a time of day, optionally with fractional seconds and, in some products, zone information. It is suitable for a daily opening time or a schedule component when no particular calendar date belongs to the value.
CREATE TABLE store_hours (
store_id BIGINT NOT NULL,
weekday_number INTEGER NOT NULL,
opens_at TIME NOT NULL,
closes_at TIME NOT NULL
);
TIME is not a duration. An elapsed period of 27 hours does not fit the semantics of a 24-hour clock and should use an interval or a numeric unit with clear documentation.
DATETIME and TIMESTAMP
DATETIME and TIMESTAMP commonly combine a calendar date and time of day, but their meaning is not portable. One product may treat TIMESTAMP as an instant influenced by session time zone, another as a local date-time, and another may attach automatic row-update behavior under certain declarations.
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
created_at TIMESTAMP NOT NULL
);
Choose from the target product's documented semantics. The column name and system contract should state whether the value is local wall time or a global instant.
Fractional-Second Precision
Temporal types can store seconds with millisecond, microsecond, or sometimes finer precision. Precision should match the source clock, event frequency, ordering needs, and connected systems. Declaring nanoseconds does not make a clock nanosecond-accurate.
Excess precision increases storage or index width in some products and can be lost when drivers or APIs support fewer digits. Too little precision can collapse distinct events into one displayed time. Define the required precision and test round trips across every layer.
Current Date and Current Timestamp
SQL products expose functions such as CURRENT_DATE, CURRENT_TIMESTAMP, NOW(), GETDATE(), and product-specific UTC variants. Their return types, precision, and transaction-stability behavior differ.
Database-generated values create one authoritative database-clock source, while application-generated values can be useful before persistence or across several stores. Whichever source owns the timestamp, synchronize clocks and document it.
Creation and Update Audit Columns
CREATE TABLE documents (
document_id BIGINT PRIMARY KEY,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
created_at records persistence or creation time, while updated_at records an update policy that must be implemented by application, trigger, or product feature. Neither automatically represents a business event date. An order can be entered today for a transaction that occurred yesterday.
Business Time Versus System Time
Business event time answers when something happened in the domain. Processing time answers when a system received or stored it. Late messages, imports, retries, and batch jobs can make these timestamps different. Keeping both can support auditing and latency analysis.
occurred_at -- event time from the source
received_at -- arrival time at this service
created_at -- row persistence time
Names should identify the event rather than use an ambiguous column called simply timestamp.
UTC and Global Instants
UTC provides a common timeline for events that have already occurred: log entries, transactions, message publication, and audit records. Storing an instant in UTC avoids interpreting it through the server's local time zone and supports comparison across regions.
UTC is not a complete answer for every temporal value. A date-only birthday is not an instant, and a future recurring meeting at 09:00 in New York requires regional time-zone rules to determine future UTC instants.
Offsets Versus Time Zones
An offset such as -05:00 describes the difference from UTC at one instant. A regional zone such as America/New_York carries historical and future rules, including daylight-saving changes. The same region can use different offsets during the year.
Store an offset when the original offset itself matters. Store a region identifier when future local-time scheduling or faithful reconstruction requires zone rules. Avoid abbreviations such as CST because they can be ambiguous.
Daylight-Saving Transitions
During a spring-forward transition, some local clock times do not exist. During a fall-back transition, one local time can occur twice. A local value such as 2026-11-01 01:30 may therefore be ambiguous without a region and offset.
Scheduling systems must define how to handle nonexistent and repeated local times. Test both transitions in every supported region rather than assuming that adding 24 hours always produces the same local time tomorrow.
Timestamp Without Time Zone
A timestamp without time-zone semantics stores date and clock fields without identifying an instant. It can be correct for a wall-clock appointment whose zone is stored separately, a floating local schedule, or a legacy value whose zone is unknown.
It is dangerous when different services interpret it using different server or session zones. The schema and API contract must make the intended interpretation explicit.
Timestamp With Time Zone
Zone-aware timestamp types can represent instants, but their storage behavior varies. PostgreSQL timestamptz normalizes an input instant and renders it using the session zone; it does not preserve the original regional zone identifier. Other products may preserve an offset or offer separate regional-zone types.
If the original appointment zone matters, store it in an additional validated column even when using a zone-aware timestamp. Never infer product behavior solely from the type name.
PostgreSQL Temporal Types
PostgreSQL supports DATE, TIME, TIME WITH TIME ZONE, TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP WITH TIME ZONE, and INTERVAL. Its timestamptz represents an instant and displays it according to session time zone. A plain timestamp carries no zone.
Use DATE for calendar dates, timestamp without zone for deliberately local values, and timestamptz for instants. Preserve a regional zone separately for future schedules.
MySQL Temporal Types
MySQL provides DATE, TIME, DATETIME, TIMESTAMP, and YEAR. DATETIME stores date-time fields without automatic UTC conversion semantics, while TIMESTAMP is commonly converted between the session time zone and UTC and has product-specific range and default behavior.
Version and configuration matter. Choose DATETIME or TIMESTAMP from desired semantics and supported range, not because the names resemble another database.
SQL Server Temporal Types
SQL Server provides DATE, TIME, DATETIME, SMALLDATETIME, DATETIME2, and DATETIMEOFFSET. DATETIME2 generally offers greater range and configurable precision than legacy DATETIME. DATETIMEOFFSET includes an offset and is useful when that offset must accompany a value.
SQL Server's historical timestamp type is not a date or time; it is a row-version binary value. Use appropriate temporal types for time and rowversion for concurrency tokens.
Oracle and SQLite Temporal Models
Oracle DATE includes date and time through seconds, unlike date-only types in many products. Oracle also provides TIMESTAMP and time-zone timestamp variants. That difference is a major migration concern.
SQLite does not have the same strict temporal type system. Applications commonly store ISO-formatted text, Julian day values, or Unix epoch numbers and use built-in functions. Constraints and consistent conventions are essential because a DATE declaration alone does not enforce traditional date semantics.
Unix Epoch Values
An epoch number stores elapsed units since a defined origin, commonly Unix time since 1970-01-01 UTC. It is compact and useful for integrations, but the unit must be documented: seconds, milliseconds, microseconds, and nanoseconds are easily confused.
Native timestamps are generally easier for SQL date arithmetic, extraction, validation, and reporting. Epoch storage is valid when required by an external protocol or high-volume model, provided range, unit, and conversion rules are explicit.
Date and Time Arithmetic
Temporal arithmetic uses product-specific functions and interval support. PostgreSQL can add an interval, MySQL provides functions such as DATE_ADD, and SQL Server provides DATEADD and DATEDIFF. Syntax and boundary-counting semantics are not portable.
Adding a calendar month is not equivalent to adding thirty days, and adding a year is not always 365 days. Requirements must distinguish elapsed durations from calendar adjustments.
Intervals and Durations
An interval represents an amount of time. Some systems distinguish year-month intervals from day-time intervals because months have variable lengths. Where interval types are unavailable or unsuitable, store a numeric duration with a clearly named unit such as duration_seconds.
Do not store duration in TIME. A clock time wraps within a day and cannot naturally represent long or negative elapsed periods. Also avoid ambiguous numeric columns called simply duration.
Month-End and Leap-Year Rules
Adding one month to January 31 requires a business rule because the next month may not contain day 31. Libraries and databases can clamp, roll, or reject under different operations. Leap day creates similar questions when adding years.
Test month end, February 29, year boundaries, and negative intervals. Financial and subscription systems should document whether recurrence means the same day number, last day of month, or fixed elapsed duration.
Extracting Date Parts
Functions such as EXTRACT, YEAR, MONTH, and DATEPART return temporal components. They are useful in projection and grouping, but dialects differ.
SELECT EXTRACT(YEAR FROM occurred_at) AS event_year,
COUNT(*)
FROM events
GROUP BY EXTRACT(YEAR FROM occurred_at);
For large reporting workloads, derived calendar dimensions or generated columns can improve consistency and performance.
Formatting Versus Storage
A date has no inherent display format in the database. Formats such as 31/10/2026 and 2026-10-31 are presentation choices. Exchange typed parameters or unambiguous ISO representations rather than locale-sensitive strings.
Formatting a timestamp inside a filtering predicate converts it to text and can prevent index use. Filter using typed temporal boundaries, then format selected results for users.
Filtering One Calendar Day
A robust timestamp filter uses a half-open range from the beginning of the day through, but not including, the beginning of the next day.
WHERE occurred_at >= :day_start
AND occurred_at < :next_day_start
This avoids guessing the last representable fraction of a second such as 23:59:59.999. Boundaries must be calculated in the correct time zone when the requested day is local rather than UTC.
Sargable Temporal Queries
Wrapping an indexed timestamp in DATE(), CAST, or formatting functions can force the database to transform every row and bypass a normal index. Range predicates leave the stored column unchanged and are often sargable.
If a frequently used derived date genuinely needs its own index, consider a generated column or supported functional index. Confirm with the execution plan rather than assuming.
Temporal Index Design
An index on event time supports ranges and chronological retrieval. Multi-tenant or device-based time-series queries may benefit from a composite key such as (tenant_id, occurred_at). Column order should match leading equality predicates and range access patterns.
High-write timestamp indexes can become hot at one edge, while broad ranges can still read many rows. Retention, partitioning, and summary tables may matter more than adding another index.
Partitioning and Retention
Large event tables are often partitioned by day, month, or another temporal key. Queries must filter on the partition key in a form that enables pruning. Partitioning can simplify bulk retention by dropping old partitions instead of deleting rows one at a time.
Choose partition granularity from volume, query ranges, maintenance, and retention policy. A date rendered from a timestamp may need a stored partition key or product-specific expression support.
Expiration and Validity
An expiration date answers a calendar question, while an expiration timestamp identifies a precise moment. A subscription valid through a local business day may use a date convention; an authentication token normally uses an instant.
WHERE expires_at <= CURRENT_TIMESTAMP
Document whether boundaries are inclusive or exclusive. Half-open periods such as [valid_from, valid_to) avoid overlap and make adjacent periods fit cleanly.
Future Appointments and Schedules
A future appointment described as 09:00 in a region should preserve local date-time and region ID. Calculating and storing a UTC instant is useful for execution, but zone rules can change, and the original scheduling intent may still be needed.
Recurring events require the local recurrence rule and region, not only a list of current UTC conversions. Generate or refresh occurrences according to policy when time-zone databases change.
Store UTC Everywhere Is Incomplete
UTC is excellent for completed events and global instants. It is wrong for date-only values and incomplete for future local schedules. The complete strategy is to classify each value, store instants consistently, preserve regional scheduling context, and convert for presentation.
Temporal architecture should therefore support several deliberate types rather than forcing all values into one UTC timestamp column.
Database Time Versus Application Time
Database-generated timestamps are close to persistence and consistent within that database. Application clocks can stamp events before network delay and coordinate several stores. Distributed systems may need source event time, service receipt time, and database persistence time.
Clock synchronization reduces differences but does not make timestamps unique or perfectly ordered. Use sequence numbers, IDs, or version columns when deterministic ordering or optimistic locking is required.
NULL and Sentinel Dates
NULL can represent an unknown or inapplicable date. Fake values such as 1900-01-01 or 9999-12-31 pollute ranges, calculations, statistics, and reports unless they are formal business sentinels with documented meaning.
Use NOT NULL where a temporal value is required and a range check where only certain dates are valid. Keep unknown distinct from a genuine historical or future date.
Age and Calendar Calculations
Age changes over time and should normally be calculated from birth date as of a specified reference date. Dividing elapsed days by 365 produces errors around birthdays and leap years. Use calendar-aware operations.
Business days, fiscal periods, holidays, and week numbers also require rules. A calendar table can store working-day flags, fiscal periods, regional holidays, ISO weeks, and reporting labels consistently.
Typed Parameters and Parsing
Applications should bind date and timestamp parameters using driver temporal types. This avoids ambiguous string parsing, locale dependence, and many injection risks. If text must be parsed, accept a documented unambiguous format and reject invalid values.
Implicit conversion can change semantics and prevent index use. Use explicit casts at boundaries, not around every indexed row, and verify session time-zone assumptions.
Conversions Can Lose Information
Converting a timestamp to DATE discards time. Reducing fractional precision loses subsecond detail. Dropping an offset or zone removes the information needed to identify an instant. Converting an instant to local time can change its calendar date.
Every conversion should be intentional and named by purpose. A local reporting date derived from an instant must use the report's region before extracting the date.
Application Type Mappings
Modern Java applications can map DATE to LocalDate, local timestamp to LocalDateTime, global instant to Instant, and regional schedule to ZonedDateTime or separate local value plus ZoneId. Python offers corresponding date, time, and timezone-aware datetime concepts.
Avoid legacy APIs that blur these meanings where modern alternatives are available. Confirm driver precision and zone conversion behavior with integration tests.
JavaScript and JSON
JavaScript's built-in Date represents an instant but has awkward parsing and local-zone behavior; a date-only string should not be forced through it without care. APIs commonly use ISO 8601 forms: 2026-10-31 for a date, 2026-10-31T15:30:00Z for UTC, or an explicit offset for an instant.
The suffix Z means UTC. An offset does not preserve a region. API schemas should distinguish date-only, local date-time, offset date-time, instant, and duration instead of documenting every value as a string.
Precision Across System Boundaries
A database may store microseconds while a service model, JSON serializer, message broker, or client keeps only milliseconds. Round trips can then alter equality and ordering. Choose a system-wide supported precision or define where intentional reduction occurs.
Precision is separate from clock accuracy. Six stored fractional digits do not prove that the source clock measured an event within one microsecond.
Validity Periods and Overlap
Effective-dated data commonly uses valid_from and valid_to. Half-open periods make one version end exactly when the next begins without double membership.
valid_from < :requested_end
AND valid_to > :requested_start
This overlap rule supports reservations and validity windows, with additional handling when an open-ended valid_to is NULL. Some databases provide range or temporal-table features that can enforce or query these models more directly.
System Time Versus Valid Time
Valid time describes when a fact is true in the business world. System time describes when the database knew or stored that fact. A retroactive correction can have an old valid date but a recent system timestamp.
SQL temporal-table features can retain system-versioned history, but they do not replace business validity modeling. Decide which timeline an audit or report needs.
Real-World Event Schema
CREATE TABLE events (
event_id BIGINT PRIMARY KEY,
occurred_at TIMESTAMP NOT NULL,
received_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
event_type VARCHAR(60) NOT NULL,
source_sequence BIGINT,
UNIQUE (event_type, source_sequence)
);
The exact timestamp declarations should be adapted to the target database's instant semantics. The sequence provides deterministic source ordering because timestamp precision alone is not a uniqueness guarantee.
Real-World Appointment Schema
CREATE TABLE appointments (
appointment_id BIGINT PRIMARY KEY,
local_start TIMESTAMP NOT NULL,
zone_id VARCHAR(80) NOT NULL,
resolved_start_utc TIMESTAMP NOT NULL,
duration_minutes INTEGER NOT NULL CHECK (duration_minutes > 0)
);
The local value and region preserve scheduling intent, while the resolved instant supports execution and ordering. Production design must define behavior when zone rules change after booking.
Temporal Data Migration and Backfills
Changing a temporal representation requires more than an SQL cast. A text-to-date migration must identify the source format, locale, invalid rows, missing components, and any values that used a sentinel instead of NULL. A local-timestamp-to-instant migration additionally needs the original region or a documented assumption. Without that context, an apparently precise UTC value may encode a guess.
Profile data before conversion and place ambiguous or invalid records in a review path. Perform conversions with explicit formats and zones, preserve the original source where audit requirements demand it, and reconcile counts across date ranges. A backfill should be restartable and should record which rule version transformed each row.
Precision changes also deserve care. Reducing microseconds to milliseconds can create duplicate timestamps or alter boundary inclusion. Expanding precision preserves the old value but does not create new accuracy. Update indexes, partitions, ORM mappings, API schemas, and downstream extracts as one coordinated change.
Late Events, Corrections, and Idempotency
Distributed systems receive events late, out of order, or more than once. Event time should therefore not be used alone to decide whether a message is new. Preserve source identifiers or sequence numbers and define idempotency independently of timestamp precision. Processing time can help diagnose delay but must not overwrite the original business occurrence time.
A correction may arrive today for an event that occurred last month. Reports based on valid business time may need restatement, while audit reports based on system time should show when the correction was received. Keeping those timelines separate makes both questions answerable.
When source clocks are unreliable, retain a quality indicator or source metadata rather than silently replacing event time with server time. A normalized instant can be exact as a stored value while still being inaccurate as a measurement of the real event.
Monitoring Clocks and Temporal Quality
Production monitoring should detect clock drift, events unexpectedly far in the future, stale events, invalid recurrence resolutions, and changes in timestamp precision. Track latency between occurred, received, and processed times. Sudden negative latency can indicate clock skew or a conversion error; a jump of whole hours can indicate an incorrect zone or daylight-saving adjustment.
Monitor NULL and sentinel usage, values near supported type limits, rejected parses, and records that fail validity-period checks. For scheduled systems, alert on nonexistent or ambiguous local times before execution. Keep time-zone data libraries current and test important future schedules after rule updates.
Dashboards must label their reporting zone and interval semantics. A chart grouped by day can produce different counts in UTC and local time, especially around midnight and DST. Reproducible reporting records both the source instant and the calendar rule used to derive each bucket.
Transactional Time and Query Consistency
Some current-time functions are stable for an entire transaction or statement, while others read the clock on each call. This affects long transactions and tests that expect all rows in one operation to share a timestamp. Check product behavior and choose the function that matches the audit requirement.
Database transaction order is not identical to wall-clock order. Two transactions can begin, commit, and become visible in ways that timestamps alone cannot fully describe. Use database concurrency controls, commit sequence mechanisms, or version columns when consistency depends on ordering rather than merely recording approximate real-world time.
Common Temporal Mistakes
- Storing dates or times as VARCHAR with ambiguous formats.
- Using TIME for elapsed duration.
- Storing local timestamps without documenting their zone.
- Assuming an offset is equivalent to a regional time zone.
- Using only UTC for future recurring local schedules.
- Filtering through an invented end-of-day value.
- Wrapping indexed timestamps in formatting functions.
- Storing age instead of a birth date.
- Ignoring leap years, variable month lengths, and DST boundaries.
- Using server local time as an undocumented default.
- Treating timestamps as unique IDs or total ordering keys.
- Assuming DATE and TIMESTAMP mean the same thing across products.
Temporal Type Selection Checklist
- Decide whether the value is a date, time of day, local date-time, instant, schedule, or duration.
- Choose product-specific types that preserve that meaning.
- Define required range and fractional precision.
- Identify UTC, offset, and regional-zone requirements.
- Specify DST and calendar arithmetic rules.
- Define NULL, validity, and boundary semantics.
- Plan indexes, range queries, retention, and partitioning.
- Map to semantic application types and typed parameters.
- Document ISO API representations and precision.
- Test database, driver, API, and user-zone round trips.
Testing Temporal Logic
Test midnight, end of month, year boundaries, February 29, negative and large intervals, minimum and maximum supported dates, NULL, and precision limits. For time-zone logic, test spring-forward gaps, fall-back overlaps, unusual offsets, and regions whose rules change.
Run tests under more than one server and session time zone. Verify local-day queries, serialization, parsing, sorting, expiration, recurrence, and query plans. Freeze or inject clocks in unit tests so current-time behavior is deterministic.
Interview Questions and Answers
When should DATE be used? For a calendar date whose meaning does not include a time or global instant.
Is TIME suitable for duration? No. TIME represents a time of day; use an interval or documented numeric unit for duration.
Why use a half-open timestamp range? It includes every supported fraction within the period without guessing an end-of-day precision.
Is an offset a time zone? No. An offset applies at an instant, while a regional zone carries daylight-saving and historical rules.
Should every temporal value be stored in UTC? Instants generally should be normalized consistently, but dates and future local schedules require their own semantics.
Why can TIMESTAMP be nonportable? Products assign different storage, conversion, range, and zone behavior to the name.
Does more fractional precision mean more accurate time? No. Storage precision does not improve the source clock's accuracy.
Key Takeaway
Temporal design begins with meaning, not formatting. DATE represents a calendar day, TIME represents a time of day, a local timestamp represents wall-clock fields, an instant identifies one point on the global timeline, and a duration describes elapsed or calendar amount.
TEMPORAL DESIGN = BUSINESS MEANING
+ CALENDAR OR INSTANT SEMANTICS
+ ZONE AND DST POLICY
+ PRECISION AND RANGE
+ QUERY AND API BEHAVIOR
Use native types, typed parameters, half-open ranges, explicit zone policies, and product-aware mappings. Preserve regional context for schedules, normalize actual instants consistently, and test calendar and DST boundaries across the complete system.