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