pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAGE function
7+ messages / 4 participants
[nested] [flat]
* AGE function
@ 2005-09-07 05:05 Louise Catherine <r1c4n@yahoo.com>
2005-09-07 06:26 ` Re: AGE function Michael Fuhr <mike@fuhr.org>
2005-09-07 07:46 ` Re: AGE function A. Kretschmer <akretschmer@despammed.com>
2005-09-07 15:13 ` Re: AGE function Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 7+ messages in thread
From: Louise Catherine @ 2005-09-07 05:05 UTC (permalink / raw)
To: pgsql-sql
When I execute this statement :
select AGE(TO_DATE('20041101','yyyymmdd'),
TO_DATE('19991201','yyyymmdd'))
at postgre 7.3.3, the result :
age
---------------------
4 years 11 mons 1 day
at postgre 8.0.3, the result :
age
---------------
4 years 11 mons
My question :
1. How does postgre 7.3.3 calculate AGE function?
2. Why the result produced by postgre 7.3.3
is different from postgre 8.0.3 ?
Thanks,
Louise
______________________________________________________
Click here to donate to the Hurricane Katrina relief effort.
http://store.yahoo.com/redcross-donate3/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: AGE function
2005-09-07 05:05 AGE function Louise Catherine <r1c4n@yahoo.com>
@ 2005-09-07 06:26 ` Michael Fuhr <mike@fuhr.org>
2005-09-07 07:22 ` Re: AGE function Louise Catherine <r1c4n@yahoo.com>
2 siblings, 1 reply; 7+ messages in thread
From: Michael Fuhr @ 2005-09-07 06:26 UTC (permalink / raw)
To: Louise Catherine <r1c4n@yahoo.com>; +Cc: pgsql-sql
On Tue, Sep 06, 2005 at 10:05:06PM -0700, Louise Catherine wrote:
> When I execute this statement :
> select AGE(TO_DATE('20041101','yyyymmdd'),
> TO_DATE('19991201','yyyymmdd'))
>
> at postgre 7.3.3, the result :
> age
> ---------------------
> 4 years 11 mons 1 day
>
> at postgre 8.0.3, the result :
> age
> ---------------
> 4 years 11 mons
>
> My question :
> 1. How does postgre 7.3.3 calculate AGE function?
> 2. Why the result produced by postgre 7.3.3
> is different from postgre 8.0.3 ?
I get the same answer ("4 years 11 mons") in 7.2.8, 7.3.10, 7.4.8,
8.0.3, and 8.1beta1. Have you verified that to_date() is returning
the correct dates? What are the results of the following queries
on each of your systems?
SELECT TO_DATE('19991201','yyyymmdd'), TO_TIMESTAMP('19991201','yyyymmdd');
SELECT TO_DATE('20041101','yyyymmdd'), TO_TIMESTAMP('20041101','yyyymmdd');
SHOW TimeZone;
What operating system are you using? Prior to 8.0, PostgreSQL
relied on the system's timezone files; as of 8.0 it has its own
timezone database. I don't know if that matters, but it's one
difference between 8.0 and previous versions that might be relevant
to the problem.
BTW, it's "PostgreSQL" or "Postgres," not "postgre."
--
Michael Fuhr
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: AGE function
2005-09-07 05:05 AGE function Louise Catherine <r1c4n@yahoo.com>
2005-09-07 06:26 ` Re: AGE function Michael Fuhr <mike@fuhr.org>
@ 2005-09-07 07:22 ` Louise Catherine <r1c4n@yahoo.com>
2005-09-07 14:24 ` Re: AGE function Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 7+ messages in thread
From: Louise Catherine @ 2005-09-07 07:22 UTC (permalink / raw)
To: pgsql-sql; +Cc: Michael Fuhr <mike@fuhr.org>
--- Michael Fuhr <mike@fuhr.org> wrote:
> On Tue, Sep 06, 2005 at 10:05:06PM -0700, Louise
> Catherine wrote:
> > When I execute this statement :
> > select AGE(TO_DATE('20041101','yyyymmdd'),
> > TO_DATE('19991201','yyyymmdd'))
> >
> > at postgre 7.3.3, the result :
> > age
> > ---------------------
> > 4 years 11 mons 1 day
> >
> > at postgre 8.0.3, the result :
> > age
> > ---------------
> > 4 years 11 mons
> >
> > My question :
> > 1. How does postgre 7.3.3 calculate AGE function?
> > 2. Why the result produced by postgre 7.3.3
> > is different from postgre 8.0.3 ?
>
> I get the same answer ("4 years 11 mons") in 7.2.8,
> 7.3.10, 7.4.8,
> 8.0.3, and 8.1beta1. Have you verified that
> to_date() is returning
> the correct dates? What are the results of the
> following queries
> on each of your systems?
>
> SELECT TO_DATE('19991201','yyyymmdd'),
> TO_TIMESTAMP('19991201','yyyymmdd');
> SELECT TO_DATE('20041101','yyyymmdd'),
> TO_TIMESTAMP('20041101','yyyymmdd');
> SHOW TimeZone;
at PostgreSQL 7.3.3 :
to_date to_timestamp
---------- ---------------------
1999-12-01 1999-12-01 00:00:00+07
to_date to_timestamp
---------- ---------------------
2004-11-01 2004-11-01 00:00:00+07
TimeZone
-----------
unknown
at PostgreSQL 8.0.3 :
to_date to_timestamp
---------- ---------------------
1999-12-01 1999-12-01 00:00:00+07
to_date to_timestamp
---------- ---------------------
2004-11-01 2004-11-01 00:00:00+07
TimeZone
------------
Asia/Jakarta
> What operating system are you using?
I'm using SuSE Linux 9.0 for the operating system
The result from your queries are similar, so what's
wrong in my queries? What should I do? Cause I must
migrate database from PostgreSQL 7.3.3 to
PostgreSQl 8.0.3.
> BTW, it's "PostgreSQL" or "Postgres," not "postgre."
Sorry about the name :)
Thanks,
Louise
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: AGE function
2005-09-07 05:05 AGE function Louise Catherine <r1c4n@yahoo.com>
2005-09-07 06:26 ` Re: AGE function Michael Fuhr <mike@fuhr.org>
2005-09-07 07:22 ` Re: AGE function Louise Catherine <r1c4n@yahoo.com>
@ 2005-09-07 14:24 ` Michael Fuhr <mike@fuhr.org>
2005-09-07 15:11 ` Re: AGE function Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 7+ messages in thread
From: Michael Fuhr @ 2005-09-07 14:24 UTC (permalink / raw)
To: Louise Catherine <r1c4n@yahoo.com>; +Cc: pgsql-sql
On Tue, Sep 06, 2005 at 10:05:06PM -0700, Louise Catherine wrote:
> 1. How does postgre 7.3.3 calculate AGE function?
> 2. Why the result produced by postgre 7.3.3
> is different from postgre 8.0.3 ?
The change appears to have been committed in 7.4 and later in
response to Bug #1332:
http://archives.postgresql.org/pgsql-bugs/2004-12/msg00013.php
http://archives.postgresql.org/pgsql-committers/2004-12/msg00009.php
http://archives.postgresql.org/pgsql-committers/2004-12/msg00008.php
--
Michael Fuhr
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: AGE function
2005-09-07 05:05 AGE function Louise Catherine <r1c4n@yahoo.com>
2005-09-07 06:26 ` Re: AGE function Michael Fuhr <mike@fuhr.org>
2005-09-07 07:22 ` Re: AGE function Louise Catherine <r1c4n@yahoo.com>
2005-09-07 14:24 ` Re: AGE function Michael Fuhr <mike@fuhr.org>
@ 2005-09-07 15:11 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 7+ messages in thread
From: Michael Fuhr @ 2005-09-07 15:11 UTC (permalink / raw)
To: Louise Catherine <r1c4n@yahoo.com>; +Cc: pgsql-sql
On Wed, Sep 07, 2005 at 08:24:54AM -0600, Michael Fuhr wrote:
> On Tue, Sep 06, 2005 at 10:05:06PM -0700, Louise Catherine wrote:
> > 1. How does postgre 7.3.3 calculate AGE function?
> > 2. Why the result produced by postgre 7.3.3
> > is different from postgre 8.0.3 ?
>
> The change appears to have been committed in 7.4 and later in
> response to Bug #1332:
Specifically, 7.4.7 and later.
--
Michael Fuhr
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: AGE function
2005-09-07 05:05 AGE function Louise Catherine <r1c4n@yahoo.com>
@ 2005-09-07 07:46 ` A. Kretschmer <akretschmer@despammed.com>
2 siblings, 0 replies; 7+ messages in thread
From: A. Kretschmer @ 2005-09-07 07:46 UTC (permalink / raw)
To: pgsql-sql
am 06.09.2005, um 22:05:06 -0700 mailte Louise Catherine folgendes:
> When I execute this statement :
> select AGE(TO_DATE('20041101','yyyymmdd'),
> TO_DATE('19991201','yyyymmdd'))
> ...
> 4 years 11 mons 1 day
> 4 years 11 mons
>
> My question :
> 1. How does postgre 7.3.3 calculate AGE function?
> 2. Why the result produced by postgre 7.3.3
> is different from postgre 8.0.3 ?
How long is a month? 28 days, 29 days, 30 days, 31 days?
select TO_DATE('20041101','yyyymmdd') - TO_DATE('19991201','yyyymmdd');
This is under 7.2.1 and 8.0.3 tha same: 1797. I guess, this is a
rounding problem.
Regards, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: AGE function
2005-09-07 05:05 AGE function Louise Catherine <r1c4n@yahoo.com>
@ 2005-09-07 15:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2005-09-07 15:13 UTC (permalink / raw)
To: Louise Catherine <r1c4n@yahoo.com>; +Cc: pgsql-sql
Louise Catherine <r1c4n@yahoo.com> writes:
> When I execute this statement :
> select AGE(TO_DATE('20041101','yyyymmdd'),
> TO_DATE('19991201','yyyymmdd'))
> at postgre 7.3.3, the result :
> age
> ---------------------
> 4 years 11 mons 1 day
With TimeZone set to 'Asia/Jakarta' on a Linux machine, I can reproduce
that behavior in 7.3.* but not 7.4 and later. I believe this is the
relevant change:
2004-12-01 14:57 tgl
* src/backend/utils/adt/timestamp.c (REL7_4_STABLE): Fix
timestamptz_age() to do calculation in local timezone not GMT, per
bug 1332.
and here is a link to the discussion that prompted the change:
http://archives.postgresql.org/pgsql-bugs/2004-12/msg00013.php
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-09-07 15:13 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-07 05:05 AGE function Louise Catherine <r1c4n@yahoo.com>
2005-09-07 06:26 ` Michael Fuhr <mike@fuhr.org>
2005-09-07 07:22 ` Louise Catherine <r1c4n@yahoo.com>
2005-09-07 14:24 ` Michael Fuhr <mike@fuhr.org>
2005-09-07 15:11 ` Michael Fuhr <mike@fuhr.org>
2005-09-07 07:46 ` A. Kretschmer <akretschmer@despammed.com>
2005-09-07 15:13 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox