postmodern-time-functions (Updated 19 October 2018)

Note: The default readtable in postmodern now returns integers unless the simple-date library is loaded, in which case it will return simple-date classes which pay no attention to timezones. So you need to pay attention to your use of timestamps, and the simple-date and local-time libraries.

To load the simple-date library:

(ql:quickload :simple-date)

(ql:quickload :simple-date/postgres-glue)

(setf cl-postgres:*sql-readtable*

        (cl-postgres:copy-sql-readtable

         simple-date-cl-postgres-glue:*simple-date-sql-readtable*))

Time Functions (now, current-timestamp, current-date, date-trunc, date-part)

Insertions or Updates

When doing insertions or updates, you need to pass a timestring, not a timestamp or a universal time number. Eg.

(postmodern:query (:update 'quotations :set 'updated-at '$1 :where (:= 'id 451)) (local-time:format-timestring nil (local-time:now)))

Time Functions with a Dao (examples helpfully provided by Cyrus Harmon)

(defclass time-test ()

  ((id :accessor id :col-type integer :initarg :id :initform 0)    (created-at :accessor created-at :col-type timestamp :initarg :created-at                :initform (simple-date:universal-time-to-timestamp                           (local-time:timestamp-to-universal                            (local-time:now)))))   (:metaclass postmodern:dao-class)   (:table-name time-test)   (:keys id))

Then the following works:

(with-test-connection   (execute (dao-table-definition 'time-test)))  (with-test-connection   (let ((dao (make-instance 'time-test :id 1)))     (insert-dao dao)))  (with-test-connection   (let ((dao (make-instance 'time-test                             :id 2                             :created-at "2016-12-02 05:26:57.459133")))     (insert-dao dao)))  (with-test-connection   (let ((dao (get-dao 'time-test 1)))     (update-dao dao)))

IMPORTANT: this will create a timestamp field without a timezone, not a timestampz field (timestamp with a timezone)

Minor Notes:

Return Type Oddities

A few of the postgresql date time functions don't quite return what I expected using postmodern. The date-part function returns a double-float, regardless of whether you are using s-sql or raw sql. You will get the same return from the extract function as well.

(query (:select (:date-part "year" 'updated-at) :from 'countries :where (:= 'name "Nevis")) :single) 2011.0d0

TimeZones

Postgresql stores a timestamp in UTC (Universal Coordinated Time - known to most people as Greenwich Mean Time). It does not store the timestamp information - it converts to and from UTC as it reads out or insert or updates records. If you provide a timestamp, it will use that timestamp. Otherwise it will use the system's TimeZone parameter in converting to UTC.