Re: Assigning a timestamp without timezone to a timestamp

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(at)crankycanuck(dot)ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun