I got curious and did a few tests on a 8.3.3 database on my laptop.
The 3 different queries all worked, but one took twice as long.
The table alarmlogg has ~930000 rows, query returns ~260000 rows.
Column alarm_tid is timestamp with time zone.
There is an index on alarm_tid.
select * from alarmlogg where alarm_tid between '2007-05-20 00:00:00'
and '2008-05-20 23:59:59';
--> 152 seconds.
select * from alarmlogg where (alarm_tid >= '2007-05-20 00:00:00') and
(alarm_tid <= '2008-05-20 23:59:59');
--> 151 seconds.
SELECT * FROM alarmlogg WHERE alarm_tid >= CAST('2007-05-20' as
timestamp) AND alarm_tid < CAST('2008-05-21' as timestamp);
--> 301 seconds.
I am using the syntax in the second example in my programs. It has
worked since 8.0.
Regards
Jan-Ivar Mellingen
Roberto Garcia skrev:
We changed it because 8.3 doesn't allow the operator LIKE on timestamp
columns. Your syntax works fine but we weren't used to use as u do.
There weren't any specific reason, only another way to do that.
I think when we read that operator LIKE and timestamp values were
incompatible we assumed that timestamp values couldn't be compared to
any char value, opposed as your syntax is.
We've tried to do "select * from X where <timestamp column> =
'2008-05-20 10:'", expecting that the result would be any minute from
10 o'clock (10:15, 10:30, 10:45, etc) of the specific date, but this
syntax retrieves only 10:00 from that date.
Tks for the new syntax.
Regards
Roberto Garcia
Gregory S. Youngblood wrote:
That's a pretty substantial change. Why did you have to make this
change?
Was it causing syntax errors or to get better performance on those
types of
queries? Actually, now that I think about it, didn't:
select * from X where <timestamp column> between '2008-05-20
00:00:00' and
'2008-05-20 23:59:59' work? I could have sworn I have used that
syntax in 8.2 without having to
arbitrarily cast the arguments... now I'm going to have to go look. :)
Yup, confirmed, 8.2.7 and no casting on the date arguments when I was
using
between.
I'm just curious if there was a specific reason (i.e. better
performance,
better use of indexes, etc.) for your syntax.
Thanks,
Greg
-----Original Message-----
From: pgsql-admin-owner@xxxxxxxxxxxxxx
[mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of Roberto Garcia
Sent: Wednesday, June 18, 2008 12:01 PM
Cc: pgsql-admin@xxxxxxxxxxxxxx
Subject: Re: [ADMIN] Major upgrade advice
Just to mention one issue we had here:
In 8.1 we did this to retrieve all data from a specific date:
SELECT * FROM xxx
WHERE <timestamp_column> LIKE '2008-05-20%'
In 8.3 we had to change to:
SELECT * FROM xxx
WHERE <timestamp_column> >= CAST('2008-05-20' as timestamp) AND
<timestamp_column> < CAST('2008-05-21' as timestamp)
Regards
Roberto Garcia
Roberto Garcia
Banco de Dados, MSc
Fone: (12) 3186-8405
