Received: from localhost (unknown [200.46.204.183])
	by developer.postgresql.org (Postfix) with ESMTP id B94B52E0038
	for <pgsql-admin-postgresql.org@developer.postgresql.org>;
	Fri, 20 Jun 2008 10:13:04 -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 28244-05
	for <pgsql-admin-postgresql.org@developer.postgresql.org>;
	Fri, 20 Jun 2008 10:12:50 -0300 (ADT)
X-Greylist: delayed 06:02:26.811696 by SQLgrey-1.7.6
Received: from cpanel12.proisp.no (cpanel12.proisp.no [88.87.44.66])
	by developer.postgresql.org (Postfix) with ESMTP id 1422C2E0030
	for <pgsql-admin@postgresql.org>; Fri, 20 Jun 2008 10:12:50 -0300 (ADT)
Received: from ti112220a340-0929.bb.online.no ([85.166.107.161]:25492
	helo=[127.0.0.1]) by cpanel12.proisp.no with esmtpa (Exim 4.68)
	(envelope-from <jan-ivar.mellingen@alreg.no>)
	id 1K9alD-0006mq-Cj; Fri, 20 Jun 2008 09:10:15 +0200
Message-ID: <485B57D6.4040702@alreg.no>
Date: Fri, 20 Jun 2008 09:10:14 +0200
From: Jan-Ivar Mellingen <jan-ivar.mellingen@alreg.no>
User-Agent: Thunderbird 2.0.0.14 (Windows/20080421)
MIME-Version: 1.0
To: Roberto Garcia <roberto.garcia@cptec.inpe.br>
CC: 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>
	<485AABC9.3020807@cptec.inpe.br>
In-Reply-To: <485AABC9.3020807@cptec.inpe.br>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 080619-0, 19.06.2008), Outbound message
X-Antivirus-Status: Clean
X-AntiAbuse: This header was added to track abuse,
	please include it with any abuse report
X-AntiAbuse: Primary Hostname - cpanel12.proisp.no
X-AntiAbuse: Original Domain - postgresql.org
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - alreg.no
X-Source: 
X-Source-Args: 
X-Source-Dir: 
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200806/176
X-Sequence-Number: 29514

I got curious and did a few tests on a 8.3.3 database on my laptop.
The 3 different queries all worked, but one took twice as long.

The table alarmlogg has ~930000 rows, query returns ~260000 rows.
Column alarm_tid is timestamp with time zone.
There is an index on alarm_tid.

select * from alarmlogg where alarm_tid between '2007-05-20 00:00:00' 
and '2008-05-20 23:59:59';
--> 152 seconds.

select * from alarmlogg where (alarm_tid >= '2007-05-20 00:00:00') and 
(alarm_tid <= '2008-05-20 23:59:59');
--> 151 seconds.

SELECT * FROM alarmlogg WHERE alarm_tid >= CAST('2007-05-20' as 
timestamp) AND alarm_tid < CAST('2008-05-21' as timestamp);
--> 301 seconds.

I am using the syntax in the second example in my programs. It has 
worked since 8.0.

Regards
Jan-Ivar Mellingen


Roberto Garcia skrev:
> 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