Time in a replicated environment

The following behaviors apply to NPS temporal types and handling:

• Netezza® temporal data types (DATE, TIME, and TIMESTAMP) store date, time, and timestamp values that do not reflect the time zone in which they were generated.
• Any legal date, time, and timestamp literals from an SQL session are accepted.
• The CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP (also NOW) SQL functions generate date, time, and timestamp values that reflect the time in the current time zone. The current time zone, by default, is that of the NPS server host. You can override this time zone in an SQL session by using the SET TIME ZONE command or equivalently, the SET TIMEZONE command. It affects the values that are generated when that session calls the three functions. Even on a single Netezza host, you have no guarantee that date, time, or timestamp values that are inserted from different client sessions (or the same session, with different SET TIME ZONE command settings) have any common timestamp reference or time zone.
• The application must manage whether the time zone interpretation is relative to date, time, and time values in table columns.
• Some built-in tools for handling time and time zone information include the TIMETZ data type (an alias for the TIME WITH TIME ZONE data type) and the TIMEZONE() function. TIMETZ is a slightly different temporal data type in that values carry a time zone offset, which you can specify in TIMETZ literals (for example, '01:23:45-06'). If a time-like literal without the time zone offset is inserted into a TIMETZ column, Replication Services uses '+00' (GMT).

The following example illustrates time zones by using a two-node Netezza replication environment where the primary node is in New York, US (Eastern time zone), and the replica node is in London, UK (GMT). The following order row is created on the primary NPS node in New York and is replicated to the replica in London. In this example, the current timestamp is 19 January 2012 at 9:12 AM.

INSERT INTO ordertable values (1, current_timestamp, 'Product A');

The row in the table on the primary NPS node would appear as follows:

ORDERNUMBER |   ORDERTIMESTAMP    | ORDERDETAILS
------------+---------------------+--------------
          1 | 2012-01-19 09:12:06 | Product A

Time zone differences can impact reporting applications that run against the replica NPS node. For example, as shown in the previous example, new data might be added to the database that appears to be from several hours ago. Further, additional problems are introduced if the roles of the two NPS nodes are reversed (London is now the primary and New York is now the replica). In that case, there will be rows with timestamps from the future relative to the replica.

There are several possible approaches to working around time zone differences. The correct solution depends on your applications and environment.

• Make all time relative to a common base (for example, UTC or GMT).
• Capture time zone-specific information whenever time is recorded.
• Capture locale, location, or system information for all records (for example, in a Location column and a separate location information table).

These approaches are described in the following sections.

In the previous example, the applications would set the TIMEZONE session variable on each node to GMT before performing the DML operations:

SYSTEM(ADMIN)=> SET TIMEZONE='GMT';
SET VARIABLE
SYSTEM(ADMIN)=> show timezone;
NOTICE: Time zone is GMT
SHOW VARIABLE

The same INSERT statement in the previous section would now record the current time relative to GMT:

ORDERNUMBER |   ORDERTIMESTAMP    | ORDERDETAILS
------------+---------------------+--------------
          1 | 2012-01-19 14:12:06 | Product A

For example, a reporting application in the previous example would need to be aware of the different relative operating times of the two regions. A query to show “all orders from today” would need to consider what “today” is, relative to the different locations (New York and London). In this case, the application might benefit from knowing where the order originated to be able to convert the relative time to that of a location. To achieve this, an application might convert all common timestamp information (which uses GMT, in the example) into another specific time zone and then process the information. Consider an extended version of the previous example, where output is as follows:

ORDERNUMBER |      TIMESTAMP      | ORDERDETAILS
-------------+---------------------+--------------
           8 | 2012-04-13 13:40:41 | t3
           7 | 2012-04-13 13:40:25 | t2
           6 | 2012-04-13 13:40:13 | t1
           5 | 2012-04-13 13:34:32 | jki
           4 | 2012-04-13 13:33:49 | def
           3 | 2012-04-13 13:33:33 | abc
           2 | 2012-04-12 14:41:40 | Product A
           1 | 2012-04-12 14:33:03 | Product A

These entries are all GMT time stamps. They can be converted into other relative times by using SQL functions, as follows. First, convert to Pacific Daylight Time:

SELECT ordernumber, timezone('PDT',
timestamp(substring(ordertimestamp,1,10),
timetz(substring(ordertimestamp, 12)))) as ts, orderdetails FROM
ordertable2 ORDER BY ts desc;

Output follows:

ORDERNUMBER |           TS           | ORDERDETAILS
------------+------------------------+--------------
          8 | 2012-04-13 06:40:41-07 | t3
          7 | 2012-04-13 06:40:25-07 | t2
          6 | 2012-04-13 06:40:13-07 | t1
          5 | 2012-04-13 06:34:32-07 | jki
          4 | 2012-04-13 06:33:49-07 | def
          3 | 2012-04-13 06:33:33-07 | abc
          2 | 2012-04-12 07:41:40-07 | Product A
          1 | 2012-04-12 07:33:03-07 | Product A

In this example, the information in the same rows is converted to Eastern Daylight Time:

SELECT ordernumber, timezone('EDT',
timestamp(substring(ordertimestamp,1,10),
timetz(substring(ordertimestamp, 12)))) as ts, orderdetails FROM
ordertable2 ORDER BY ts desc;

Output follows:

ORDERNUMBER |           TS           | ORDERDETAILS
------------+------------------------+--------------
          8 | 2012-04-13 09:40:41-04 | t3
          7 | 2012-04-13 09:40:25-04 | t2
          6 | 2012-04-13 09:40:13-04 | t1
          5 | 2012-04-13 09:34:32-04 | jki
          4 | 2012-04-13 09:33:49-04 | def
          3 | 2012-04-13 09:33:33-04 | abc
          2 | 2012-04-12 10:41:40-04 | Product A
          1 | 2012-04-12 10:33:03-04 | Product A

This example, similar to the original example, uses the VARCHAR type to capture the time stamp information:

INSERT INTO ordertable2 values (1, timezone('EDT', current_timestamp),
'Product A');

The resulting order row in the table is as follows:

ORDERNUMBER |     ORDERTIMESTAMP     | ORDERDETAILS
------------+------------------------+--------------
          1 | 2012-01-19 09:12:06-05 | Product A

Availability of location or time zone-specific information is necessary in the session f capture. You can achieve this by passing a location code, time zone, time zone offset, or some other identifier into the session or through some other means. A sample table follows:

ORDERNUMBER |    ORDERTIMESTAMP    | ORDERDETAILS | LOC_CODE
------------+----------------------+--------------+----------
          1 | 2012-01-19 09:12:06  | Product A    | 1

The LOC_CODE field can then be a mapping to another table that contains location details:

LOC_CODE |   LOCATIONCITY   | LOCATIONTZ | TIMEZONEOFFSET
---------+------------------+------------+----------------
       1 | New York, NY     | EST        | -5