SQLWays Product Page - Migration to PostgreSQL
Free Evaluation
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) |
Related Data Types in PostgreSQL
| Data Types | ||
|---|---|---|
| Datetime | DATE | TIME(p) |
| Intervals | INTERVAL(p) | |
| Oracle | SQL Server | IBM DB2 | MySQL | PostgreSQL | Sybase ASE | Sybase ASA | Informix DS | Teradata | HP Neoview | Interbase/Firebird |
SQLWays Product Page - Migration to PostgreSQL
Free Evaluation
Discussion