From: | Steve Crawford <scrawford(at)pinpointresearch(dot)com> |
---|---|
To: | Anson Abraham <anson(dot)abraham(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: querying a column w/ timestamp with timezone datatype |
Date: | 2012-01-30 18:09:57 |
Message-ID: | 4F26DCF5.40901@pinpointresearch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | chester c young | 2012-01-31 13:17:32 | pg_dump not correctly saving schema with partitioned tables? |
Previous Message | Anson Abraham | 2012-01-30 15:00:14 | querying a column w/ timestamp with timezone datatype |