Re: upgrading from 7.3.5 to 8.1.5

[Frank Bax <fbax(at)sympatico(dot)ca>]

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()?