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

SabraOnTheHill	Search this site