How can I get mysql to output a DateTime to Julian day number?

Question

Basically I am using the MySQL gem for Ruby, and I have no reasonable support for date comparison. The Mysql::Time class only gives me only accessor methods like year, month, second, etc. I could do much better date comparison, if I could turn this into a Ruby DateTime object. How can convert MySQL's DateTime field to a Julian day number which can be passed to DateTime.jd?

Answer 1

Consider using Ruby/DBI instead of using the MySQL gem directly. Ruby/DBI should take care of the conversion into standard Ruby classes for you automatically, and as an added bonus feature if you ever change the DBMS you're running, your use of the DBI doesn't change.

Answer 2

You could use MySQL's TO_DAYS function to get the date as an integer number of days since the year zero (and just add the appropriate offset to have a Julian Day number), or you could use the UNIX_TIMESTAMP function to get an integer number of seconds since 1970-01-01 00:00:00 UTC.

Answer 3

class Mysql::Time
  def to_datetime
    DateTime.civil(year,month,day,hour,minute,second)
  end
end