From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Assigning a timestamp without timezone to a timestamp
Date: Thu, 5 Oct 2006 14:31:28 -0400
Message-ID: <20061005183128.GD8826@phlogiston.dyndns.org> (raw)
In-Reply-To: <8C5B026B51B6854CBE88121DBF097A864DE848@ehost010-33.exch010.intermedia.net>
References: <20061003145134.GA3399@phlogiston.dyndns.org>
<8C5B026B51B6854CBE88121DBF097A864DE848@ehost010-33.exch010.intermedia.net>
On Wed, Oct 04, 2006 at 11:04:56AM -0700, Hector Villarreal wrote:
> Hi
> I am also interested in this type of setup. However, in the example
> below
> I am a little confused as to why the table entry is 1, -3
The 1 is an artificial key (it's the criterion in the WHERE clause).
The -03 is the time zone offset. The most reliable way to handle
time zone offsets, I find, is to use the numeric offset from UTC.
That's the way PostgreSQL shows them in some cases, too. On my
system, for instance, I get this for SELECT now() (at the moment):
now
-------------------------------
2006-10-05 14:21:51.507419-04
(1 row)
> SELECT a.timestamp::timestamptz from (SELECT '2006-10-03
> 09:00'||"timezone" as timestamp from storetz where id = 1) as a;
> timestamp
So what this does is
SELECT
the column named "timestamp" from relation "a"
cast to timestamp with time zone (the :: is a shorthand for
cast in Postgres)
FROM
a relation called "a"
constituted as (this is that "as a" on the end)
SELECT
the literal string '2006-10-03 09:00'
concatenated to (that's what "||" means)
the column "timezone"
[and call that whole thing "timestamp"
FROM
a relation called "storetz"
WHERE
the storetz row has an id of 1.
So, what you get is a timestamp with a time zone that is built up
from the combination of a timestamp without time zone and some time
zone data that you have.
What's _really_ cool in Postgres about the time handling is that you
can also change your time zone, and find that the data nicely
represents your new time zone too. You can see this in my original
example: I was using GMT, but inserted a timestamp in -03. When I
selected the answer, though, I got one back in GMT (==UTC). So
that's why you see this:
>
> testing=# SELECT a.timestamp::timestamptz from (SELECT '2006-10-03
> 09:00'||"timezone" as timestamp from storetz where id = 1) as a;
> timestamp
> ------------------------
> 2006-10-03 12:00:00+00
> (1 row)
2006-10-03 09:00:00-03 == 2006-10-03 12:00:00+00
Hope that helps,
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun
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: ajs@crankycanuck.ca
Subject: Re: Assigning a timestamp without timezone to a timestamp
In-Reply-To: <20061005183128.GD8826@phlogiston.dyndns.org>
* 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