Re: Assigning a timestamp without timezone to a timestamp

Hi Andrew,

If only all time zones were fixed offset timezones life would be so much
simpler.

Unfortunately the main area of deployment of my app will beToronto which is
on EDT which is not a fixed offsets timezone.  I hope/assume your solution
works with "EDT" instead of "-3", I will test it soon.


Andrew Sullivan wrote:
> 
> 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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
> 
> 

-- 
View this message in context:
http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6667446
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.