postmodern-time-functions

Note: There are two libraries used below: local-time and simple-date. Your choice will impact how times and dates are stored in your database. Make very clear on which library you are using.

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

  • Using Local-time library (recommended)
    Now
    (query (:select (:now))) (("2012-11-18 08:37:49.874461-08"))
    Current-Timestamp
    (query (:select 'current-timestamp) :single) "2012-11-18 08:41:15.435901-08"
    Current-Date
    (query (:select 'current-date) :single) 3562185600 1
    (query (:select 'current-date (:type "now" :time))) ((3562185600 "08:42:11.345821"))
    Return the timestamp as a timestring
    (query (:select (:to-char 'updated-at "YYYY-MM-DD HH24:MI:SS") :from 'countries :where (:= 'name "Nevis")) :single) "2011-01-22 15:20:15"
    Return the timestamp as a timestring limited to the date
    (query (:select (:to-char 'updated-at "YYYY-MM-DD") :from 'countries :where (:= 'name "Nevis")) :single) "2011-01-22"
    (query (:select (:to-char (:now) "DY (Day), Mon (month)"))) (("SUN (Sunday ), Nov (november )"))
    (query (:select (:to-char (:type "yesterday" :timestamp) "FMMonth FMDDth")) :single) "November 17th"
    Date-trunc
    (query (:select (:date-trunc "minute" (:now))) ) (("2012-11-18 08:46:00-08"))
    Date-part
    (query (:select(:date-part "year" 'updated-at) :from 'countries :where (:= 'name "Nevis")) :single 2011.0d0 
    Time Zone Games
    We are trying to play a bit with the timezones. Notice were are selecting at EST, but calling out a PDT timezone and getting a universal time back.
    (query "SELECT '08/31/2011 12:00 pm EST'::timestamptz AT TIME ZONE 'PDT';" :single)
    3523773600
    (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
    Here, on the other hand, we call alling out EST at both places. Compare the hours between the following and the previous calls.
    (local-time:universal-to-timestamp (query "SELECT '08/31/2011 12:00 pm EST'::timestamptz AT TIME ZONE 'EST';" :single))
    @2011-08-31T05:00:00.000000-07:00

  • Simple-date library
    (query (:select 'current-time (:type "now" :time))) (("05:58:04.167966-08" "05:58:04.167966"))
    (query (:select (:now))) ((#<SIMPLE-DATE:TIMESTAMP 22-01-2011T15:06:06,756>))
    (query (:select 'current-timestamp)) ((#<SIMPLE-DATE:TIMESTAMP 22-01-2011T15:06:13,295>))
    (query (:select 'current-date (:type "now" :time))) ((#<SIMPLE-DATE:DATE 22-01-2011> "07:08:14.350286"))
    (query (:select (:to-char 'updated-at "YYYY-MM-DD HH24:MI:SS") :from 'countries :where (:= 'name "Nevis"))) (("2011-01-22 15:20:15"))
    (query (:select (:to-char 'updated-at "YYYY-MM-DD") :from 'countries :where (:= 'name "Nevis"))) (("2011-01-22"))
    (query (:select (:to-char (:now) "DY (Day), Mon (month)"))) (("SAT (Saturday ), Jan (january )"))
    (query (:select (:to-char (:type "yesterday" :timestamp) "FMMonth FMDDth"))) (("January 21st"))
    (query (:select (:date-trunc "minute" (:now))) ) ((#<SIMPLE-DATE:TIMESTAMP 22-01-2011T15:49:00>))

    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