Обсуждение: birthday calculation

It must be late, because I cannot seem to figure this out. I've got a
field which has a user's birthday - I want to figure out how old they
are in terms of years.

If I just do something like:

select current_date - user.bday;

I get their age in days, which doesn't let me take leap years into
account. Is there a simple magic date_diff function that I'm missing? Or
lacking that some other way to get postgres to do the date calculations?

Hello

SELECT age(CURRENT_DATE, date '730715');
           age
--------------------------
 30 years 7 days 23:00:00
(1 row)

bye
ps


On 22 Jul 2003, Ben wrote:

> It must be late, because I cannot seem to figure this out. I've got a
> field which has a user's birthday - I want to figure out how old they
> are in terms of years.
>
> If I just do something like:
>
> select current_date - user.bday;
>
> I get their age in days, which doesn't let me take leap years into
> account. Is there a simple magic date_diff function that I'm missing? Or
> lacking that some other way to get postgres to do the date calculations?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>

How come it gives 23 hours, is that correct?Pavel Stehule wrote: <blockquote type="cite"><pre wrap="">Hello

SELECT age(CURRENT_DATE, date '730715');          age
--------------------------30 years 7 days 23:00:00
(1 row)

bye
ps


On 22 Jul 2003, Ben wrote:
 </pre><blockquote type="cite"><pre wrap="">It must be late, because I cannot seem to figure this out. I've got a
field which has a user's birthday - I want to figure out how old they
are in terms of years.

If I just do something like:

select current_date - user.bday;

I get their age in days, which doesn't let me take leap years into
account. Is there a simple magic date_diff function that I'm missing? Or
lacking that some other way to get postgres to do the date calculations?


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your     joining column's datatypes do not match
   </pre></blockquote><pre wrap="">

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to <a class="moz-txt-link-abbreviated"
href="mailto:majordomo@postgresql.org">majordomo@postgresql.org</a>
 </pre></blockquote><br /><br />

> I get their age in days, which doesn't let me take leap years into
> account. Is there a simple magic date_diff function that I'm missing? Or
> lacking that some other way to get postgres to do the date calculations?

There are two ways to do this, the quick and dirty and almost accurate
method, which is to divide by 365.25 and truncate to years, or to
extract the year.

Here's a function I wrote that returns the age in years.  I suppose
there are better ways to do this, when I wrote this several months
ago I knew less about date handling in postgres than I do now.

But it still works for me and I haven't had the inclination to rewrite it:

create or replace function public.age_in_years(date, date)
returns integer as
'
DECLARE
  date1 alias for $1;
  date2 alias for $2;
BEGIN

  if date1 is null or date2 is null then
     return NULL;
  end if;
  return cast( coalesce(substring(age(date1, date2) from ''(.*) year''),''0'')
as int);
END
' language 'plpgsql';

hmmmm, I use this on an ecommunities type site- I haven't seen any errors.  This
is also a pg 7.1.3

As an example, something like this might be executed:

select date_trunc('year',age(to_date(dob,'MM/DD/YYYY'))) as age from members
where memid=101;


Quoting Ben <bench@silentmedia.com>:

> It must be late, because I cannot seem to figure this out. I've got a
> field which has a user's birthday - I want to figure out how old they
> are in terms of years.
>
> If I just do something like:
>
> select current_date - user.bday;
>
> I get their age in days, which doesn't let me take leap years into
> account. Is there a simple magic date_diff function that I'm missing? Or
> lacking that some other way to get postgres to do the date calculations?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>


--
Keith C. Perry
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> I get their age in days, which doesn't let me take leap years into account.


SELECT extract(year FROM age(user.bday));


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200307231714

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE/HvtGvJuQZxSWSsgRAhhZAJ91pLMPxeKexIPWn8A+heShGLLCOQCeN2/k
Cl4gJIVF7Nakq9hbJrt19pg=
=Gjos
-----END PGP SIGNATURE-----