Skip site navigation (1) Skip section navigation (2)

Re: upgrading from 7.3.5 to 8.1.5

From: Frank Bax <fbax(at)sympatico(dot)ca>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: upgrading from 7.3.5 to 8.1.5
Date: 2006-12-31 05:21:38
Message-ID: 5.2.1.1.0.20061230232815.029bae70@pop6.sympatico.ca (view raw or flat)
Thread:
Lists: pgsql-novice
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()? 


In response to

Responses

pgsql-novice by date

Next:From: Frank BaxDate: 2007-01-01 17:30:07
Subject: Re: upgrading from 7.3.5 to 8.1.5
Previous:From: Tom LaneDate: 2006-12-31 01:09:44
Subject: Re: upgrading from 7.3.5 to 8.1.5

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group