Στις Friday 20 June 2008 16:26:19 ο/η Roberto Garcia έγραψε: > 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. Then create an additional index like CREATE INDEX tablename_tscol_date on tablename (date(tscol)); where tscol is timestamp > > 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. -- Achilleas Mantzios
