Timezone handling

[Julien Danjou]

On Wed, Jul 24 2013, Sabra Crolleton wrote:

Hi Sabra,

> 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

No, I've stumbled upon this page too yesterday actually and learnt it
stores eveything in UTC.

> 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)

Agreed. The problem in this case is that you don't know the timezone of
the serverm and I want to insert an UTC timestamp.

> 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)

Yeah, because `encode-timestamp' returned 2013-01-01 11:00:00, and
what's got inserted.

> 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?

Not really unfortunatelly, unless I've missed the obvious.

I've still have no clue on how to insert "2013-01-01 12:00:00 UTC" into
PG. Your first example inserted "2013-01-01 04:00:00 UTC", and the
second example inserted "2013-01-01 03:00:00 UTC".

Thanks!

-- 
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/559c930e/attachment.sig>