ORA-0000 (SYSDATE Traps)

Quick confession: I never took the 9i TIMESTAMP datatype very seriously. I looked at it merely as a way to record fractional parts of a second. I never needed to record fractional parts of a second.

Then came the Great Daylight Savings Time Scare of 2007. I was glad I never paid attention to TIMESTAMP datatypes. If I had, I might have had a problem. No time zone aware columns, no problem.

Until I wrote a task which was used by all sites in my application to drive cron.

Suddenly, a number of sites starting running tasks repeatedly, starting an hour early. Mysteriously, they all were in the Central time zone.

Here is what the problem was. Cron was using Central time. Even though it was in a data center in the Eastern time zone, the VM server had America/Chicago for its TZ variable. But SYSDATE was returning Eastern time, because it too was in the Eastern time zone, and the listener and spawned dedicated Sql*net servers were all Eastern time. Since my task derived the time from SYSDATE, it was an hour off.

The fix was to replace SYSDATE with CURRENT_DATE. CURRENT_DATE returns the database server time adjusted for the client's time zone. SYSDATE is never adjusted.

A few other functions I should have learned earlier:

DBTIMEZONE: Returns the offset from Zulu time of the database.

SESSIONTIMEZONE: As above, but for the session.

SYSTEMTIMESTAMP: Like SYSDATE, but returns a timestamp with time zone offset

CURRENT_TIMESTAMP: This timestamp is adjusted for the session.

LOCALTIMESTAMP: Like CURRENT_TIMESTAMP, but without the time stamp offset.