search

hackinghat / cl-mysql

Common Lisp MySQL library
http://hackinghat.com/index.php/cl-mysql
53 stars 17 forks source link

"0000-00-00 00:00:00" as datetime #2

Open bhyde opened 11 years ago

bhyde commented 11 years ago

I found these datetime scattered thru out a WordPress database. Which understandably makes cl-mysql-system::string-to-date cranky. The mysql doc http://dev.mysql.com/doc/refman/5.0/en/datetime.html say "Illegal DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00').". But then I notice some people talking about null values. I'm not enough of a sql/mysql maven to know how these ought to be handled.

I did this so as to be able to get on with my read-only life...

(defun string-to-universal-time (string &optional len)
  (declare (optimize (speed 3) (safety 3))
           (type (or null simple-string) string)
           (type (or null fixnum) len))
  (cond
    ((string= "0000-00-00 00:00:00" string)
     0)
    ((and string (> (or len (length string)) 0))
     (+ (string-to-date (subseq string 0 10))
        (string-to-seconds (subseq string 11))))))