postmodern-time-functions (Updated 3 December 2016)

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.

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

  Query Standard With simple-date library loaded
 Now (query (:select (:now))) ((3689769030)) ((#<SIMPLE-DATE:TIMESTAMP 03-12-2016T15:42:39,147>))
 Current-Timestamp (query (:select 'current-timestamp) :single) 3689769427 #<SIMPLE-DATE:TIMESTAMP 03-12-2016T16:14:25,301>
 Current-Date (query (:select 'current-date) :single) 3689712000 #<SIMPLE-DATE:DATE 03-12-2016>
  (query (:select 'current-date (:type "now" :time))) ((3689712000 ((:HOURS 7) (:MINUTES 57) (:SECONDS 32) (:MICROSECONDS 7860)))) ((#<SIMPLE-DATE:DATE 03-12-2016> #<SIMPLE-DATE:TIME-OF-DAY 08:15:04.177600>))
 timestring (query (:select (:to-char 'updated-at "YYYY-MM-DD HH24:MI:SS") :from 'countries :where (:= 'name "Nevis")) :single) "2014-11-14 17:43:55" "2014-11-14 17:43:55"
  (query (:select (:to-char 'updated-at "YYYY-MM-DD") :from 'countries :where (:= 'name "Nevis")) :single) "2014-11-14" "2014-11-14"
  (query (:select (:to-char (:now) "DY (Day), Mon (month)"))) (("SAT (Saturday ), Dec (december )")) (("SAT (Saturday ), Dec (december )"))
  (query (:select (:to-char (:type "yesterday" :timestamp) "FMMonth FMDDth")) :single) "December 2nd" "December 2nd"
 Date-trunc (query (:select (:date-trunc "minute" (:now))) ) ((3689769480)) ((#<SIMPLE-DATE:TIMESTAMP 03-12-2016T16:17:00>))
 Date-part (query (:select(:date-part "year" 'updated-at) :from 'countries :where (:= 'name "Nevis")) :single) 2014.0d0 2014.0d0
 Time Zone (query "SELECT '08/31/2011 12:00 pm EST'::timestamptz AT TIME ZONE 'PDT';" :single) 3523773600 #<SIMPLE-DATE:TIMESTAMP 31-08-2011T10:00:00>
 With library local-time (local-time:universal-to-timestamp (query "SELECT '08/31/2011 12:00 pm EST'::timestamptz AT TIME ZONE 'PDT';" :single)) @2011-08-31T03:00:00.000000-07:00 Error: Argument X is not a number (Why? Because you
are trying to pass a simple-date timestamp when
local-time:universal-to-timestamp is expecting an integer.
  (local-time:universal-to-timestamp (query "SELECT '08/31/2011 12:00 pm EST'::timestamptz AT TIME ZONE 'EST';" :single)) (("07:59:51.807799-08"  ((:HOURS 7) (:MINUTES 59) (:SECONDS 51) (:MICROSECONDS 807799)))) Same error

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.
Comments