User Documentation
Ispirer Capabilities - Oracle Migration
Pricing
Oracle Data Types - TIMESTAMP
Versions: Oracle 11g, 10g and 9i
Oracle - TIMESTAMP | |
---|---|
Syntax | TIMESTAMP[(p)] [WITH [LOCAL] TIME ZONE] |
Data | Date and time data (year, month, day, hours, minutes, seconds and fractional seconds) WITH TIME ZONE means that Oracle stores time zone offset WITH LOCAL TIME ZONE means that Oracle normalizes timestamp data according to the database server time zone, and does not store time zone offset |
Parameters | p specifies the number of digits in the fractional part of seconds |
Range | January 1, 4712 BC 00:00:00.000000000 to December 31, 9999 23:59:59.999999999, 0 ⇐ p ⇐ 9 |
Default | p is 6 (microseconds) |
Version Differences | Oracle 9i - TIMESTAMP data type is introduced |
Standards | ANSI SQL |
Oracle TIMESTAMP - Equivalents in Other Databases
Database | Data Type and Conversion |
---|---|
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) |
PostgreSQL | TIMESTAMP(p), January 01, 4713 BC 00:00:00:000000 and December 31, 5874897 23:59:59:999999 (microseconds 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 00:00:00.00000 to December 31, 9999 11:59:59.99999 (with 10 microseconds accuracy) |
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; TIME(p), 00:00:00 and 24:00:00 |
Related Data Types in Oracle
Data Types | ||
---|---|---|
Datetime | DATE | |
Intervals | INTERVAL YEAR(p) TO MONTH | INTERVAL DAY(p1) TO SECOND(p2) |
All Data Types