Message-ID: <4F26DCF5.40901@pinpointresearch.com>
Date: Mon, 30 Jan 2012 10:09:57 -0800
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.24) Gecko/20111109 Lightning/1.0b2 Thunderbird/3.1.16
MIME-Version: 1.0
To: Anson Abraham <anson.abraham@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: querying a column w/ timestamp with timezone datatype
References: 
 <CAC9g-He80+W8fjMBZcN_8Gn5VM1Pb7_5BdjR2COdJqC3dEWH4w@mail.gmail.com>
In-Reply-To: 
 <CAC9g-He80+W8fjMBZcN_8Gn5VM1Pb7_5BdjR2COdJqC3dEWH4w@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 01/30/2012 07:00 AM, Anson Abraham wrote:
> I an 9.1 PG database: I have a column which is a timestamp w/ time 
> zone.  So the value I have as one record in table is: 15:55:24.342848+00
>
> If i want to find records less or greater than that timestamp, how do 
> I construct the query?
>
> select * from schema.table where tscol >= '15:55:24.342848+00';
> select * from schema.table where tscol >= '15:55:24.342848+00'::timestamp;
> select * from schema.table where tscol >= cast('15:55:24.342848+00' as 
> timestamp with time zone);
>
> do not work.  Do I have to convert the value to a string and substr to 
> 15:55:24 and then convert back to a timestamp? It's been a long while 
> since I had to query a pg table w/ a timestamp with time zone value. 
>  Any help here would be appreciated.

Those aren't timestamps - timestamps include the date part.

Perhaps you are thinking about a *time* with time zone (a type that 
exists due to SQL requirements but which is a somewhat nonsensical type, 
the use of which is not recommended):
http://www.postgresql.org/docs/9.1/static/datatype-datetime.html#DATATYPE-TIMEZONES

Cheers,
Steve