Thread: AGE function
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/
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
--- 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+07to_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+07to_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
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 ===
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
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): Fixtimestamptz_age() to do calculation in local timezone not GMT,
perbug1332.
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
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