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

  • 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)))
Minor Notes: 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
Comments