Oracle and Postgres seem to behave differently when daylight savings
time (DST) is involved. To illustrate, I choose 2001-03-31 because the
switch to daylight savings time occurs the next day, 2001-04-01.
Suppose we want to add a full day to 2001-03-31, 10:00am. In Oracle,
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:00
In 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?
