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