[Special Characters][A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [P] [Q] [R] [S] [T] [U] [V] [W] [X] [Y] [Z]

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.


While postmodern is generally thread-safe, it is up to the developer to pay attention and remember that postgresql may be spinning off processes at the same time that you are creating threads in your application. 

Something like taking from the postmodern/tests.lisp: 
(defclass test-data ()
  ((id :col-type serial :initarg :id :accessor test-id)
   (a :col-type (or (varchar 100) db-null) :initarg :a :accessor test-a)
   (b :col-type boolean :col-default nil :initarg :b :accessor test-b)
   (c :col-type integer :col-default 0 :initarg :c :accessor test-c))
  (:metaclass dao-class)
  (:table-name dao-test)
  (:keys id))

(execute (dao-table-definition 'test-data))

(defvar *dao-update-lock* (bt:make-lock))

(let ((item (make-instance 'test-data :a "SC" :b t :c 0))) 
  (with-test-connection (save-dao item))
  (let ((id (test-id item))) 
    (loop for x from 1 to 50 do
           (lambda () (with-test-connection 
                       (loop repeat 1000 do (bt:with-lock-held (*dao-update-lock*) (incf (test-c item) 1)) (save-dao item))
                       (loop repeat 1000 do (bt:with-lock-held (*dao-update-lock*) (decf (test-c item) 1)) (save-dao item)))))) 
    (with-test-connection (describe (get-dao 'test-data id)))))

may return to your control before all the processes are done. As a result, if you check for value of:
 (test-c (get-dao 'dao-test 1))
when the lisp code returns, you may be surprised that the answer is not 0. Check a few seconds, later and it may be a different number. If you call
(query (:select '* :from 'pg-stat-activity))
you may notice that there are still outstanding connection - postgresql is still working its way through the processes you just created and it will get through all of them and you will notice that the value has been finally incremented and decremented down to 0.

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

  • Using Local-time library (recommended)
    (query (:select (:now))) (("2012-11-18 08:37:49.874461-08"))
    (query (:select 'current-timestamp)) (("2012-11-18 08:41:15.435901-08"))
    (query (:select 'current-date)) ((3562185600)) 1
    (query (:select 'current-date (:type "now" :time))) ((3562185600 "08:42:11.345821"))
    (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)"))) (("SUN (Sunday ), Nov (november )"))
    (query (:select (:to-char (:type "yesterday" :timestamp) "FMMonth FMDDth"))) (("November 17th"))
    (query (:select (:date-trunc "minute" (:now))) ) (("2012-11-18 08:46:00-08"))
    (query (:select (:date-part "year" 'updated-at) :from 'countries :where (:= 'name "Nevis")) :single) 2011.0d0
  • 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


Postmodern has no truncate table function, so you will need to call it yourself using a string.