Timezone handling

[Julien Danjou]

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 --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 835 bytes
Desc: not available
URL: <https://mailman.common-lisp.net/pipermail/postmodern-devel/attachments/20130724/da250702/attachment.sig>