Thread: querying a column w/ timestamp with timezone datatype
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.
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