It's About Time - Oracle Time Data and SharePlex

Recently, we’ve had a number of questions about how SharePlex replicates Oracle time data; especially in cases where the source and target systems are in different timezones.  Here’s a brief tutorial on how Oracle handles time data, and what happens when it’s replicated from one system to another.

"A man with a watch knows what time it is.  A man with two watches is never sure.”

Segal’s law is especially important when managing connected, distributed systems.  The easiest way to make sure that your systems remain synchronized is to use a network time service, such as NTP.   This will ensure that your systems will have “one watch”.    Without synchronization, clocks on different servers can drift, and even a few seconds difference can cause issues passing data between systems.    This can be important in SharePlex systems with Active-Active or Master-Master replication, since timestamps are often used for conflict resolution.  If the system clocks are not synchronized, the wrong data could be treated as “master”.

A Brief History

Once you have time on your servers synchronized; you’ll need to consider how Oracle stores time internally.   Oracle stores time data in one of three datatypes, DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE.   Collectively, the data in these types are referred to as datetimes.  

All of these types include fields for YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. TIMESTAMP WITH TIMEZONE also stores fields for TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION and TIMEZONE_ABBR.  TIMESTAMP WITH LOCAL TIMEZONE does not actually store timezone, but the local timezone can be displayed.

The DATE datatype stores time to the second.  The TIMESTAMP datatypes all store fractional seconds, which are not available in the DATE datatype.

Regardless of the data type, Oracle stores time data in internal formats that can range from January 1, 4712 BCE (BC) to December 31, 9999 CE (AD).  This is important to remember if you’re moving data to or from Oracle from other databases, which may be more restrictive.  It’s also important when picking “default” values, since Oracle will consider any date in that range as a “valid” date.

Oracle accounts for the anomalies caused by the switch from the Julian to Gregorian Calendars, so while dates between October 5th and October 14th 1582 can be stored; the day after any day between October 4th, 1582 and October 14th, 1582 will be treated as October 15th, 1582.  Oracle also accounts for Daylight Savings, or Summer Time, based on Time Zone values for the local time in specific regions.  Finally, it should be noted that Oracle date arithmetic does not support country specific standards, such as those that might be found in Asia or the Middle East.

SharePlex Date Replication

All of the above can become important when replicating data between two systems that are in different time zones.   SharePlex does not actually change the data it replicates; which can result in displays that are not necessarily obvious.   

Since the DATE and TIMESTAMP datatypes do not store timezone, a date/time entered on a system in San Francisco as 10-OCT-2016 08:58:23 will display on a system in New York as 10-OCT-2016 08:58:23

TIMESTAMP WITH TIME ZONE explicitly stores time zone information.  After replication, a date/time entered in San Francisco as 10-OCT-2016 08:58:23-08:00 will still display on a system in New York as 10-OCT-2016 08:53:23 -08:00, since the time zone stored with the record will be PST or PDT.

However, a date/time stored as TIMESTAMP WITH LOCAL TIMEZONE will be converted, since in this case, the time zone is relative to the system’s local time zone.

 Here’s an example.  We defined a table as follows:

CREATE TABLE TZ_TEST (

               DT          DATE,

               TS           TIMESTAMP,

               TSZ         TIMESTAMP WITH TIME ZONE,

               TSLZ       TIMESTAMP WITH LOCAL TIME ZONE);

We then set up replication between a system in San Francisco (TIME ZONE = -08:00) and one on New York (TIME ZONE = -05:00) and insert SYSDATE in to all columns:

INSERT INTO TZ_TEST(SYSDATE, SYSDATE, SYSDATE, SYSDATE);

After the commit, here’s what we’ll see on the San Francisco system:

DT

---------

TS

---------------------------------------------------------------------------

TSZ

---------------------------------------------------------------------------

TSLZ

---------------------------------------------------------------------------

05-MAR-17

05-MAR-17 05.16.57.000000 PM

05-MAR-17 05.16.57.000000 PM -08:00

05-MAR-17 05.16.57.000000 PM

And on the New York system, we’ll see:

DT

---------

TS

---------------------------------------------------------------------------

TSZ

---------------------------------------------------------------------------

TSLZ

---------------------------------------------------------------------------

05-MAR-17

05-MAR-17 05.16.57.000000 PM

05-MAR-17 05.16.57.000000 PM -08:00

05-MAR-17 08.16.57.000000 PM

I hope this article proves useful as you work with time data.  In future blogs, we’ll cover what happens when replicating data from an Oracle database to other databases, such as SQLServer or Postgres.

About the Author
Clay.Jackson
Clay Jackson is a Database Systems Consultant for Quest, specializing in Database Performance Management and Replication Tools. Prior to joining Quest, Jackson was the DBA Manager at Darigold. He also...