Received: from localhost (unknown [200.46.204.183])
	by developer.postgresql.org (Postfix) with ESMTP id 0B78C2E0045
	for <pgsql-admin-postgresql.org@developer.postgresql.org>;
	Thu, 19 Jun 2008 16:02:09 -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 19965-02
	for <pgsql-admin-postgresql.org@developer.postgresql.org>;
	Thu, 19 Jun 2008 16:02:03 -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 71EFA2E0054
	for <pgsql-admin@postgresql.org>; Thu, 19 Jun 2008 16:02:02 -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 51EE3706B8
	for <pgsql-admin@postgresql.org>; Thu, 19 Jun 2008 16:01:51 -0300 (BRT)
Message-ID: <485AABC9.3020807@cptec.inpe.br>
Date: Thu, 19 Jun 2008 15:56:09 -0300
From: Roberto Garcia <roberto.garcia@cptec.inpe.br>
User-Agent: Thunderbird 2.0.0.14 (X11/20080421)
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Re: Major upgrade advice
References: <48594429.7040001@pinpointresearch.com>
	<48595B63.4080000@cptec.inpe.br>
	<!&!AAAAAAAAAAAYAAAAAAAAAN6d+lQFliBLjc3jO6z0BSQigQAAEAAAANXsptp1/mNJkA+ZsX3eKygBAAAAAA==@tcscs.com>
In-Reply-To: 
 <!&!AAAAAAAAAAAYAAAAAAAAAN6d+lQFliBLjc3jO6z0BSQigQAAEAAAANXsptp1/mNJkA+ZsX3eKygBAAAAAA==@tcscs.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.79 tagged_above=0 required=5 tests=AWL=0.790
X-Spam-Level: 
X-Archive-Number: 200806/164
X-Sequence-Number: 29502

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