Timestamp Data Types

3. Understanding SQL Data Types : SQL Data Types : Date and Time Data Types : Timestamp Data Types

Share this page

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 page II_DATE_FORMAT for Absolute Ingresdate) input formats.