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 > > -- Achilleas Mantzios
