Division of intervals.
Attempting rather unsuccessfully to convert something of the form
45 years 9 mons 5 days 20:28:18.886891
to a total number of months, I attempted
SELECT age(now(), timestamp '1957-06-13') / interval '1 month';
and got the error
ERROR: Unable to identify an operator '/' for types 'interval' and 'interval'
You will have to retype this query using an explicit cast
Does anyone know what interval format intervals are stored as? Is there a way
I can cast this expression to be able to find the number of months within
this interval?
Or is there a function I don't know about capable of doing this? (extracting
the total number of a specific interval from another interval)... e.g. total
number of months, total number of days...
Josh.
Joshua Moore-Oliva <josh@chatgris.com> writes:
I attempted
SELECT age(now(), timestamp '1957-06-13') / interval '1 month';
and got the error
ERROR: Unable to identify an operator '/' for types 'interval' and 'interval'
I'm not sure division of intervals is sensible --- consider the
recently-pointed-out issues about variable length of months, etc.
Does this do what you want?
regression=# SELECT extract(year from age(now(), timestamp '1957-06-13'));
date_part
-----------
45
(1 row)
regression=# SELECT extract(month from age(now(), timestamp '1957-06-13'));
date_part
-----------
9
(1 row)
regression=# SELECT extract(year from age(now(), timestamp '1957-06-13')) *12
regression-# + extract(month from age(now(), timestamp '1957-06-13'));
?column?
----------
549
(1 row)
regards, tom lane
I guess that it does for this problem.. But I have other applications that
require the number of days... and that gets a lot more complex to work out.
Are there any plans in the near future to add this functionality?
I had already through of this solution, but it appeared rather bulky to me,
but I guess that's all there is now thanks.
Josh.
Show quoted text
On March 19, 2003 12:23 am, Tom Lane wrote:
Joshua Moore-Oliva <josh@chatgris.com> writes:
I attempted
SELECT age(now(), timestamp '1957-06-13') / interval '1 month';
and got the error
ERROR: Unable to identify an operator '/' for types 'interval' and
'interval'I'm not sure division of intervals is sensible --- consider the
recently-pointed-out issues about variable length of months, etc.Does this do what you want?
regression=# SELECT extract(year from age(now(), timestamp '1957-06-13'));
date_part
-----------
45
(1 row)regression=# SELECT extract(month from age(now(), timestamp '1957-06-13'));
date_part
-----------
9
(1 row)regression=# SELECT extract(year from age(now(), timestamp '1957-06-13'))
*12 regression-# + extract(month from age(now(), timestamp '1957-06-13'));
?column?
----------
549
(1 row)regards, tom lane
Thanks, that should work.
Hmm that gives me an idea... Maybe I'll try to make a date_ceil function
identical to date_part and submit it.
All this stuff currently just seems so messy!
Josh.
Show quoted text
On March 19, 2003 06:13 am, Bruno Wolff III wrote:
On Wed, Mar 19, 2003 at 01:03:50 -0500,
Joshua Moore-Oliva <josh@chatgris.com> wrote:
I guess that it does for this problem.. But I have other applications
that require the number of days... and that gets a lot more complex to
work out.If you subtract the timestamps the interval will have 0 for the month/year
part and you can extract the epoch from it to get the time in seconds.
This can be divided by 24*60*60 to get days.---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
Import Notes
Reply to msg id not found: 20030319111314.GA702@wolff.to
On Wed, Mar 19, 2003 at 01:03:50 -0500,
Joshua Moore-Oliva <josh@chatgris.com> wrote:
I guess that it does for this problem.. But I have other applications that
require the number of days... and that gets a lot more complex to work out.
If you subtract the timestamps the interval will have 0 for the month/year
part and you can extract the epoch from it to get the time in seconds.
This can be divided by 24*60*60 to get days.
On Wed, Mar 19, 2003 at 06:02:25AM -0500, Joshua Moore-Oliva wrote:
Thanks, that should work.
Hmm that gives me an idea... Maybe I'll try to make a date_ceil function
identical to date_part and submit it.All this stuff currently just seems so messy!
I agree. It looks somewhat incomplete and cumbersome.
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Hay quien adquiere la mala costumbre de ser infeliz" (M. A. Evans)