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!