Thread: type casting a subselect as an interval

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'

Thanks!

Jc

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)