Skip to content
Tutorials Center
My learning

Topics

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.

SQL
CREATE TABLE events (
  id       INTEGER PRIMARY KEY,
  starts_on DATE NOT NULL,
  starts_at TIMESTAMP
);

Try it

SQL Dates stored as text, read with date functions ยท database: shop

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

SQL Three storage formats for the same moment

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

Syntax

SQL
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.
SQL
-- 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-DD or YYYY-MM-DD HH:MM:SS. It sorts correctly as text, so ORDER BY and 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 return NULL for them.
  • Time zones: store timestamps in UTC and convert when displaying. Plain TIMESTAMP values 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' over strftime('%Y-%m', ordered_at) = '2025-01'; it can use an index. See sargable predicates.
  • Date arithmetic differs a lot between databases. See DATEDIFF and the date functions.
  • Invalid values. SQLite stores '2025-02-30' happily in a column, and date('2025-02-30') quietly rolls it over to 2025-03-02. Add a CHECK (date(col) = col) if you want validation.
  • Pick one representation per project (text, Julian day or Unix seconds) and use it everywhere.