From: Steve Crawford <scrawford@pinpointresearch.com>
To: Anson Abraham <anson.abraham@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: querying a column w/ timestamp with timezone datatype
Date: Mon, 30 Jan 2012 10:09:57 -0800
Message-ID: <4F26DCF5.40901@pinpointresearch.com> (raw)
In-Reply-To: <CAC9g-He80+W8fjMBZcN_8Gn5VM1Pb7_5BdjR2COdJqC3dEWH4w@mail.gmail.com>
References: <CAC9g-He80+W8fjMBZcN_8Gn5VM1Pb7_5BdjR2COdJqC3dEWH4w@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: scrawford@pinpointresearch.com, anson.abraham@gmail.com
Subject: Re: querying a column w/ timestamp with timezone datatype
In-Reply-To: <4F26DCF5.40901@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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