Timezone handling

[Sabra Crolleton]

Julien,

Are you thinking that PG will allow you to have different timezones in your
timestamp? If my understanding of PG is correct, it keeps everything in a
single timezone - UTC. Then everything else is set using the offset. See,
e.g. http://www.postgresql.org/docs/9.1/static/datatype-datetime.html

"For timestamp with time zone, the internally stored value is always in UTC
(Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT).
An input value that has an explicit time zone specified is converted to UTC
using the appropriate offset for that time zone. If no time zone is stated
in the input string, then it is assumed to be in the time zone indicated by
the system's timezone<http://www.postgresql.org/docs/9.1/static/runtime-config-client.html#GUC-TIMEZONE>parameter,
and is converted to UTC using the offset for the
timezone zone.
When a timestamp with time zone value is output, it is always converted
from UTC to the current timezone zone, and displayed as local time in that
zone. To see the time in another time zone, either change timezone or use
the AT TIME ZONE construct."

So, looking at a server that is set for PDT, for table test with fields
name, text and updated_at

Default with no timezone or offset:
(query (:insert-into 'test :set 'name "george" 'text "insert here"
'updated-at
                     (local-time:encode-timestamp 0 0 0 12 01 01 2013)))

2013-01-01 12:00:00-08 (looking at the default timezone for the server, PG
has set the timezone to UTC less 8 hours - UTC time would be 04:00:00)

Using offset to explicitly offset 1 hour from UTC (e.g. Paris)
(query (:insert-into 'test :set 'name "ringo" 'text "offset 1 hour"
'updated-at
                     (local-time:encode-timestamp 0 0 0 12 01 01 2013
:offset 3600)))

2013-01-01 03:00:00-08 (looking at the default timezone for the server, PG
has kept the timezone as PDT - UTC less 8 hours - but set the time as
03:00:00, which is 1 hour ahead of UTC)

Using timezone to explicitly set it for UTC
(query (:insert-into 'test
                     :set 'name "paul" 'text "insert here using timezone
utc"
                     'updated-at
                     (local-time:encode-timestamp 0 0 0 12 01 01 2013
                                                  :timezone
local-time::+utc-zone+)))

2013-01-01 04:00:00-08 (looking at the default timezone for the server, PG
has kept the timezone as PDT - UTC less 8 hours - but set the time as
04:00:00, which is the time in UTC relative to the PDT time at the server.

Does this help?

Sabra


On Wed, Jul 24, 2013 at 12:14 AM, Julien Danjou <julien at danjou.info> wrote:

> On Wed, Jul 24 2013, Sabra Crolleton wrote:
>
> > I use the local-time package with encode-timestamp to create the
> timestamp
> > and just put that into the database.
> >
> > Function: local-time:encode-timestamp nsec sec minute hour day month year
> > &key timezone offset into
> >
> > Returns a new timestamp instance corresponding to the specified time
> > elements. The offset is the number of seconds offset from UTC of the
> > locale. If offset is not specified, the offset will be guessed from the
> > timezone. If a timestamp is passed as the into argument, its value will
> be
> > set and that timestamp will be returned. Otherwise, a new timestamp is
> > created.
>
> The problem is that it returns a timestamp without any timezone
> information, it just convert the time given into an UTC representation
> based on the timezone arguments.
> Or when INSERTed, if your Postgresql session runs with a default
> timezone different than UTC (which is usually the case), PG will insert
> it considering the timezine is in the locale timestamp, not UTC.
>
> When your PG server is in GMT+2 and has such a default session, if:
> a. you INSERT (encode-timestamp 0 0 0 12 01 01 2013) it will insert
>    2013-01-01 12:00:00+02
>    (which is correct because the CL timezone and the PG timezone matches)
>
> b. you INSERT (encode-timestamp 0 0 0 12 01 01 2013 :timezone UTC) it will
> insert
>    2013-01-01 10:00:00+02
>    (which is wrong since the timestamp is in UTC)
>
> And here I'm on case b.
>
> --
> Julien Danjou
> # Free Software hacker # freelance consultant
> # http://julien.danjou.info
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mailman.common-lisp.net/pipermail/postmodern-devel/attachments/20130724/c48d488c/attachment.html>