Thread: Daylight Savings Time

First of all: daylight savings time sucks.  Now that I've gotten that off
my chest, I have a question.  I ran across something that I know is
theoretically correct but is not functionally correct (for me at least) and
I wanted some more input.  Basically, when I select a timestamp from the
table, the timezone (based on daylight savings time) that is returned is
the one based on the time itself.  So, if it is currently November 5th and
Mountain Standard Time (MST), and I select a timestamp that is
in...September let's say (that's Mountain Daylight Time (MDT)), it will
return the time in MDT.  What I'd like it to do is return the time in MST,
the time I'm currently in.

If you don't understand why I would want it that way, here's an example.
So, I've got a calendar application that allows me to insert events and the
system emails me a reminder at a certain time before the event (1 hour, 2
hours, 1 day, etc).  If the current date is October 20th (before the
Daylight Savings Time change on the 28th) and I'm inserting calendar events
for November, once Daylight Savings Time occurs, all of my events will be
off by one hour.

My question: is there a good way to deal with all of this?

My answer: the only thing I could come up with was to patch PostgreSQL so
that when it encodes the timestamp to be displayed, it checks the tm_isdst
field of the time structure for the current time, not the stored time.
I've made an option called "now_zone" to enable/disable this on the fly.

Here's an example:

www=# select start_date, end_date from event_recur where eid = 452;
       start_date       |        end_date
------------------------+------------------------
 2001-09-16 00:00:00-06 | 2001-12-16 00:00:00-07
(1 row)

www=# set now_zone = true;
SET VARIABLE

www=# select start_date, end_date from event_recur where eid = 452;
       start_date       |        end_date
------------------------+------------------------
 2001-09-16 00:00:00-07 | 2001-12-16 00:00:00-07
(1 row)

If anyone wants to take a look at this patch, it's located here:
http://www.superiorcomm.net/files/now_zone.patch

I would really appreciate some comments/feedback on whether or not my patch
is a good idea or if there's a better way to handle this.  Thanks!

--
PC Drew

Superior Communications
1455 Dixon Ave, Suite 310
Lafayette, CO 80026

pc@superiorcomm.net
720-841-4543

PC Drew <pc@superiorcomm.net> writes:
> First of all: daylight savings time sucks.  Now that I've gotten that off
> my chest, I have a question.  I ran across something that I know is
> theoretically correct but is not functionally correct (for me at least) and
> I wanted some more input.

Pull down 7.2beta and try the "timestamp without time zone" datatype.

regression=# select '2001-10-30 17:00'::timestamp;
      timestamptz
------------------------
 2001-10-30 17:00:00-05
(1 row)

regression=# select '2001-10-30 17:00'::timestamp - '10 days'::interval;
        ?column?
------------------------
 2001-10-20 18:00:00-04
(1 row)

regression=# select '2001-10-30 17:00'::timestamp without time zone;
      timestamp
---------------------
 2001-10-30 17:00:00
(1 row)

regression=# select '2001-10-30 17:00'::timestamp without time zone
regression-# - '10 days'::interval;
      ?column?
---------------------
 2001-10-20 17:00:00
(1 row)

Doing the arithmetic in this type and then casting back to timestamp
with time zone seems like it will do what you want.

Having said that, I agree that this behavior is not quite right.
The interval type should consider '1 day' and '24 hours' to be
two different things; then it would be possible to expect
select '2001-10-30 17:00'::timestamp - '10 days'::interval;
to produce 17:00 local time rather than 18:00.
See previous discussions...

            regards, tom lane