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@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Roberto Garcia
> Sent: Wednesday, June 18, 2008 12:01 PM
> Cc: pgsql-admin@postgresql.org
> 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