SQL Dialects Reference/Data structure definition/Data types/Date and time types

From Wikibooks, open books for an open world
Jump to navigation Jump to search

Date and time types[edit]

This page includes comparison tables which can be big and complex.

While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.

SQL version Feature Standard
SQL:2011
DB2 Firebird Ingres Linter MSSQL MySQL
Vers. 5.x
MonetDB Oracle
Vers. 11.x
PostgreSQL SQLite Virtuoso
? Date only DATE DATE DATE DATE
ANSIDATE
INGRESDATE
DATE,
DATETIME
date DATE DATE DATE (includes TIME !) DATE TEXT[1] DATE
? Time only TIME TIME TIME TIME [WITHOUT TIME ZONE] DATE
DATETIME
time TIME TIME no TIME [WITHOUT TIME ZONE] TEXT[1] TIME
? Time with time zone TIME WITH TIME ZONE ? ? TIME WITH TIME ZONE ? N/A N/A TIME WITH TIME ZONE no TIME WITH TIME ZONE N/A N/A
? Date and time without time zone TIMESTAMP TIMESTAMP TIMESTAMP DATE
TIMESTAMP
ANSIDATE
INGRESDATE
DATE
DATETIME
datetime2
datetime
smalldatetime
TIMESTAMP TIMESTAMP
TIMESTAMP WITH TIME ZONE
DATE
TIMESTAMP
TIMESTAMP [WITHOUT TIME ZONE] TEXT
REAL
INTEGER[1]
DATETIME
TIMESTAMP
? Date and time with time zone TIMESTAMP WITH TIME ZONE ? ? ? ? datetimeoffset N/A TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH TIME ZONE TIMESTAMP WITH TIME ZONE N/A N/A
? Time interval INTERVAL DAY TO SECOND(n)

INTERVAL YEAR TO MONTH
? ? INTERVAL DAY TO SECOND(n)

INTERVAL YEAR TO MONTH
? N/A Not available as data type, only as data values. INTERVAL INTERVAL DAY[(day_precision)] TO SECOND[(fractional_second_precision)]

INTERVAL YEAR [(year_precision)] TO MONTH
INTERVAL N/A ?
  1. a b c the built-in date/time functions can use these types for storing values