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 */
signature.asc
Description: PGP signature
