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.