type casting a subselect as an interval

On Fri, Feb 21, 2003 at 07:59:19PM +0900, Jean-Christian Imbeault wrote:

How can I cast the return value of a sub-select as an interval?

Something like:

select current_timestamp - interval '(select days from T) days'

You can cast it in the subselect:

select current_timestamp - (select (days::text || 'd')::interval from T);

Karel
--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

Karel Zak <zakkr@zf.jcu.cz> writes:

On Fri, Feb 21, 2003 at 07:59:19PM +0900, Jean-Christian Imbeault wrote:

How can I cast the return value of a sub-select as an interval?
select current_timestamp - interval '(select days from T) days'

You can cast it in the subselect:

select current_timestamp - (select (days::text || 'd')::interval from T);

A better way of converting numeric values to intervals is to rely on the
interval-times-float8 operator:

select current_timestamp - (select days from T) * interval '1 day';

This is readable, efficient, and easily adapted to other units (second,
month, etc).

regards, tom lane

On Fri, 21 Feb 2003, Tom Lane wrote:

Karel Zak <zakkr@zf.jcu.cz> writes:

On Fri, Feb 21, 2003 at 07:59:19PM +0900, Jean-Christian Imbeault wrote:

How can I cast the return value of a sub-select as an interval?
select current_timestamp - interval '(select days from T) days'

You can cast it in the subselect:

select current_timestamp - (select (days::text || 'd')::interval from T);

A better way of converting numeric values to intervals is to rely on the
interval-times-float8 operator:

select current_timestamp - (select days from T) * interval '1 day';

This is readable, efficient, and easily adapted to other units (second,
month, etc)

Since this comes up so often would it make sense to add it to the FAQ
(possibly along with a short description of what interval 'blah' means
since people seem to keep wanting to use it as a cast)