 |
|
EnterpriseDB: DATE and TIMESTAMP
Oracle Tips by Burleson Consulting
|
In
EnterpriseDB, the DATE and TIMESTAMP data types are different from
the DATE and TIMESTAMP date types in Oracle. In Oracle, a DATE
column stores the DATE and the TIME to the second. A TIMESTAMP
stores the DATE and the TIME to an optional fraction of a second (0
to 9 positions of precision) and optionally stores the time zone.
In
EnterpriseDB, a DATE column only stores a DATE. A TIMESTAMP column
stores the DATE and TIME. An EnterpriseDB TIMESTAMP can also
optionally store a time zone. The optional precision fractions of a
second can be from 0 to 6.
EnterpriseDB has a configuration option, "edb_redwood_date", that
can be set to true or false. If you create a table with a DATE
column, and edb_redwood_date = true, EnterpriseDB will replace the
DATE with a TIMESTAMP(0), thus allowing DATE and TIME. If
edb_redwood_date = false, the DATE column will remain a DATE column
and only the date portion of the data (minus any time components)
will be stored.
Example:
If the
edb_redwood_date option is set to false and we run this CREATE TABLE
command:
CREATE
TABLE date_fields (
date_field DATE );
The
table would be created as:
Column Data Type
------------ -------------
DATE_FIELD DATE
If we
ran the same command but edb_redwood_date is set to true, we would
get:
Column Data Type
------------ -------------
DATE_FIELD TIMESTAMP(0)
For
maximum compatibility, I would suggest setting the edb_redwood_date
option to true and creating all tables as DATE. You can ignore the
time component of the date if you do not want it.
In SPL
code, a DATE always stores the DATE and TIME regardless of the
edb_redwood_date option.
A time
zone is a server level setting. While you can declare a TIMESTAMP
WITH TIME ZONE, it is not recommended. I would suggest staying away
from time zones if you can.
Synonyms for TIMESTAMP are:
*
TIMESTAMP(n)
*
DATETIME
*
SMALLDATETIME
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.
