use Perl

• Don't work in seconds from 1970 (Score:1)

  by $Bob (668) on 2007.08.07 13:07 (#56816)

  I dealt with this issue a long time ago (Oracle 7 or 8, IIRC), and I haven't had to deal with Oracle in quite a while, so forgive me if my examples seem a bit hazy, or if Oracle's handling of these issues has improved.
  
  In my experience, Oracle doesn't understand time zones, daylight savings adjustments, leap seconds, or anything else that makes dealing with date/times easy. So, the shortcut I eventually came up with was to use seconds from "now" as the epoch. That way you can deal with the various other issues in Perl, where the solutions are well documented.
  
  So in Perl, I'd pull a "date" out of the database and add it to the value of time(), then convert that to whatever date/time format I needed. I'd do the reverse if I was sending a date to Oracle.
  
  In Oracle, I'd get a value, divide it by (24 * 60 * 60), and add it to whatever function gave you the current timestamp. I'd do the reverse if I was sending a date to Perl.
  
  Clock drift may skew the results slightly, but that's correctable to within a second by using NTP on the machines.
  
  I don't know how things have changed since then, but I hope this helps.

  Reply to This

  • Re: (Score:2)

    by jdavidb (1361)

    Today Oracle understands time zones and daylight savings time if you use the TIMESTAMP WITH TIMEZONE data type, which is sort of a superset of the DATE datatype. There is also a plain TIMESTAMP datatype that has some features beyond DATE but does not do timezones.

    The only time I really had to mess with this was when I had Log4perl logging to a database table, and I demanded that the timestamp field be a TIMESTAMP WITH TIMEZONE in UTC time, with UTC as the TIMEZONE value. Somewhere back in my journal I h

    --
    J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers