Date/Time handling, TZ

From: Rodney Volz <rodney_at_watchtower.LF.net>
Date: 9 Jan 2000 23:05:10 GMT
Message-ID: <85b477$fj3$1@news.lf.net>

Hello,

I'm running into some problems handling date and time in Oracle 7.

With TZ == GMT, I do this:

  ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

  SELECT sysdate FROM dual;   

  SYSDATE

---

  2000-01-09 22:54:30

Fine so far.

  CREATE TABLE foo AS (SELECT sysdate d FROM DUAL);

  SELECT * FROM foo;

  D

---

  2000-01-09 22:55:14

Still fine.

Now I connect to the database with TZ == MET, which is GMT + 1.

  ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24:MI:SS';   

  SELECT sysdate FROM dual;  

  SYSDATE

---

  2000-01-09 23:56:23

This is o. k., but:

  SELECT * FROM foo;   

  D

---

  2000-01-09 22:55:14

What is this? Shouldn't that be 23:55:14? What if two users in different timezones were accessing that data? The MET-user would think that the timestamp is about an hour old, while the GMT-user would see it as just a few minutes old. That seems odd.

The question is, doesn't oracle give one a way to describe an absolute point in time? It doesn't seem to be possible to tack on the timezone info, and it also doesn't seem to be possible to find out the "current" timezone (whatever that be, client or server timezone).

Regards,
-Rodney Volz Received on Sun Jan 09 2000 - 17:05:10 CST