From: Steve Crawford <scrawford@pinpointresearch.com>
To: LaraK <indarija@gmx.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: convert in GMT time zone without summer time
Date: Tue, 03 May 2011 08:54:29 -0700
Message-ID: <4DC02535.3070106@pinpointresearch.com> (raw)
In-Reply-To: <1304406921088-4366565.post@n5.nabble.com>
References: <1302848430605-4304830.post@n5.nabble.com>
<ioc0gr$53p$1@reversiblemaps.ath.cx>
<4DAC6D4D.30405@pinpointresearch.com>
<1304406921088-4366565.post@n5.nabble.com>
On 05/03/2011 12:15 AM, LaraK wrote:
> Very good!
>
> Another question:
> I want to convert the string to the type TIMESTAMP WITH TIME ZONE. What do I
> have to format? 'TZ' does not.
>
> select to_timestamp('2011-03-22 14:17:00 Europe/Berlin', 'YYYY-MM-DD
> HH:MI:SS TZ')
>
Just cast it to a timestamp with time zone:
select timestamptz '2011-04-22 19:17:00 Europe/Berlin';
Remember...
The value of a timestamp with time zone is always stored internally as UTC.
When a timestamp with time zone is displayed, the time zone is based on
the client's default, the "set timezone to" statement or the "at time
zone" clause in the query.
In the case of an explicit "at time zone" clause, the result becomes a
timestamp without time zone data type (that is why the previous static
example with the "at time zone" clause was a timestamp without time zone).
A timestamp with time zone is useful to identify a specific point in
time. "Bin Laden's death was announced at...", "shuttle Endeavor
launched at...", "Amazon EC2 crashed at...". Most timestamp data I
encounter is of this type.
A timestamp without time zone might be useful for data like "Breakfast
is served at 7am". Presumably a hotel chain would serve at 7am in each
hotel and not have all hotels serve at 7am corporate headquarters time.
It takes a bit of time to wrap your head around time and time zones but
it would be well worth your time to carefully read
http://www.postgresql.org/docs/8.4/interactive/datatype-datetime.html
(IIRC, you are using 8.4) a couple times.
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, indarija@gmx.net
Subject: Re: convert in GMT time zone without summer time
In-Reply-To: <4DC02535.3070106@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