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

From Wikibooks, open books for an open world
Jump to: navigation, 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 DATE DATE
 ? Time only TIME TIME TIME TIME [WITHOUT TIME ZONE] DATE
DATETIME
time TIME TIME no TIME [WITHOUT TIME ZONE] TIME 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
 ? 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] DATETIME
TIMESTAMP
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
 ? 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 TO SECOND
INTERVAL YEAR TO MONTH
INTERVAL  ?  ?