SQL Reference ยท Constraints and Data Types
SQL DATE, TIME and TIMESTAMP types
Reference for SQL temporal types DATE, TIME and TIMESTAMP: formats, time zones, how SQLite stores dates as text, and the main differences between databases.
SQL has three core temporal types. DATE holds a calendar day, TIME holds a time of day, and TIMESTAMP (also called DATETIME in some databases) holds both. Most databases also offer a WITH TIME ZONE variant of the last two, and an INTERVAL type for durations.
SQLite has none of these types. You can declare a column as DATE or TIMESTAMP, but the values are stored as ordinary text (ISO 8601 strings such as '2025-01-13 17:29'), as Julian day numbers (REAL) or as Unix seconds (INTEGER). Date functions then interpret those values. The sample databases on this site store dates as ISO text, which sorts correctly and works with every SQLite date function.
CREATE TABLE events ( id INTEGER PRIMARY KEY, starts_on DATE NOT NULL, starts_at TIMESTAMP );
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
DATE -- 'YYYY-MM-DD' TIME [(p)] [WITH TIME ZONE] -- 'HH:MM:SS[.ffff]' TIMESTAMP [(p)] [WITH TIME ZONE] -- 'YYYY-MM-DD HH:MM:SS[.ffff]'
| Part | Description |
|---|---|
p |
Optional fractional-second precision (digits after the point). |
WITH TIME ZONE |
The value carries or is converted by a time zone. Without it, the value is a plain wall-clock reading. |
| Literal form | A string in ISO format, or the standard DATE '2025-03-14' prefix syntax (not in SQLite). |
Returns: a value of that type. In SQLite the stored value is whatever you inserted: use date(), time(), datetime(), strftime(), julianday() and unixepoch() to read it.
Dialect differences
| Database | Types | Notes |
|---|---|---|
| SQLite | None; text, REAL or INTEGER |
Date functions accept ISO text, Julian days, or Unix seconds (with the 'unixepoch' modifier). Unparseable text gives NULL, and a day past the end of the month rolls over (date('2025-02-30') is 2025-03-02). No time zone support beyond 'utc' and 'localtime' modifiers. |
| PostgreSQL | DATE, TIME, TIMESTAMP, TIMESTAMPTZ, INTERVAL |
Strict validation. TIMESTAMPTZ stores UTC and converts to the session time zone on output. |
| MySQL | DATE, TIME, DATETIME, TIMESTAMP, YEAR |
TIMESTAMP is stored as UTC and limited to 1970-2038; DATETIME has no time zone. |
| SQL Server | DATE, TIME, DATETIME2, DATETIMEOFFSET, SMALLDATETIME, DATETIME |
Prefer DATETIME2 over the older DATETIME. |
| Oracle | DATE (includes a time), TIMESTAMP, TIMESTAMP WITH TIME ZONE, INTERVAL |
Oracle's DATE always has a time part; there is no date-only type. |
-- PostgreSQL SELECT TIMESTAMPTZ '2025-03-14 09:30+01', INTERVAL '3 days'; -- SQL Server SELECT SYSDATETIME(), CAST('2025-03-14' AS DATE); -- Oracle SELECT DATE '2025-03-14', SYSTIMESTAMP FROM dual;
Usage notes
- Use the ISO format
YYYY-MM-DDorYYYY-MM-DD HH:MM:SS. It sorts correctly as text, soORDER BYand range comparisons work in SQLite without any conversion. - Do not mix formats in one column.
'14/03/2025'or'March 14'will not sort by date and SQLite date functions returnNULLfor them. - Time zones: store timestamps in UTC and convert when displaying. Plain
TIMESTAMPvalues have no zone, so two servers can disagree about what a stored value means. - Range filters. Prefer
ordered_at >= '2025-01-01' AND ordered_at < '2025-02-01'overstrftime('%Y-%m', ordered_at) = '2025-01'; it can use an index. See sargable predicates. - Date arithmetic differs a lot between databases. See
DATEDIFFand the date functions. - Invalid values. SQLite stores
'2025-02-30'happily in a column, anddate('2025-02-30')quietly rolls it over to2025-03-02. Add aCHECK (date(col) = col)if you want validation. - Pick one representation per project (text, Julian day or Unix seconds) and use it everywhere.
Related
DATE(),TIME(), date formats,DATEDIFF, SQLite type affinity- Lessons: Date functions, Data types