Non-default Time Zones for CURRENT or TODAY (XPS)

For Extended Parallel Server, the time and date returned by CURRENT (like the date returned by TODAY) defaults to the value from the system clock-calendar, but you can specify instead that CURRENT return the time and date from some other time zone. You can specify a time zone by using the SET ENVIRONMENT CLIENT_TZ statement of SQL (or by setting the IBM_XPS_PARAMS environment variable) to specify an offset from Greenwich Mean Time (GMT).

Suppose that a table in an Extended Parallel Server instance has this schema:

create table testtab (intcol integer, 
      dtcol              date, 
      datecol          datetime year to second); 

Assume also that the IBM_XPS_PARAMS setting for CLIENT_TZ has the value +1, that the server time zone is at GMT+4 hours, that the current server date is 2005-05-15, and the current server time is 1:10:39. If the system clocks for client and server are both based on the same value for GMT, this implies a client time zone of GMT+1 hour, or 3 hours earlier than the time zone of the server.

Now you enter the following SQL statements:

INSERT INTO testtab VALUES (1, TODAY, CURRENT);
SELECT * FROM testtab WHERE intcol = 1;

The query returns 1 (from column intcol) and the following TODAY and CURRENT values:

2005-05-15          01:10:39.000 05/15/2005

Now assume that the client issues the following statements:

SET ENVIRONMENT CLIENT_TZ "-06:30"
INSERT INTO testtab VALUES (3, TODAY, CURRENT);
SELECT * FROM testtab WHERE intcol = 2;

The query returns 2 (from column intcol) and the following TODAY and CURRENT values:

   2005-05-14     14:40:39 05/14/2005

This value is 10.5 hours earlier than the result from the first example.

Note that values that have been stored into a database table are not affected by the CLIENT_TZ specification, as the following example illustrates. The CLIENT_TZ setting is used only when evaluating the CURRENT and TODAY operators as specific DATETIME and DATE values.

SET ENVIRONMENT CLIENT_TZ ON
SELECT * FROM testtab WHERE intcol = 1;

The query returns 1 (from column intcol) and the following TODAY and CURRENT values:

   2005-05-15     01:10:39 05/15/2005

It is also important to note that the INSERT statements above do not evaluate to the time on the client system. Instead, they are based on the time of the database server, as offset to the time zone of the client system. These two times might differ, if the server and client system clocks are not synchronized correctly.