Date & Time Types
About
Date and time data types are used to store temporal values such as dates, times, and timestamps. These types allow querying, sorting, and arithmetic with time-based data like birthdays, events, or logs.
Standard SQL defines several temporal types, though support varies between vendors.
1. DATE
Stores both date and time components in standard SQL.
Some databases (like Oracle) include time, while others (like MySQL) store only the date part.
Example:
DATE '2024-06-01'
May include 2024-06-01 00:00:00 in systems that store time.
2. TIME [(p)]
Stores time only, without a date.
pis optional precision for fractional seconds.
Example:
TIME '14:30:00' or TIME(3) '14:30:00.123'
3. TIMESTAMP [(p)]
Stores both date and time, including fractional seconds if specified.
Used for tracking full temporal values like creation or modification times.
Example:
TIMESTAMP '2024-06-01 14:30:00'
4. TIME WITH TIME ZONE / TIMESTAMP WITH TIME ZONE
Stores time or timestamp with a time zone offset.
Important for applications dealing with global users and multiple time zones.
Example:
TIMESTAMP '2024-06-01 14:30:00+05:30'
5. INTERVAL
Stores a duration or difference between two temporal values.
Comes in two forms:
INTERVAL YEAR TO MONTHINTERVAL DAY TO SECOND
Example:
INTERVAL '2-6' YEAR TO MONTH
INTERVAL '5 12:30:10' DAY TO SECOND
Vendor Support Overview
Database
DATE
TIME
TIMESTAMP
Time Zone Support
INTERVAL Support
Oracle
Yes (includes time)
No separate type
Yes
TIMESTAMP WITH TIME ZONE
Yes (INTERVAL types)
MySQL
Yes (date only)
Yes
Yes
Limited via CONVERT_TZ()
No built-in INTERVAL, but supports functions like DATE_ADD()
PostgreSQL
Yes (date only)
Yes
Yes
Full support (WITH TIME ZONE)
Yes (full standard support)
SQL Server
Yes (date only)
Yes (TIME)
Yes (DATETIME2)
Limited; no native time zone storage
No native INTERVAL; uses DATEDIFF()
SQLite
Yes (stored as TEXT/REAL)
Yes (via TEXT)
Yes
No native time zone support
No native INTERVAL, use math functions
Last updated