Re: TIMESTAMP SUBTRACTION

Note that this will only work for days and since the 30 you are using
could be anything i would consider rewriting the query but if you are
sure it is just days you want then the following will work.

SELECT EXTRACT(days FROM (timestampz1 - timestampz2));

This will give you the day integer although in theory the 30 you have
could be anything not just days.
Darren
Joseph Healy wrote:

>Hi,
>
>You could use something like this, although it might depend on how many
>days you were expecting:
>
>
>select substring((TO_TIMESTAMP('05-21-2003 00:40:00','mm-dd-yyyy')
>-TO_TIMESTAMP('04-30-2003 00:00:00','mm-dd-yyyy'))::text from 0 for
>3)::int as test;
>
>Hope this helps,
>
>Joe Healy
>
>On Wed, 2003-05-21 at 20:57, Madhavi Daroor wrote:
>
>
>>Hi All,
>>     When I subtract 2 timestamp variables in postgres 2.3.1, I get an
>>interval value.
>>Eg:
>>SELECT TO_TIMESTAMP('05-21-2003 00:00:00','mm-dd-yyyy') -
>>TO_TIMESTAMP('04-30-2003 00:00:00','mm-dd-yyyy');
>>Result:
>>    21 days --------- This is an interval
>>
>>But what I need is a numeric value. Ie; 21 and NOT 21 days.
>>
>>I need to compare this difference with a numeric value in my WHERE clause
>>like this
>>
>>WHERE
>>    TIMESTAMP('05-21-2003 00:00:00','mm-dd-yyyy') - TO_TIMESTAMP('04-30-2003
>>00:00:00','mm-dd-yyyy') > 30
>>
>>But if I do this....I get wrong results. How do I do such a comparison? Or
>>How to I get a Numeric value after the subtraction?
>>
>>
>>Please reply SOON !!!
>>
>>Thanx,
>>Madhavi Daroor
>>
>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 2: you can get off all lists at once with the unregister command
>>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>
>>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>