Skip to content
Tutorials Center
My learning

Topics

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

SQL The same moment as text, a Julian day and Unix seconds

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.

SQL ISO text sorts and compares like a date ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Invalid dates are stored as typed (and may parse oddly)

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 timestamptz converts input to UTC for storage and back to the session time zone on output. It does not remember the original offset.
  • MySQL TIMESTAMP is stored in UTC and shown in the session zone. DATETIME is a plain wall-clock value.
  • SQL Server datetimeoffset stores the offset with the value. datetime2 has none.
  • Oracle has TIMESTAMP WITH TIME ZONE (keeps the offset) and TIMESTAMP 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
SQL
-- 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/2025 or 03/15/2025 do not sort correctly and the date functions return NULL for 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 DATE includes a time down to the second, unlike the standard DATE.