Postgres Pro
Downloads
Home   >   mailing lists

Thread: How Many Years have Passed?

How Many Years have Passed?

From
"Ian Harding"
Date:
I have checked the FAQ and other sources and learned that the difference between two dates is the integer number of
days. I want to know how many years are between those two dates.   

Is the only way to do that to do something like checking the julian day first to see if the second one is higher (i.e.
it'spast my birthday), then doing date_part('year', foo) - date_part('year', bar) and adjusting for whether my birthday
haspassed? 

Thanks!

Ian A. Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
(253) 798-3549
mailto: iharding@tpchd.org

Re: How Many Years have Passed?

From
Paul Wehr
Date:
Based on the lack of responses, I would say the short answer to your
question is "yes [that is the only way]".

The longer answer would be "are you sure that is what you want to do?"
 I thought this would be an easy one, given the amout of time I spend
working with dates, but it turns out I've never really need to do it.  I
have always either:

tested to see if a particular [conversational] age has come up:
can_vote=(today::date >= birth_date::date+'18 years'::interval);

or calculated a "biological" age, using a [relatively] fixed unit of
measure like days:
age='today'::date-birth_date::date;

what you are calculating, I would call "conversational age", in that
that is what we would say, if asked in conversation.  For most
applications, I have found it to be of lower resolution (and lower
reproducibility) because of the varying length of years (even worse for
months, which is used as a basis of measurement more times than I care
to think about).  This is also the same reason Postgres will not convert
an interval beween days, months and years.

You have probably already considered most of this, but it makes me feel
better to bring it up.

-paul


Ian Harding wrote:

>I have checked the FAQ and other sources and learned that the difference between two dates is the integer number of
days. I want to know how many years are between those two dates.   
>
>Is the only way to do that to do something like checking the julian day first to see if the second one is higher (i.e.
it'spast my birthday), then doing date_part('year', foo) - date_part('year', bar) and adjusting for whether my birthday
haspassed? 
>
>Thanks!
>
>Ian A. Harding
>Programmer/Analyst II
>Tacoma-Pierce County Health Department
>(253) 798-3549
>mailto: iharding@tpchd.org
>

Re: How Many Years have Passed?

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> I have checked the FAQ and other sources and learned that the difference
> between two dates is the integer number of days.  I want to know how many
> years are between those two dates.

> Is the only way to do that to do something like checking the julian day
> first to see if the second one is higher (i.e. it's past my birthday),
> then doing date_part('year', foo) - date_part('year', bar) and adjusting
> for whether my birthday has passed?

"Julian day" is the number of days since 1 January 4713 BC. This number
will only help you determine which date is chronologically first.

The "Julian date", the number of days since January 1st in a given
year, is not of much help either, since leap years can cause the same
dates to have different Julian dates for different years.

The best way I have come up with is the following, which accepts any two
dates ('AAA' and 'BBB') and spits out the years between them. If you want
March 1, 2000 to March 1, 2001 to NOT count as a full year, just
change the < and > at the end of the extract('day') lines to <= and >=


SELECT CASE WHEN
(
  ( extract('year' FROM datetime('AAA')) -
    extract('year' FROM datetime('BBB')) > 0
  )
  AND
  (
    ( extract('month' FROM datetime('AAA')) <
      extract('month' FROM datetime('BBB'))
    )
    OR
    (
      ( extract('month' FROM datetime('AAA')) =
        extract('month' FROM datetime('BBB'))
      )
      AND
      ( extract('day' FROM datetime('AAA')) <
        extract('day' FROM datetime('BBB'))
      )
    )
  )
)
OR
(
  ( extract('year' FROM datetime('AAA')) -
    extract('year' FROM datetime('BBB')) < 0
  )
  AND
  (
    ( extract('month' FROM datetime('AAA')) >
      extract('month' FROM datetime('BBB'))
    )
    OR
    (
      ( extract('month' FROM datetime('AAA')) =
        extract('month' FROM datetime('BBB'))
      )
      AND
      ( extract('day' FROM datetime('AAA')) >
        extract('day' FROM datetime('BBB'))
      )
    )
  )
)
THEN
  ABS(extract('year' FROM datetime('AAA')) -
      extract('year' FROM datetime('BBB'))) -1
ELSE
  ABS(extract('year' FROM datetime('AAA')) -
      extract('year' FROM datetime('BBB')))
END;



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

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

iD8DBQE8IoLtvJuQZxSWSsgRAvsDAKDn/IlVBryqsAAsFU2+Dufv0TnwCACfbnKM
cneqtsX4bpJGJ0hGAlGEOnc=
=VQ8u
-----END PGP SIGNATURE-----

Re: How Many Years have Passed?

From
Thomas Lockhart
Date:
> > I have checked the FAQ and other sources and learned that the difference
> > between two dates is the integer number of days.  I want to know how many
> > years are between those two dates.

thomas=# select age(date 'today', date '1980-01-01'),
thomas-# extract(year from age(date 'today', date '1980-01-01'));
           age            | date_part
--------------------------+-----------
 21 years 11 mons 20 days |        21

Is this close to what you want? The nice thing about the age() function
is that it preserves the qualitative units of month and year.

                      - Thomas

Re: How Many Years have Passed?

From
"Ian Harding"
Date:
Yikes!  Who knew there was an age() function?  Thanks!!  Although it calls for timestamp arguments, it works with date.
Are dates usually implicitly cast to timestamp in functions? 

test=# \df age
                            List of functions
  Result  | Function |                     Arguments
----------+----------+----------------------------------------------------
 interval | age      | timestamp with time zone
 interval | age      | timestamp with time zone, timestamp with time zone
(2 rows)



Ian A. Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
(253) 798-3549
mailto: iharding@tpchd.org

>>> lockhart@fourpalms.org 12/20/01 05:34PM >>>
> > I have checked the FAQ and other sources and learned that the difference
> > between two dates is the integer number of days.  I want to know how many
> > years are between those two dates.

thomas=# select age(date 'today', date '1980-01-01'),
thomas-# extract(year from age(date 'today', date '1980-01-01'));
           age            | date_part
--------------------------+-----------
 21 years 11 mons 20 days |        21

Is this close to what you want? The nice thing about the age() function
is that it preserves the qualitative units of month and year.

                      - Thomas

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: How Many Years have Passed?

From
Thomas Lockhart
Date:
> Are dates usually implicitly cast to timestamp in functions?

"Usually"? Probably not. "If necessary"? Yes. Not sure of the ratio of
direct calls to conversions over the total set of functions and
operators, but if the result is not likely to change with a conversion
step then I feel less of a need to implement Yet Another Function to do
the direct call.

Glad the function (documented in the, uh, documentation ;) worked for
you.

                   - Thomas