We have an index on the time_stamp column, if the format of argument is
different from the format the index was created it is not used, then
performance is decreased because a sequential scan is done instead of an
index scan.
Achilleas Mantzios wrote:
Why not simply,
SELECT * FROM xxx WHERE <timestamp_column>::date = '2008-05-20'::date;
Στις Thursday 19 June 2008 21:56:09 ο/η Roberto Garcia έγραψε:
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
--
A luta contra o aquecimento global depende de cada um de nós, faça sua parte, economize recursos naturais.
--
http://www.cptec.inpe.br
http://www.inpe.br
Roberto Garcia
Banco de Dados, MSc
Fone: (12) 3186-8405
--
A luta contra o aquecimento global depende de cada um de nós, faça sua parte, economize recursos naturais.
--
http://www.cptec.inpe.br
http://www.inpe.br
