The TIMESTAMP data type is a column definition that contains the date and time as one field. A TIMESTAMP is frequently used for audit records to establish when critical data has been changed. It is stored internally as ‘CCYY-MM-DD HH.MM.SS.MMMMMM’ (up to 26 in length). A timestamp column must have a valid date and time and can not be blank. If the column is defined as allowing NULLs, then NULL should be used for a missing timestamp.

Example:    COL1 TIMESTAMP

Sample value is: ’2056-02-20 17:39.45′

Notice that the keyword, TIMESTAMP, is a complete definition of the data type and has no value defined in parentheses. See Sample Data Type Values.

TIMESTAMP WITHOUT TIME ZONE is the same as TIMESTAMP. In this course, we use TIMESTAMP.

TIMESTAMP WITH TIME ZONE is the same as TIME WITH TIME ZONE, it’s just a timestamp with a time zone.

The TIME WITH TIME ZONE data type is stored in the ‘HH:MM:SS.MMMMMM -HH:MM’ format. A time column must have a valid time value and can not be blank. The TIME ZONE component shows the offset of the time zone and can range from -12:59 to +13:00. All other SQL rules are the same as they are with TIME.

Example:    COL1 TIME WITH TIME ZONE

Sample value is: ’17:39.45 -6:00′

Notice that the keyword, TIME WITH TIME ZONE, is a complete definition of the data type and has no value defined in parentheses. See Sample Data Type Values.

Like the DATE data type, the TIME data type is stored internally with hours, minutes, seconds, and optionally, with milliseconds in the ‘HH:MM:SS’ (length 8), and ‘HH:MM:SS.MMMMMM’ (up to length 15) format. A time column must have a valid time value and can not be blank. Valid values are from ’00:00:000000′ to ’23:59:59.999999′. If the column is defined as allowing NULLs, then NULL should be used for a missing time.

Example:    COL1 TIME

Sample value is: ’17:39.45′

Notice that the keyword, TIME, is a complete definition of the data type and has no value defined in parentheses.
See Sample Data Type Values.

TIME WITHOUT TIME ZONE is the same as TIME. In this course, we use TIME.