Thread: extract day from interval

Hi,

I'm not very savvy with interval manipulations, but I have a query like
this:

---<---------------cut here---------------start-------------->---
SELECT DISTINCT ON (table_a.id) table_a.id, table_a.var2,
       EXTRACT(DAY FROM (table_b.date - table_a.date)) AS age,
       FROM table_a INNER JOIN table_b
       ON (table_a.id = table_b.id)
       WHERE table_a.date IS NOT NULL
       ORDER BY table_a.pupid, age;
---<---------------cut here---------------end---------------->---

Is this an efficient way to get the days from the time interval?  Both
'date' columns are of type "Date".  Thanks.

Cheers,

--
Seb

Seb <spluque@gmail.com> writes:
>        EXTRACT(DAY FROM (table_b.date - table_a.date)) AS age,

> Is this an efficient way to get the days from the time interval?  Both
> 'date' columns are of type "Date".  Thanks.

Efficiency is moot, it's just plain wrong.  Or at least something is
wrong here, Subtraction of two "date" values gives an integer number of
days already, so I'd expect the EXTRACT to fail altogether.  You
sure the date columns are type date and not type timestamp?

            regards, tom lane

On Fri, 28 Mar 2008 00:06:59 -0400,
Tom Lane <tgl@sss.pgh.pa.us> wrote:

[...]

> Efficiency is moot, it's just plain wrong.  Or at least something is
> wrong here, Subtraction of two "date" values gives an integer number
> of days already, so I'd expect the EXTRACT to fail altogether.  You
> sure the date columns are type date and not type timestamp?

My mistake, yes, sure they're both timestamp!


--
Seb