Thread: extract or date_part on an interval? How many e
Hmm. Seemed so simple. But how do I get the number of years an interval represents? extract is clearly the wrong way: stage=# select 'now()-'1987-02-01' as interval,extract(year from now()-'1987-02-01') as age; interval | age --------------------------+----- 7665 days 18:05:51.660345 | 0 select EXTRACT(year FROM INTERVAL '7665 days'); gives the same result. select EXTRACT(day FROM now()-'1987-02-01')/365 as age; seems messy. select (now()-'1987-02-01')/365; gives extraneous junk. Thanks! -- ---- Visit http://www.obviously.com/
On Sunday 27 January 2008 6:30 pm, Bryce Nesbitt wrote:
> Hmm. Seemed so simple. But how do I get the number of years an
> interval represents? extract is clearly the wrong way:
>
> stage=# select 'now()-'1987-02-01' as interval,extract(year from
> now()-'1987-02-01') as age;
> interval | age
> --------------------------+-----
> 7665 days 18:05:51.660345 | 0
>
> select EXTRACT(year FROM INTERVAL '7665 days'); gives the same result.
>
> select EXTRACT(day FROM now()-'1987-02-01')/365 as age; seems messy.
> select (now()-'1987-02-01')/365; gives extraneous junk.
>
> Thanks!
SELECT age('1987-02-01'::timestamp); age
--------------------------20 years 11 mons 26 days
(1 row)
--
Adrian Klaver
aklaver@comcast.net
Bryce Nesbitt <bryce1@obviously.com> writes:
> Hmm. Seemed so simple. But how do I get the number of years an
> interval represents? extract is clearly the wrong way:
There is nothing simple about datetime calculations, ever :-(
Let me exhibit why this particular case is not as simple as you
could wish:
regression=# select '2007-02-01'::timestamp + interval '1 year'; ?column?
---------------------2008-02-01 00:00:00
(1 row)
regression=# select '2007-02-01'::timestamp + interval '365 days'; ?column?
---------------------2008-02-01 00:00:00
(1 row)
regression=# select '2008-02-01'::timestamp + interval '1 year'; ?column?
---------------------2009-02-01 00:00:00
(1 row)
regression=# select '2008-02-01'::timestamp + interval '365 days'; ?column?
---------------------2009-01-31 00:00:00
(1 row)
That is, there isn't any fixed conversion factor between N days
and N years, so the interval datatype treats them as incommensurate.
If you're willing to settle for an approximate answer, you can
do extract(epoch from interval) and then divide by however many
seconds you want to believe are in a year. This will give various
wrong answers in various corner cases, but I'm not sure there is
a right answer.
regards, tom lane
Sigh. Ok, I settled on:<br /><blockquote><tt>select '1987-01-29'::timestamp + interval '21 years' > now();</tt><br /></blockquote>Which is closer to what I wanted anyway (this was all about determining who was under 21 years old). Thisat least should be robust over leap years.<br /><br /><br /> Tom Lane wrote:<br /><blockquote cite="mid:16843.1201489840@sss.pgh.pa.us"type="cite"><pre wrap="">There is nothing simple about datetime calculations, ever:-( </pre></blockquote><br /><pre class="moz-signature" cols="100">-- ---- Visit <a class="moz-txt-link-freetext" href="http://www.not-so-obviously.com/">http://www.not-so-obviously.com/</a> </pre>
On Jan 27, 2008, at 23:51 , Bryce Nesbitt wrote:
> Sigh. Ok, I settled on:
> select '1987-01-29'::timestamp + interval '21 years' > now();
> Which is closer to what I wanted anyway (this was all about
> determining who was under 21 years old). This at least should be
> robust over leap years.
I think this should work for you for your particular case:
EXTRACT(year FROM AGE(born_on)) >= 21
SELECT born_on, current_date, age(born_on), EXTRACT(year FROM AGE
(born_on)) AS age_in_years, EXTRACT(year FROM age(born_on)) >= 21 as
old_enough_to_drink FROM (SELECT CAST('1987-01-25' AS DATE) + i AS
born_on FROM generate_series(0,5) as the(i)) foo; born_on | date | age | age_in_years |
old_enough_to_drink
------------+------------+--------------------------+--------------
+--------------------- 1987-01-25 | 2008-01-28 | 21 years 3 days | 21 | t 1987-01-26 | 2008-01-28 |
21years 2 days | 21 | t 1987-01-27 | 2008-01-28 | 21 years 1 day | 21 | t
1987-01-28| 2008-01-28 | 21 years | 21 | t 1987-01-29 | 2008-01-28 | 20 years 11 mons 30 days
| 20 | f 1987-01-30 | 2008-01-28 | 20 years 11 mons 29 days | 20 | f
(6 rows)
Michael Glaesemann
grzm seespotcode net