Re: upgrading from 7.3.5 to 8.1.5

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

>Frank Bax <fbax@sympatico.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()?