Thread: possible bug on age() function (8.2.4 , 8.3.6)
Hi all,
here is a little test case for a problem we run into in our
developpement
Is it a bug or a setting problem ?
try
select (current_timestamp),(current_timestamp - interval '1008 hours');
report this value in age()
select age('2009-06-23 18:36:05.064066+02' ,'"2009-05-12 18:36:05.064066
+02"');
then use all in this :
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2009-06-23 18:36:05.064066+02') -
EXTRACT(EPOCH FROM TIMESTAMP '"2009-05-12 18:36:05.064066+02"'),
EXTRACT(EPOCH FROM interval '1 mon 11 days');
Can I workaround this ?
Regards
"Philippe Amelant" <pamelant@companeo.com> writes:
> Can I workaround this ?
Don't assume that "1 month" means a constant number of seconds.
regards, tom lane
Ok but if I work with hours or whatever the problem is still there
SELECT (EXTRACT(EPOCH FROM TIMESTAMP '2009-06-23 18:36:05.064066+02') -
EXTRACT(EPOCH FROM TIMESTAMP '"2009-05-12 18:36:05.064066+02"'))/3600,
EXTRACT(EPOCH FROM interval '1008 hours')/3600, age('2009-06-23
18:36:05.064066+02' ,'"2009-05-12 18:36:05.064066+02"') > interval '1007
hours';
The third test should be true and not false
Le mardi 23 juin 2009 à 13:43 -0400, Tom Lane a écrit :
> "Philippe Amelant" <pamelant@companeo.com> writes:
> > Can I workaround this ?
>
> Don't assume that "1 month" means a constant number of seconds.
>
> regards, tom lane
>
On 2009-06-24, Philippe Amelant <pamelant@companeo.com> wrote:
> Ok but if I work with hours or whatever the problem is still there
>
> SELECT (EXTRACT(EPOCH FROM TIMESTAMP '2009-06-23 18:36:05.064066+02') -
> EXTRACT(EPOCH FROM TIMESTAMP '"2009-05-12 18:36:05.064066+02"'))/3600,
> EXTRACT(EPOCH FROM interval '1008 hours')/3600, age('2009-06-23
> 18:36:05.064066+02' ,'"2009-05-12 18:36:05.064066+02"') > interval '1007
> hours';
>
> The third test should be true and not false
The third test is comparing a double with an interval.
compare like with like.
SELECT (EXTRACT(EPOCH FROM TIMESTAMP '2009-06-23 18:36:05.064066+02') -
EXTRACT(EPOCH FROM TIMESTAMP '"2009-05-12 18:36:05.064066+02"'))/3600,
EXTRACT(EPOCH FROM interval '1008 hours')/3600, age('2009-06-23
18:36:05.064066+02' ,'"2009-05-12 18:36:05.064066+02"')
> EXTRACT(EPOCH FROM interval '1007 hours');
Le mercredi 24 juin 2009 à 12:45 +0000, Jasen Betts a écrit :
> On 2009-06-24, Philippe Amelant <pamelant@companeo.com> wrote:
> > Ok but if I work with hours or whatever the problem is still there
> >
> > SELECT (EXTRACT(EPOCH FROM TIMESTAMP '2009-06-23 18:36:05.064066+02') -
> > EXTRACT(EPOCH FROM TIMESTAMP '"2009-05-12 18:36:05.064066+02"'))/3600,
> > EXTRACT(EPOCH FROM interval '1008 hours')/3600, age('2009-06-23
> > 18:36:05.064066+02' ,'"2009-05-12 18:36:05.064066+02"') > interval '1007
> > hours';
> >
> > The third test should be true and not false
>
> The third test is comparing a double with an interval.
> compare like with like.
>
> SELECT (EXTRACT(EPOCH FROM TIMESTAMP '2009-06-23 18:36:05.064066+02') -
> EXTRACT(EPOCH FROM TIMESTAMP '"2009-05-12 18:36:05.064066+02"'))/3600,
> EXTRACT(EPOCH FROM interval '1008 hours')/3600, age('2009-06-23
> 18:36:05.064066+02' ,'"2009-05-12 18:36:05.064066+02"')
> > EXTRACT(EPOCH FROM interval '1007 hours');
>
>
>
From de doc : age(timestamp, timestamp) return an interval so if I
wrote
select age('2009-06-23 18:36:05.064066+02' ,'"2009-05-12 18:36:05.064066
+02"') > interval '1000 hours';
I think I compare an interval with an interval.
the result should be true because there is 1008 hours between the 2
dates
But I need to substract more than 24 hours to get a 'true'
select age('2009-06-23 18:36:05.064066+02' ,'"2009-05-12 18:36:05.064066
+02"') > interval '983 hours';
So age() lost 24 H.
On 2009-06-24, Philippe Amelant <pamelant@companeo.com> wrote:
> Le mercredi 24 juin 2009 à 12:45 +0000, Jasen Betts a écrit :
>> On 2009-06-24, Philippe Amelant <pamelant@companeo.com> wrote:
>> > Ok but if I work with hours or whatever the problem is still there
>> >
>> > SELECT (EXTRACT(EPOCH FROM TIMESTAMP '2009-06-23 18:36:05.064066+02') -
>> > EXTRACT(EPOCH FROM TIMESTAMP '"2009-05-12 18:36:05.064066+02"'))/3600,
>> > EXTRACT(EPOCH FROM interval '1008 hours')/3600, age('2009-06-23
>> > 18:36:05.064066+02' ,'"2009-05-12 18:36:05.064066+02"') > interval '1007
>> > hours';
>> >
>> > The third test should be true and not false
>>
>> The third test is comparing a double with an interval.
>> compare like with like.
>>
>> SELECT (EXTRACT(EPOCH FROM TIMESTAMP '2009-06-23 18:36:05.064066+02') -
>> EXTRACT(EPOCH FROM TIMESTAMP '"2009-05-12 18:36:05.064066+02"'))/3600,
>> EXTRACT(EPOCH FROM interval '1008 hours')/3600, age('2009-06-23
>> 18:36:05.064066+02' ,'"2009-05-12 18:36:05.064066+02"')
>> > EXTRACT(EPOCH FROM interval '1007 hours');
>>
>
>From de doc : age(timestamp, timestamp) return an interval so if I
> wrote
> select age('2009-06-23 18:36:05.064066+02' ,'"2009-05-12 18:36:05.064066
> +02"') > interval '1000 hours';
>
> I think I compare an interval with an interval.
that does but they are different units. one in months and days and the other in hours.
if you want to count days subtract dates,
> the result should be true because there is 1008 hours between the 2
> dates
but there is not reliably 1008 hours in "1 mon 11 days"
if the context of the interval is important apply it,
select timestamptz '2009-05-12 18:36:05.064066+02'
,timestamptz '"2009-06-23 18:36:05.064066+02"'
,interval '1007 hours'
,timestamptz '2009-05-12 18:36:05.064066+02' + interval '1007 hours'
,timestamptz '2009-05-12 18:36:05.064066+02' + interval '1007 hours'
< timestamptz '"2009-06-23 18:36:05.064066+02"';
> But I need to substract more than 24 hours to get a 'true'
>
> select age('2009-06-23 18:36:05.064066+02' ,'"2009-05-12 18:36:05.064066
> +02"') > interval '983 hours';
age() works but is not well suited to that use:
select timestamptz '"2009-05-12 18:36:05.064066 +02"' + age('2009-06-23 18:36:05.064066+02' ,'"2009-05-12
18:36:05.064066+02"');