Ispirer Ispirer
 


Ispirer Home Page Ispirer SQLWays Product Page - Migration to PostgreSQL Request SQLWays

PostgreSQL Data Types - TIMESTAMP

Versions: PostgreSQL 8.x and 7.x

PostgreSQL - TIMESTAMP
Syntax TIMESTAMP[(p)] [WITH|WITHOUT TIMEZONE]
Data Date and time data (year, month, day, hours, minutes, seconds and microseconds)
Parameters p is the number of digits in the fractional part of second, 0 ⇐ p ⇐ 6; WITH TIME ZONE specifies that the time zone is stored along with the time values
Default p is 6
Range January 01, 4713 BC 12:00:00.000000 AM to December 31, 5874897 11:59:59.999999 PM
Special Values The special values (strings in single quotes) are supported: 'epoch' (1970-01-01 00:00:00 - Unix system time zero), 'now' (current transaction's start time),
'today' (midnight today), 'yesterday' (midnight yesterday) and 'tomorrow' (midnight tomorrow)
Storage Size 8 bytes
Standards ANSI SQL

PostgreSQL TIMESTAMP - Equivalents in Other Databases

Database Data Type and Conversion
Oracle TIMESTAMP(p), January 1, 4712 BC 00:00:00.000000000 to December 31, 9999 23:59:59.999999999 (nanoseconds accuracy)
SQL Server DATETIME, January 01, 1753 00:00:00.000 to December 31, 9999 23:59:59.997 with accuracy 1/300 second
DATETIME2 and DATETIMEOFFSET, January 01, 0001 00:00:00.0000000 to December 31, 9999 23:59:59.9999999 (100 nanoseconds accuracy)
SMALLDATETIME, January 01, 1900 00:00 to June 06, 2079 11:59 (minute accuracy)
MySQL DATETIME, January 01, 1000 00:00:00 to December 31, 9999 23:59:59 (seconds accuracy)
Sybase ASE DATETIME, January 01, 1753 12:00:00.000 AM to December 31, 9999 11:59:59.999 PM (with accuracy 1/300 second)
SMALLDATETIME, January 01, 1900 00:00 to June 06, 2079 11:59 (minute accuracy)
Informix DATETIME, January 01, 0001 12:00:00.00000 AM to December 31, 9999 11:59:59.99999 PM (with 10 microseconds)
Teradata TIMESTAMP(p), January 01, 0001 00:00:00.000 to December 31, 9999 23:59:61.999999 (microseconds accuracy)
HP Neoview TIMESTAMP(p), January 01, 0001 00:00:00.000000 to December 31, 9999 23:59:59.999999 (microseconds accuracy)
Ingres TIMESTAMP(p), 0001-01-01 00:00:00 to 9999-12-31 24:00:00

Related Data Types in PostgreSQL

Data Types
Datetime DATE TIME(p)
Intervals INTERVAL(p)


Ispirer Home Page Ispirer SQLWays Product Page - Migration to PostgreSQL Request SQLWays

sqlways/postgresql/data-types/timestamp.txt · Last modified: March 20, 2013, 04:07:48 AM (external edit)
 
Ispirer Ispirer © 1999-2014, Ispirer Systems Ltd.
All Rights Reserved.  Privacy Statement