Thread: TIMESTAMP SUBTRACTION
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
On 21 May 2003 at 16:27, Madhavi Daroor wrote:
> 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
Try
WHERE
(TIMESTAMP('05-21-2003 00:00:00','mm-dd-yyyy') - TO_TIMESTAMP('04-30-2003
00:00:00','mm-dd-yyyy'))::integer > 30
or something like that...
> Please reply SOON !!!
And why is that?
I am sure you would agree that it is not polite when you are asking for help.
Bye
Shridhar
--
lp1 on fire(One of the more obfuscated kernel messages)
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)
"Madhavi Daroor" <madhavi@zoniac.com> writes:
> 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-200300:00:00','mm-dd-yyyy') > 30
And what is the "30" supposed to mean? Seconds, days, fortnights, ... ?
If it's a constant you'd be better off to write it as an interval
constant:
... > interval '30 days'
(or whatever unit you have in mind). If it's not constant you might
try this way:
... > 30 * interval '1 day'
since there is a float-times-interval-yielding-interval operator.
regards, tom lane
On Wed, May 21, 2003 at 04:27:32PM +0530, 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.
One thing you can try is to convert the timestamps to dates, and
substract that. It'll return a numerical value.
regression=# select to_date('2003-04-99', 'yyyy-mm-dd') -
to_date('2002-13-85', 'yyyy-mm-dd') > 100;
?column?
----------
t
(1 row)
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"I think my standards have lowered enough that now I think 'good design'
is when the page doesn't irritate the living fuck out of me." (JWZ)
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
>
>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> 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?
Check out the EXTRACT function, which can give you the number of days:
CREATE TABLE timetest (
mike TIMESTAMP,
ike TIMESTAMP
);
INSERT INTO timetest (mike, ike) VALUES (now(), now()-1);
INSERT INTO timetest (mike, ike) VALUES (now(), now()-2);
INSERT INTO timetest (mike, ike) VALUES (now(), now()-3);
INSERT INTO timetest (mike, ike) VALUES (now(), now()-4);
SELECT * FROM timetest WHERE EXTRACT(day FROM mike - ike) > 2;
If you don't want to limit yourself to days, you can also convert everything
to seconds, which has the nice effect that any interval such as minute,
hour, and day can be expressed as a number of seconds.
Note that "epoch" does not return an integer, so we throw in a ROUND to
neaten things up.
* Number of seconds between the two:
SELECT ROUND(EXTRACT(epoch FROM mike-ike)) FROM timetest;
round
- --------
118727
205127
291527
377927
* Number of minutes between the two:
SELECT ROUND(EXTRACT(epoch FROM mike-ike)/60) FROM timetest;
round
- -------
1979
3419
4859
6299
* Number of hours between the two:
SELECT ROUND(EXTRACT(epoch FROM mike-ike)/(60*60)) FROM timetest;
round
- -------
33
57
81
105
* Number of days between the two, expressed both ways:
SELECT ROUND(EXTRACT(epoch FROM mike-ike)/(60*60*24)) AS uno,
EXTRACT(days FROM mike-ike) AS dos FROM timetest;
uno | dos
- -----+-----
1 | 1
2 | 2
3 | 3
4 | 4
Note that "days" is the only one where they will be equivalent: do not
try the following:
SELECT EXTRACT(hours FROM mike-ike) FROM timetest;
as it will not give you the total hours, but only the difference in hours as if
both times were on the same day. "Days" is safe to use as an absolute difference
because intervals are not measured in units higher than a day.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200305210851
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html
iD8DBQE+y30IvJuQZxSWSsgRAlbvAKCBlDCKY7UnlZXi7L/JDyeVMI35QwCfdXs6
yjjqi0wk6cGRaW/ub0aQyCQ=
=W5ny
-----END PGP SIGNATURE-----