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