[postmodern-devel] Timestamp (with time zone)

[Marijn Haverbeke]

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 at 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 at common-lisp.net
>  http://common-lisp.net/cgi-bin/mailman/listinfo/postmodern-devel
>