Hi,
This is partly postmodern, partly postgresql related.
I was wondering whether it would not be useful to also do the following when loading simple-date above postmodern:
(in-package :simple-date)
(cl-postgres:set-sql-datetime-readers :timestamp-with-timezone (lambda (usecs) (multiple-value-bind (days usecs) (floor usecs +usecs-in-one-day+) (make-instance 'timestamp :days (+ days +postgres-day-offset+) :ms (floor usecs 1000)))))
Or even in place of the normal :timestamp binding (from which the above code is copied) ?
From my experiments and reading, using timestamp with time zone as SQL type is more useful than timestamp.
What I want is a created or last-modified timestamp that is set or updated automatically by SQL and not CL with a default like now(). The problem seems to be that the way now() is interpreted is dependent on the time zone set for the session (or the time zone default of postgresql, which defaults to your machine's).
The only correct thing to store are GMT timestamps and to adjust those to the viewer's own time zone. psql does that correctly only with the type timestamp with time zone and not with the type timestamp, although they seem to be the same thing internally.
In CL, (simple-date:universal-time-to-timestamp (get-universal-time)) is always GMT, indepedent of the current (viewer's) timezone. In PG SQL this seems to be hard to do using now() or similar functions.
So out of the box reader support for that type would be useful.
A second question is how to correctly specify this col-type in a DAO since :timestamp-with-time-zone doesn't generate the necessary spaces when generating the table definition and a string is not considered a CL type ?
Any thoughts ?
Thanks,
Sven
Hey Sven,
I had no idea now() behaves differently depending on the data type. I can see the problem. There are two new patches in the repo, one adding a reader for timestamp with time zone values as you suggested, and one replacing dashes with spaces when writing out type names. That second one is a bit of a hack, but seems to work well in most cases.
Cheers, Marijn
On Mon, Apr 14, 2008 at 3:32 PM, Sven Van Caekenberghe scaekenberghe@common-lisp.net wrote:
Hi,
This is partly postmodern, partly postgresql related.
I was wondering whether it would not be useful to also do the following when loading simple-date above postmodern:
(in-package :simple-date)
(cl-postgres:set-sql-datetime-readers :timestamp-with-timezone (lambda (usecs) (multiple-value-bind (days usecs) (floor usecs +usecs-in-one-day+) (make-instance 'timestamp :days (+ days +postgres-day-offset+) :ms (floor usecs 1000)))))
Or even in place of the normal :timestamp binding (from which the above code is copied) ?
From my experiments and reading, using timestamp with time zone as SQL type is more useful than timestamp.
What I want is a created or last-modified timestamp that is set or updated automatically by SQL and not CL with a default like now(). The problem seems to be that the way now() is interpreted is dependent on the time zone set for the session (or the time zone default of postgresql, which defaults to your machine's).
The only correct thing to store are GMT timestamps and to adjust those to the viewer's own time zone. psql does that correctly only with the type timestamp with time zone and not with the type timestamp, although they seem to be the same thing internally.
In CL, (simple-date:universal-time-to-timestamp (get-universal-time)) is always GMT, indepedent of the current (viewer's) timezone. In PG SQL this seems to be hard to do using now() or similar functions.
So out of the box reader support for that type would be useful.
A second question is how to correctly specify this col-type in a DAO since :timestamp-with-time-zone doesn't generate the necessary spaces when generating the table definition and a string is not considered a CL type ?
Any thoughts ?
Thanks,
Sven
postmodern-devel mailing list postmodern-devel@common-lisp.net http://common-lisp.net/cgi-bin/mailman/listinfo/postmodern-devel
Marijn,
On 14 Apr 2008, at 16:08, Marijn Haverbeke wrote:
I had no idea now() behaves differently depending on the data type. I can see the problem.
Well, the documentation,
http://www.postgresql.org/docs/8.3/static/datatype-datetime.html
is quite confusing around these issues, it does seem to be possible to do extra conversions, but I would expect their now() to work like (get- universal-time), but I am no postgresql expert.
There are two new patches in the repo, one adding a reader for timestamp with time zone values as you suggested, and one replacing dashes with spaces when writing out type names. That second one is a bit of a hack, but seems to work well in most cases.
Thanks for the patches, it now works for me.
Sven
PS:
I would like to add that Postmodern is a very nice project, well documented. I know from experience that this requires a lot of work. I was especially surprised (in the good way) by the quality of your source code: very readable, using many CL features in a clean way. Great work!
postmodern-devel@common-lisp.net
-
Marijn Haverbeke -
Sven Van Caekenberghe