Forum OpenACS Development: Date arithmetic in Oracle and Postgres, and daylight savings time (ugh)
SQL> select to_char(to_date('2001-03-31 10:00:00','YYYY-MM-DD HH24:MI:SS') + 1,'YYYY-MM-DD HH24:MI:SS') from dual; TO_CHAR(TO_DATE('20 ------------------- 2001-04-01 10:00:00In Postgres,
jowell=# select timestamp '2001-03-31 10:00:00' + interval '1 day'; ?column? ------------------------ 2001-04-01 11:00:00-04 (1 row)Note that 11:00:00-4 is EDT, wich is the correct timezone (here in Cambridge, MA) on 2001-04-01.
The problem I'm having is that when dealing with recurrence in acs-events port, the resulting date-time combination is off by an hour whenever a recurrence date involves the switchover to/from DST. Say an event occurs on 2001-03-31 at 10:00:00am, and I want the the event to recur for one day. This involves adding a day to the original event date, so Oracle will say the event will recur on 2001-04-01 at 10:00:00, but Postgres will say the event will recur on 2001-04-01 at 11:00:00. I like the extra sleep that Postgres gives me, but I don't think this is what is expected.
The acs-events code actually does something similar to the following date arithmetic:
jowell=# select timestamp '2001-03-31 10:00:00' + (timestamp '2001-04-01' - timestamp '2001-03-31'); ?column? ------------------------ 2001-04-01 11:00:00-04 (1 row)That is, it first calculates the target date 2001-04-01, and then adds the difference between the event date 2001-03-31 and the target date to the original event date to get the recurrent event date (and time). It may seem weird logic, but it nicely takes care of date-time arithmetic when the recurrent event date does *not* fall on a DST changeover date, even if it overlaps the DST changeover date:
jowell=# select timestamp '2001-03-31 10:00:00' + (timestamp '2001-04-02' - timestamp '2001-03-31'); ?column? ------------------------ 2001-04-02 10:00:00-04 (1 row)So, when the event above recurs for two days, the date-time combination for the recurrent event that does not not fall on the DST changeover day has the expected time (10:00am).
Short of adding code to the API to deal with DST, is there a more obvious way of making sure that the time is taken care of when a DST changeover is involved? Or perhaps this is too esoteric?
Definitely worth asking about, though. I know that interval arithmetic isn't 100% correct at the moment but am uncertain of the details...
It does make sense, more or less. Adding "1 day" is the same as adding "24 hours", which is a day after all. Yet, you could argue that it should only bump the day, and leave the rest of the timestamp alone. In different contexts, either behavior will be confusing.
One way to deal with this would be to strip out the year/month/day portion, bump it by a day, and add the hour/minute/second portion back in. Ugly, but it could be done in a PL/pgSQL routine we could make available globally and it would survive fixing of this bug (if it is a bug, which it might or might not be).