pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedquerying a column w/ timestamp with timezone datatype
2+ messages / 2 participants
[nested] [flat]
* querying a column w/ timestamp with timezone datatype
@ 2012-01-30 15:00 Anson Abraham <anson.abraham@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Anson Abraham @ 2012-01-30 15:00 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: querying a column w/ timestamp with timezone datatype
@ 2012-01-30 18:09 Steve Crawford <scrawford@pinpointresearch.com>
parent: Anson Abraham <anson.abraham@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Steve Crawford @ 2012-01-30 18:09 UTC (permalink / raw)
To: Anson Abraham <anson.abraham@gmail.com>; +Cc: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2012-01-30 18:09 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-01-30 15:00 querying a column w/ timestamp with timezone datatype Anson Abraham <anson.abraham@gmail.com>
2012-01-30 18:09 ` Steve Crawford <scrawford@pinpointresearch.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox