Обсуждение: upgrading from 7.3.5 to 8.1.5

I've got a couple of problems/questions - all in one table.  These commands
came from pg_dump on 7.3.5:

CREATE TABLE payperiod (
     pay real NOT NULL,
     lo_date timestamp with time zone,
     hi_date timestamp with time zone,
     "input" timestamp with time zone,
     payday date,
     notes text
);
INSERT INTO payperiod VALUES (2006.01, '2005-12-22 00:00:00-05',
'2006-01-04 23:59:00-05', '2006-01-06 09:30:00-05', '2006-01-12', '');
INSERT INTO payperiod VALUES (2006.02, '2006-01-05 00:00:00-05',
'2006-01-18 23:59:00-05', '2006-01-20 09:30:00-05', '2006-01-26', '');
INSERT INTO payperiod VALUES (2006.03, '2006-01-19 00:00:00-05',
'2006-02-01 23:59:00-05', '2006-02-03 09:30:00-05', '2006-02-09', '');
INSERT INTO payperiod VALUES (2006.04, '2006-02-02 00:00:00-05',
'2006-02-15 23:59:00-05', '2006-02-17 09:30:00-05', '2006-02-23', '');
INSERT INTO payperiod VALUES (2006.05, '2006-02-16 00:00:00-05',
'2006-03-01 23:59:00-05', '2006-03-03 09:45:00-05', '2006-03-09', '');
INSERT INTO payperiod VALUES (2006.06, '2006-03-02 00:00:00-05',
'2006-03-15 23:59:00-05', '2006-03-17 10:00:00-05', '2006-03-23', '');
INSERT INTO payperiod VALUES (2006.07, '2006-03-16 00:00:00-05',
'2006-03-29 23:59:00-05', '2006-03-31 10:00:00-05', '2006-04-06', '');
INSERT INTO payperiod VALUES (2006.08, '2006-03-30 00:00:00-05',
'2006-04-12 23:59:00-04', '2006-04-13 09:40:00-04', '2006-04-20', 'Early
Cut-off');
INSERT INTO payperiod VALUES (2006.09, '2006-04-13 00:00:00-04',
'2006-04-26 23:59:00-04', '2006-04-28 09:30:00-04', '2006-05-04', '');
INSERT INTO payperiod VALUES (2006.1, '2006-04-27 00:00:00-04', '2006-05-10
23:59:00-04', '2006-05-12 10:10:00-04', '2006-05-18', '');
INSERT INTO payperiod VALUES (2006.11, '2006-05-11 00:00:00-04',
'2006-05-24 23:59:00-04', '2006-05-26 10:45:00-04', '2006-06-01', '');
INSERT INTO payperiod VALUES (2006.12, '2006-05-25 00:00:00-04',
'2006-06-07 23:59:00-04', '2006-06-09 09:30:00-04', '2006-06-15', '');
INSERT INTO payperiod VALUES (2006.13, '2006-06-08 00:00:00-04',
'2006-06-21 23:59:00-04', '2006-06-23 09:30:00-04', '2006-06-29', '');
INSERT INTO payperiod VALUES (2006.14, '2006-06-22 00:00:00-04',
'2006-07-05 23:59:00-04', '2006-07-07 09:30:00-04', '2006-07-13', '');
INSERT INTO payperiod VALUES (2006.15, '2006-07-06 00:00:00-04',
'2006-07-19 23:59:00-04', '2006-07-21 09:30:00-04', '2006-07-27', '');
INSERT INTO payperiod VALUES (2006.16, '2006-07-20 00:00:00-04',
'2006-08-02 23:59:00-04', '2006-08-03 16:30:00-04', '2006-08-10', 'Early
Cut-off');
INSERT INTO payperiod VALUES (2006.17, '2006-08-03 00:00:00-04',
'2006-08-16 23:59:00-04', '2006-08-18 09:30:00-04', '2006-08-24', '');
INSERT INTO payperiod VALUES (2006.18, '2006-08-17 00:00:00-04',
'2006-08-30 23:59:00-04', '2006-09-01 08:30:00-04', '2006-09-07', 'Early
Cut-off');
INSERT INTO payperiod VALUES (2006.19, '2006-08-31 00:00:00-04',
'2006-09-13 23:59:00-04', '2006-09-15 10:15:00-04', '2006-09-21', 'Stat');
INSERT INTO payperiod VALUES (2006.2, '2006-09-14 00:00:00-04', '2006-09-27
23:59:00-04', '2006-09-29 10:00:00-04', '2006-10-05', '');
INSERT INTO payperiod VALUES (2006.21, '2006-09-28 00:00:00-04',
'2006-10-11 23:59:00-04', '2006-10-13 10:00:00-04', '2006-10-19', 'Stat');
INSERT INTO payperiod VALUES (2006.22, '2006-10-12 00:00:00-04',
'2006-10-25 23:59:00-04', '2006-10-27 10:00:00-04', '2006-11-02', '');
INSERT INTO payperiod VALUES (2006.23, '2006-10-26 00:00:00-04',
'2006-11-08 23:59:00-05', '2006-11-10 11:02:00-05', '2006-11-16', '');
INSERT INTO payperiod VALUES (2006.25, '2006-11-23 00:00:00-05',
'2006-12-06 23:59:00-05', '2006-12-08 10:00:00-05', '2006-12-14', '');
INSERT INTO payperiod VALUES (2006.24, '2006-11-09 00:00:00-05',
'2006-11-22 23:59:00-05', '2006-11-24 10:00:00-05', '2006-11-30', '');
INSERT INTO payperiod VALUES (2006.26, '2006-12-07 00:00:00-05',
'2006-12-20 23:59:00-05', '2006-12-21 10:25:00-05', '2006-12-28', 'Early
Cut-off');

CREATE UNIQUE INDEX payperiod_pkey ON payperiod USING btree (pay);


All output in these examples is from 8.1.5:

1) select and pg_dump produce different results. Why does .06 change to
.0601 and .07 to .0699?
$ pg_dump -t payperiod -d | grep 01,
INSERT INTO payperiod VALUES (2006.01, '2005-12-22 00:00:00-05',
'2006-01-04 23:59:00-05', '2006-01-06 09:30:00-05', '2006-01-12', '');
INSERT INTO payperiod VALUES (2006.0601, '2006-03-02 00:00:00-05',
'2006-03-15 23:59:00-05', '2006-03-17 10:00:00-05', '2006-03-23', '');
INSERT INTO payperiod VALUES (2006.1801, '2006-08-17 00:00:00-04',
'2006-08-30 23:59:00-04', '2006-09-01 08:30:00-04', '2006-09-07', 'Early
Cut-off');


$ pg_dump -t payperiod -d | grep 99,
INSERT INTO payperiod VALUES (2006.0699, '2006-03-16 00:00:00-05',
'2006-03-29 23:59:00-05', '2006-03-31 10:00:00-05', '2006-04-06', '');
INSERT INTO payperiod VALUES (2006.1899, '2006-08-31 00:00:00-04',
'2006-09-13 23:59:00-04', '2006-09-15 10:15:00-04', '2006-09-21', 'Stat');


2) age() changed from 7.3.5 to 8.1.5?

  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';
         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)

On 8.1.5, the query returns no results. How do I now identify the
payperiods where there is a time change?

3) I was surprised to see that d14 contains '13 days 24:00:00' instead of
'14 days' (which is what 7.3.5 returned), but my query works anyway; so I
don't much care about this detail.

Frank

Frank Bax <fbax@sympatico.ca> writes:
> 1) select and pg_dump produce different results. Why does .06 change to
> .0601 and .07 to .0699?

It's actually the same values --- remember that float4 is only good to
a bit over six decimal digits anyway.  Post-7.3 versions of pg_dump set
"extra_float_digits" to provide a couple of guard digits in the output:

regression=# select 2006.18::real;
 float4
---------
 2006.18
(1 row)

regression=# set extra_float_digits TO 2;
SET
regression=# select 2006.18::real;
  float4
-----------
 2006.1801
(1 row)

On machines with properly-written floating point I/O, this ensures you
get back exactly the same float4 bit pattern you had stored before.
But it looks a bit funny, because the last digit is not "accurate"
in decimal terms.

> 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.

            regards, tom lane

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

At 12:21 AM 12/31/06, Frank Bax wrote:

>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.
>
>
>You mentioned GMT.  Can I force age() to use GMT or can I convert
>timestamptz to GMT and then use age()?


I figured it out (I found section 9.9.3):
select age(hi_date at time zone 'GMT',lo_date at time zone 'GMT')+'1 min'

BTW: Why isn't timezone() function listed in table 9-26?  Shouldn't "at
time zone" be considered an operator?

Frank