pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedextract or date_part on an interval? How many e
5+ messages / 4 participants
[nested] [flat]
* extract or date_part on an interval? How many e
@ 2008-01-28 02:30 Bryce Nesbitt <bryce1@obviously.com>
2008-01-28 02:56 ` Re: extract or date_part on an interval? How many e Adrian Klaver <aklaver@comcast.net>
2008-01-28 03:10 ` Re: extract or date_part on an interval? How many e Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Bryce Nesbitt @ 2008-01-28 02:30 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: extract or date_part on an interval? How many e
2008-01-28 02:30 extract or date_part on an interval? How many e Bryce Nesbitt <bryce1@obviously.com>
@ 2008-01-28 02:56 ` Adrian Klaver <aklaver@comcast.net>
1 sibling, 0 replies; 5+ messages in thread
From: Adrian Klaver @ 2008-01-28 02:56 UTC (permalink / raw)
To: pgsql-sql; +Cc: Bryce Nesbitt <bryce1@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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: extract or date_part on an interval? How many e
2008-01-28 02:30 extract or date_part on an interval? How many e Bryce Nesbitt <bryce1@obviously.com>
@ 2008-01-28 03:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-01-28 04:51 ` Re: extract or date_part on an interval? How many e Bryce Nesbitt <bryce1@obviously.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2008-01-28 03:10 UTC (permalink / raw)
To: Bryce Nesbitt <bryce1@obviously.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: extract or date_part on an interval? How many e
2008-01-28 02:30 extract or date_part on an interval? How many e Bryce Nesbitt <bryce1@obviously.com>
2008-01-28 03:10 ` Re: extract or date_part on an interval? How many e Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-01-28 04:51 ` Bryce Nesbitt <bryce1@obviously.com>
2008-01-28 05:19 ` Re: extract or date_part on an interval? How many e Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 1 reply; 5+ messages in thread
From: Bryce Nesbitt @ 2008-01-28 04:51 UTC (permalink / raw)
To: pgsql-sql
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
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). This at 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>
</body>
</html>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: extract or date_part on an interval? How many e
2008-01-28 02:30 extract or date_part on an interval? How many e Bryce Nesbitt <bryce1@obviously.com>
2008-01-28 03:10 ` Re: extract or date_part on an interval? How many e Tom Lane <tgl@sss.pgh.pa.us>
2008-01-28 04:51 ` Re: extract or date_part on an interval? How many e Bryce Nesbitt <bryce1@obviously.com>
@ 2008-01-28 05:19 ` Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 0 replies; 5+ messages in thread
From: Michael Glaesemann @ 2008-01-28 05:19 UTC (permalink / raw)
To: Bryce Nesbitt <bryce1@obviously.com>; +Cc: pgsql-sql
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 | 21 years 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-01-28 05:19 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-28 02:30 extract or date_part on an interval? How many e Bryce Nesbitt <bryce1@obviously.com>
2008-01-28 02:56 ` Adrian Klaver <aklaver@comcast.net>
2008-01-28 03:10 ` Re: extract or date_part on an interval? How many e Tom Lane <tgl@sss.pgh.pa.us>
2008-01-28 04:51 ` Re: extract or date_part on an interval? How many e Bryce Nesbitt <bryce1@obviously.com>
2008-01-28 05:19 ` Re: extract or date_part on an interval? How many e Michael Glaesemann <grzm@seespotcode.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox