I was poking around at my project, Chirp, today and finally decided to deal with a strange bug I was having. When I sent JSON down to the AngularJS controller that renders the page, dates were always coming back as the time the request was made at. This was strange, since the database records of the chirps store when they were created, and the time should have been that. The one I was testing was over a week old.

Eventually, I realized that in the slot definition

(created-at :type clsql:wall-time
:initform (clsql:get-time))

the time stamp would always be the current if the time wasn't set on the instance when the database was read.

I'd recently started using clsql-helper to get instances of classes back from complicated SQL queries. By 'complicated' I mean 'contains a basic join.' Apparently, CLSQL doesn't feel it needs to do that. I wish I knew why.

In any event, when I used clsql-helper, the dates were wrong, when I used clsql's built-in #'select, the dates were correct. So it slowly dawned on me that clsql-helper was doing something wrong. Sure enough, it wasn't converting the string that the time stamp returned into the wall-time instance that clsql expected.

So I took the chance to play around with the MOP a bit and wrote code to convert the values to the right type, looking at their slot definitions in the class.

(defun get-slot-by-name (name class)
(find name (closer-mop:class-direct-slots (find-class class))
:key (lambda (slot)
(intern (symbol-name (closer-mop:slot-definition-name slot))

(defun coerce-value-to-column-type (class column value)
(let ((type (clsql-sys::specified-type (get-slot-by-name column class))))
(coerce-value-to-db-type value type)))

With that code, you just do something like

(coerce-value-to-column-type class coulmn data)

and things come out the right way.