SQL Reference ยท Date and Time Functions
SQL date and time storage
How SQL databases store dates and times: DATE, TIME, TIMESTAMP and time zones, ISO 8601 text in SQLite, Unix timestamps, and the mistakes to avoid.
Every database needs to answer the same question: what is the best way to keep a moment in time in a column? The SQL standard defines separate types for a calendar date, a time of day, and a combined timestamp, with or without a time zone. Most servers implement them as compact binary values and give you functions to format them on the way out.
SQLite is the odd one out. It has no date type at all. A "date" is just a value of one of three ordinary types: ISO 8601 text, a Julian day number, or Unix seconds. The built-in date functions understand all three. The sample databases on this site use ISO text, so that is what this page demonstrates.
The standard date and time types
| Type | Holds | Example literal |
|---|---|---|
DATE |
A calendar day | DATE '2025-03-15' |
TIME |
A time of day | TIME '14:30:00' |
TIMESTAMP |
Day and time | TIMESTAMP '2025-03-15 14:30:00' |
TIMESTAMP WITH TIME ZONE |
An absolute moment | TIMESTAMP WITH TIME ZONE '2025-03-15 14:30:00+01' |
INTERVAL |
A length of time | INTERVAL '3' DAY |
The literals above are standard SQL. SQLite accepts none of them as typed literals; there you write plain strings and call functions.
SQLite: three ways to store a moment
Press Run (or Ctrl + Enter) to see the result here.
ISO text (YYYY-MM-DD HH:MM:SS) is the usual choice. It is readable, it sorts correctly as plain text, and every SQLite date function accepts it. Unix seconds are compact and fast to compare. Julian day numbers are mostly useful for arithmetic.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
SQLite stores whatever text you give it, so a column "of dates" can hold nonsense. Above, date() turns the impossible 2025-02-30 into 2025-03-02 and returns NULL for text it cannot read. A CHECK (date(happens_on) = happens_on) constraint is the usual guard; it accepts only text that is a valid date in canonical form.
Time zones
A timestamp without a time zone is only a wall-clock reading: 2025-03-15 14:30 is a different moment in Berlin and in Toronto. A timestamp with a time zone, or a UTC value, identifies a single instant.
- PostgreSQL
timestamptzconverts input to UTC for storage and back to the session time zone on output. It does not remember the original offset. - MySQL
TIMESTAMPis stored in UTC and shown in the session zone.DATETIMEis a plain wall-clock value. - SQL Server
datetimeoffsetstores the offset with the value.datetime2has none. - Oracle has
TIMESTAMP WITH TIME ZONE(keeps the offset) andTIMESTAMP WITH LOCAL TIME ZONE(normalized). - SQLite has no zone support beyond the
'utc'and'localtime'modifiers. Store UTC and convert at the edges.
Dialect differences
| Feature | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
| Date-only type | None (text) | date |
DATE |
date |
DATE (includes time) |
| Date and time | None (text) | timestamp |
DATETIME, TIMESTAMP |
datetime2 |
TIMESTAMP |
| With time zone | None | timestamptz |
TIMESTAMP (UTC-based) |
datetimeoffset |
TIMESTAMP WITH TIME ZONE |
| Current moment | datetime('now') |
now(), CURRENT_TIMESTAMP |
NOW() |
SYSDATETIME() |
SYSTIMESTAMP |
| Format as text | strftime() |
to_char() |
DATE_FORMAT() |
FORMAT(), CONVERT() |
TO_CHAR() |
Invalid date 2025-02-30 |
Accepted as text | Error | Error in strict mode | Error | Error |
-- PostgreSQL SELECT to_char(TIMESTAMP '2025-03-15 14:30', 'DD Mon YYYY'); -- 15 Mar 2025 -- MySQL SELECT DATE_FORMAT('2025-03-15 14:30:00', '%d %b %Y'); -- 15 Mar 2025 -- SQL Server SELECT FORMAT(CAST('2025-03-15' AS date), 'dd MMM yyyy'); -- 15 Mar 2025 -- Oracle SELECT TO_CHAR(DATE '2025-03-15', 'DD Mon YYYY') FROM dual; -- 15 Mar 2025
Usage notes
- Use the native date type when the database has one. It validates input, uses less space, and allows index-friendly range queries.
- Always store ISO 8601 in SQLite. Formats such as
15/03/2025or03/15/2025do not sort correctly and the date functions returnNULLfor them. - Compare with half-open ranges.
ordered_at >= '2025-02-01' AND ordered_at < '2025-03-01'works for dates and timestamps alike.BETWEEN '2025-02-01' AND '2025-02-28'silently drops orders placed on the 28th after midnight when the column also holds a time. - Store UTC, display local. Convert only when you show the value to a person.
- Do not store dates as separate year, month, day columns unless you have a very specific reason; you lose validation and simple comparisons.
- Beware of mixed precision.
'2025-03-15'is less than'2025-03-15 00:00'as text. Normalize the format on insert. - Oracle
DATEincludes a time down to the second, unlike the standardDATE.