employees.hire_date has been DATE since Module 7, storing a calendar date correctly without ever needing a time component. This lesson is purely about the storage types themselves, what DATE, TIME, DATETIME, and TIMESTAMP actually hold on each database, the actual functions and arithmetic for working with them are Module 10's SQL Dates lesson.
Learning Objectives
- Distinguish DATE, TIME, DATETIME, and TIMESTAMP by what each actually stores.
- Explain why Oracle has no dedicated time-only type.
- Explain why SQL Server's TIMESTAMP type has nothing to do with dates at all.
A Moment in Time
2026-10-30, or 14:30:00, or both
Date Only? Time Only? Both?
DATE vs TIME vs DATETIME/TIMESTAMP
Matching Type
The real keyword, per database
Stored Correctly
No silent format mismatch
DATE: Calendar Date Only
| Database | What DATE Actually Stores |
|---|---|
| MySQL / MariaDB | Calendar date only (YYYY-MM-DD), year range 1000 to 9999 |
| PostgreSQL | Calendar date only, a much wider range (4713 BC to 5874897 AD) |
| SQL Server | Calendar date only, year range 0001 to 9999 |
| Oracle | Date AND time together, down to the second, always, even for date-only values |
| SQLite | No real DATE storage class at all, stored as TEXT, REAL, or INTEGER by convention |
Oracle's row is the genuine surprise: DATE there isn't date-only at all, it always carries a time component down to the second internally, even when every row's time happens to be midnight. SQLite's DATE isn't a storage class either, it's purely a naming convention, SQLite stores the actual value as whichever of TEXT, REAL, or INTEGER the application chose, covered in full in Module 10's SQL Dates lesson.
TIME: Time of Day Only
| Database | TIME Support |
|---|---|
| MySQL / MariaDB | TIME, time-of-day or a duration, can exceed 24 hours (-838:59:59 to 838:59:59) |
| PostgreSQL | TIME [WITHOUT TIME ZONE] / TIME WITH TIME ZONE |
| SQL Server | TIME(n), with configurable fractional-second precision |
| Oracle | No dedicated time-only type at all |
| SQLite | No real TIME storage class, same TEXT/REAL/INTEGER convention as DATE |
Oracle genuinely has no way to store a time-of-day value on its own, there's only DATE (which always includes both date and time together) or INTERVAL for a pure duration. Needing "just a time", like a daily opening-hours value, on Oracle means either storing a full DATE and ignoring the date part, or representing it differently entirely.
DATETIME vs TIMESTAMP: Not the Same Thing Everywhere
| Database | Combined Date + Time |
|---|---|
| MySQL / MariaDB | DATETIME (no time zone, wide range) and TIMESTAMP (auto-updatable, narrow range: 1970-01-01 to 2038-01-19) |
| PostgreSQL | No DATETIME keyword at all; TIMESTAMP [WITHOUT TIME ZONE], plus TIMESTAMPTZ (WITH TIME ZONE) |
| SQL Server | DATETIME (legacy, ~3ms precision) and DATETIME2(n) (modern, recommended); its own TIMESTAMP means something else entirely, below |
| Oracle | TIMESTAMP(p), plus TIMESTAMP WITH TIME ZONE / WITH LOCAL TIME ZONE |
| SQLite | Neither keyword creates a real storage class, same TEXT/REAL/INTEGER convention |
SQL Server has a column type literally named TIMESTAMP, and it has nothing to do with dates at all, it's an automatically-updating binary row-version number used to detect whether a row changed, completely unrelated to the TIMESTAMP every other database on this list uses for date/time. Microsoft's own documentation recommends against using it for new work at all, and suggests ROWVERSION, its newer name for the exact same thing, instead. Never reach for SQL Server's TIMESTAMP expecting date/time behavior, it will not hold a date.
Try It Yourself
Add a column scheduled_at to a new meetings table, that needs to store both a date and a time, on PostgreSQL.
ALTER TABLE meetings ADD COLUMN scheduled_at TIMESTAMP NOT NULL;
Frequently Asked Questions
Which of these types actually store a time zone?
Only the explicit WITH TIME ZONE variants, PostgreSQL's TIMESTAMPTZ and Oracle's TIMESTAMP WITH TIME ZONE. Plain DATE/TIME/DATETIME/TIMESTAMP everywhere else store a value with no time zone attached at all, interpreting it correctly is entirely up to the application.
Is MySQL's TIMESTAMP 2038 range limit actually still a real concern?
Yes, for any column that might need to store a date past 2038-01-19, a long-term warranty expiration, a future-dated record. DATETIME has no such limit and is the safer default in MySQL/MariaDB whenever a far-future date is plausible.
Common Beginner Mistakes
Assuming SQL Server's TIMESTAMP type works like every other database's TIMESTAMP
It doesn't, SQL Server's TIMESTAMP is a row-versioning binary value, not a date/time type at all, and it can't be used to store or query an actual date. DATETIME2 is the correct modern SQL Server type for an actual date and time.
Using MySQL's TIMESTAMP for a far-future date and silently hitting the 2038 ceiling
TIMESTAMP's range ends at 2038-01-19, the same 32-bit limit behind the general Year 2038 problem, any later date either gets rejected or wraps incorrectly depending on the MySQL version. DATETIME has no such limit and should be used instead whenever a date could plausibly land beyond 2038.
Best Practices
- Use DATETIME2 for new SQL Server work, never the legacy DATETIME, and never the unrelated TIMESTAMP.
- Store a timezone-aware moment with the explicit WITH TIME ZONE type where the engine offers one, rather than assuming context will make it unambiguous later.
- On SQLite, pick one date/time storage convention (ISO8601 text is the most common) and apply it consistently, nothing enforces it automatically.
Interview Questions
Why can't SQL Server's TIMESTAMP column be used to store a date?
SQL Server's TIMESTAMP (also exposed under its newer name, ROWVERSION) is an automatically-incrementing binary value the database uses internally to detect whether a row has changed, it has no date or time meaning whatsoever and can't be set, compared, or read as one. CREATE TABLE t (updated_at TIMESTAMP); creates a column that changes on every UPDATE to that row, but never holds an actual date, DATETIME2 is the correct type for that.
What's the Year 2038 problem, and which type in this course is actually affected by it?
The Year 2038 problem is the overflow of a signed 32-bit Unix timestamp (seconds since 1970-01-01) on 2038-01-19, the exact moment the counter exceeds what 32 bits can represent. MySQL and MariaDB's TIMESTAMP type is built on that same 32-bit representation internally, so it genuinely can't store a date past 2038-01-19, hire_date TIMESTAMP would be a real mistake for any employee record expected to outlast that date, hire_date DATETIME has no such limit and is the correct fix.
Summary
DATE, TIME, DATETIME, and TIMESTAMP are not interchangeable across databases, Oracle has no time-only type at all, SQLite has no real date/time storage class whatsoever, and SQL Server's own TIMESTAMP keyword is a row-versioning value with no date meaning at all. Knowing which real keyword to reach for, and which trap each database sets, matters more here than almost anywhere else in this course.
What's Next?
Boolean and Binary Data Types is next, the two categories Beacon Robotics' schema has never actually needed, and which of the six databases genuinely has a real BOOLEAN type (most don't).
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.