3. Understanding SQL Data Types : SQL Data Types : Date and Time Data Types : Timestamp Data Types
 
Share this page                  
Timestamp Data Types
The TIMESTAMP data type consists of a date and time, with optional time zone.
The format is as follows:
TIMESTAMP [(timestamp_precision)] [time_zone_spec]
timestamp_precision
(Optional) Indicates the number of digits of precision in the fractions of seconds, as an integer value from 0 to 9. The default is 6.
Note:  When a value is entered that exceeds the specified precision, the value is truncated at the specified precision.
time_zone_spec
(Optional) Specifies a time zone as one of the following:
WITH TIME ZONE
WITHOUT TIME ZONE
WITH LOCAL TIME ZONE
Note:  The time value in TIMESTAMP WITH TIME ZONE data type indicates the time at the specified time zone.
Examples:
Timestamp Format
Example
TIMESTAMP
2006-12-15 12:30:55.123456
TIMESTAMP(4) WITHOUT TIME ZONE
2006-12-15 12:30:55.1234
TIMESTAMP(5) WITH TIME ZONE
2006-12-15 9:30:55.12345-08:00
TIMESTAMP(9) WITH LOCAL TIME ZONE
2006-12-15 12:30:55.123456789
Note:  Two TIMESTAMP WITH TIME ZONE values are considered identical if they represent the same instant in UTC, regardless of the TIME ZONE offsets stored in the database. For example:
TIMESTAMP '2006-12-15 9:30:55 -8:00'
is the same as
TIMESTAMP '2006-12-15 12:30:55 -5:00'
Note:  The ANSI datetimes can also be loaded using the II_DATE_FORMAT for Absolute Ingresdate (see II_DATE_FORMAT for Absolute Ingresdate) input formats.