Hi there,
I'm struggling with timezone handling in Postmodern. I'm using timestamp with time zone column types, and I'm looking for the correct way to feed postmodern such information on insert. Basically, I don't see any class that would fit. I've tried to grep through the code but didn't find much about timestamp/timezone handling.
Concretely, I'm having UTC timestamp that I want to insert into those timestamp-with-time-zone columns, but I don't know how to tell Postmodern that it should postfix them with something like +00 so PG understand it should not default them to its default timezone.
On the same topic, I thought that one easy solution would be to set the session timezone to UTC. It's easy enough to come with a macro doing a SET TIMEZONE='UTC' before each request, but honestly, I'd like to avoid that and do it only at connection time. Now, I've read through cl-postgres code, and I was hoping being able to plug my code at connection time, but it seems everything is defined as function and not as method, which seems weird to me, and not really pluggable in the end.
Any hint appreciated!
Cheers,
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.
Hope that helps.
Sabra
On Tue, Jul 23, 2013 at 3:39 PM, Julien Danjou julien@danjou.info wrote:
Hi there,
I'm struggling with timezone handling in Postmodern. I'm using timestamp with time zone column types, and I'm looking for the correct way to feed postmodern such information on insert. Basically, I don't see any class that would fit. I've tried to grep through the code but didn't find much about timestamp/timezone handling.
Concretely, I'm having UTC timestamp that I want to insert into those timestamp-with-time-zone columns, but I don't know how to tell Postmodern that it should postfix them with something like +00 so PG understand it should not default them to its default timezone.
On the same topic, I thought that one easy solution would be to set the session timezone to UTC. It's easy enough to come with a macro doing a SET TIMEZONE='UTC' before each request, but honestly, I'd like to avoid that and do it only at connection time. Now, I've read through cl-postgres code, and I was hoping being able to plug my code at connection time, but it seems everything is defined as function and not as method, which seems weird to me, and not really pluggable in the end.
Any hint appreciated!
Cheers,
Julien Danjou ;; Free Software hacker ; freelance consultant ;; http://julien.danjou.info
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,
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 timezonehttp://www.postgresql.org/docs/9.1/static/runtime-config-client.html#GUC-TIMEZONEparameter, 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@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
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!
On 7/24/2013 10:37 AM, Julien Danjou wrote:
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!
Baring the rest of the issues you might be experiencing with these layered systems, "2013-01-01 11:00:00" is considered timezoneless (ie: local zone of whatever computer is interpreting it). If you wish to insert "2013-01-01 04:00:00 UTC" the correct spelling is "2013-01-01 04:00:00Z" or "2013-01-01 04:00:00+0" which are two spellings of a timestamp in UTC. Both should be recognized by postegresql correctly.
This call to local-time:format-timestring should allow you to produce timestamps in the correct zone. (local-time:format-timestring nil (local-time:encode-timestamp 0 0 0 12 01 01 2013) :timezone local-time:+utc-zone+)
This wikipedia article should help. https://en.wikipedia.org/wiki/ISO_8601
Hope this helps, Russ Tyndall Acceleration.net Software Developer
On Wed, Jul 24 2013, Russ Tyndall wrote:
Baring the rest of the issues you might be experiencing with these layered systems, "2013-01-01 11:00:00" is considered timezoneless (ie: local zone of whatever computer is interpreting it). If you wish to insert "2013-01-01 04:00:00 UTC" the correct spelling is "2013-01-01 04:00:00Z" or "2013-01-01 04:00:00+0" which are two spellings of a timestamp in UTC. Both should be recognized by postegresql correctly.
This call to local-time:format-timestring should allow you to produce timestamps in the correct zone. (local-time:format-timestring nil (local-time:encode-timestamp 0 0 0 12 01 01 2013) :timezone local-time:+utc-zone+)
Thanks, that's helpful. I hoped there was a way to insert a timestamp with a timezone attached without passing by a conversion to its string representation, but it seems impossible.
But this seems to work fine, event with DAO as far as I can see.
Cheers,
postmodern-devel@common-lisp.net
-
Julien Danjou -
Russ Tyndall -
Sabra Crolleton