Re: upgrading from 7.3.5 to 8.1.5

At 08:09 PM 12/30/06, Tom Lane wrote:

>Frank Bax <fbax(at)sympatico(dot)ca> writes:
> > 2) age() changed from 7.3.5 to 8.1.5?
>
>I see this in the 7.4.7 release notes:
> Make age(timestamptz) do calculation in local timezone not GMT
>It looks like the examples you cite are crossing DST boundaries, so
>the one-hour difference is correct. Depending on what you are trying
>to accomplish, you might wish to do the calculation in timestamp without
>time zone.

Sorry, I made a mistake in my first post, I said all output was from
8.3.5 - that's not correct - let's try again.

select lo_date,hi_date,age(hi_date,lo_date)+'1 min' as d14 from payperiod
where age(hi_date,lo_date)+'1 min' <> '14 days';

7.3.5 produced:

lo_date | hi_date | d14
------------------------+------------------------+---------------
2006-03-30 00:00:00-05 | 2006-04-12 23:59:00-04 | 13 days 23:00
2006-10-26 00:00:00-04 | 2006-11-08 23:59:00-05 | 14 days 01:00
(2 rows)

8.1.5 produced no results, which is incorrect.

Table has two timestamps, which are the beginning (lo_date) and end
(hi_date) for a two-week payroll payperiod. The begin date always has time
00:00, end date always has time 23:59, that's why I added '1 min' to age()
between two dates. I need to identify which rows represent something other
than 24*14=336 hours which happens every time clocks change because of
DST. I expect to get two rows per year from the above query.

My application is used to pay people. When they work overnight in the fall
on a weekend when the time changes; the normal 9-hour shift really 10 hours
of work. Here's an example with an employee who worked from midnight to
9am with a DST time change:

select age('2006-10-29 09:00'::timestamp,'2006-10-29
00:00'::timestamp),age('2006-10-29 09:00'::timestamptz,'2006-10-29
00:00'::timestamptz),version();
age | age | version
-------+-------+---------------------------------------------------------------------
09:00 | 10:00 | PostgreSQL 7.3.5 on i386-unknown-openbsd3.5, compiled by
GCC 2.95.3

select age('2006-10-29 09:00'::timestamp,'2006-10-29
00:00'::timestamp),age('2006-10-29 09:00'::timestamptz,'2006-10-29
00:00'::timestamptz),version();
age | age | version
----------+----------+-----------------------------------------------------------------------------------------
09:00:00 | 09:00:00 | PostgreSQL 8.1.5 on i386-unknown-openbsd4.0,
compiled by GCC cc (GCC) 3.3.5 (propolice)

Only 7.3.5 with time zone got the answer right. People will not be happy
if they only get 9 hours pay (And yes, they only get 8 hours pay in the
spring for the same shift). I'm hoping we can find a way for 8.1.5 to
produce the same results as 7.3.5 with tz. Is there another function I can
use?

You mentioned GMT. Can I force age() to use GMT or can I convert
timestamptz to GMT and then use age()?