Thread: birthday calculation

birthday calculation

From
Ben
Date:
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?


Re: birthday calculation

From
Pavel Stehule
Date:
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
>


Re: birthday calculation

From
Dennis Gearon
Date:
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 />

Re: birthday calculation

From
nolan@celery.tssi.com
Date:
> 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';




Re: birthday calculation

From
Network Administrator
Date:
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

Re: birthday calculation

From
greg@turnstep.com
Date:
-----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-----