Received: from localhost (unknown [200.46.204.183])
	by developer.postgresql.org (Postfix) with ESMTP id 208A72E0037
	for <pgsql-admin-postgresql.org@developer.postgresql.org>;
	Fri, 20 Jun 2008 10:32:18 -0300 (ADT)
Received: from developer.postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 48304-07
	for <pgsql-admin-postgresql.org@developer.postgresql.org>;
	Fri, 20 Jun 2008 10:32:04 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from tatuape.cptec.inpe.br (tatuape.cptec.inpe.br [150.163.141.147])
	by developer.postgresql.org (Postfix) with ESMTP id 2D8422E0030
	for <pgsql-admin@postgresql.org>; Fri, 20 Jun 2008 10:32:08 -0300 (ADT)
Received: from [150.163.140.113] (nhatimana.cptec.inpe.br [150.163.140.113])
	by tatuape.cptec.inpe.br (Postfix) with ESMTP id 5CB75706A5;
	Fri, 20 Jun 2008 10:32:03 -0300 (BRT)
Message-ID: <485BAFFB.80300@cptec.inpe.br>
Date: Fri, 20 Jun 2008 10:26:19 -0300
From: Roberto Garcia <roberto.garcia@cptec.inpe.br>
User-Agent: Thunderbird 2.0.0.14 (X11/20080421)
MIME-Version: 1.0
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>
CC: pgsql-admin@postgresql.org
Subject: Re: Major upgrade advice
References: <48594429.7040001@pinpointresearch.com>
	<!&!AAAAAAAAAAAYAAAAAAAAAN6d+lQFliBLjc3jO6z0BSQigQAAEAAAANXsptp1/mNJkA+ZsX3eKygBAAAAAA==@tcscs.com>
	<485AABC9.3020807@cptec.inpe.br>
	<200806201005.09702.achill@matrix.gatewaynet.com>
In-Reply-To: <200806201005.09702.achill@matrix.gatewaynet.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.395 tagged_above=0 required=5 tests=AWL=0.395
X-Spam-Level: 
X-Archive-Number: 200806/184
X-Sequence-Number: 29522

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@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 
>>
>>
>>     
>
>
>
>   


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