[postmodern-devel] Timestamp (with time zone)

[Sven Van Caekenberghe]

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