Timestamp data type in database servers

Timestamp data type is one of the data types that can create problems which migrating data from one kind of server to another. Although most database products consider timestamp as a data type, Microsoft SQL Server does not consider it as a data type. Some databases just store date information in text, integer and real.

Review the following taken from various database products:


SQL Server:
SQL Server 2000. timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows.

MySql:
The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

Oracle:

 
Timestamp.png

DB2:

A timestamp is a seven-part value representing a date and time by year, month, day, hour, minute, second, and microsecond, in the range of 0001-01-01-00.00.00.000000000 to 9999-12-31-24.00.00.000000000 with nanosecond precision. Timestamps can also hold timezone information.

PostgreSQL:
The timestamp data type allows you to store both date and time. However, it does not have any time zone data. It means that when you change your database server’s time zone, the timestamp value stored in the database does not change.

The timestamptz data is the timestamp with time zone. The timestamptz is a time zone-aware date and time data type. PostgreSQL stores the timestamptz in UTC value. When you insert a value into a timestamptz column, PostgreSQL converts the timestamptz value into a UTC value and stores the UTC value in the table.

SQL Anywhere 12:
The fraction is stored to 6 decimal places. A TIMESTAMP value requires 8 bytes of storage.

Although the range of possible dates for the TIMESTAMP data type is the same as the DATE type (covering years 0001 to 9999), the useful range of TIMESTAMP date types is from 1600-02-28 23:59:59 to 7911-01-01 00:00:00. Before and after this range the time portion of the TIMESTAMP may be incomplete.

When a TIMESTAMP value is converted to TIMESTAMP WITH TIME ZONE, the connection's time_zone_adjustment setting is used for the time zone offset in the result. In other words, the value is considered to be "local" to the connection. When a TIMESTAMP WITH TIME ZONE value is converted to TIMESTAMP, the offset is discarded.

SQLite:
No timestamp. Even date/time are stored as Text, real or integers
"SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

    TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
    REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
    INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC. "

Netezza:
Type_name       DataType              Precision   Nullable   Case Sens   Maximum Scale
DATE               91 [DATE]           10               true           false            0
TIME               92 [TIME]            15               true           false            6
TIMESTAMP  93 [TIMESTAMP]26              true           false            6