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

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?

Use:

TO_DAYS(col)+1721060

To convert to julian date.

And:

FROM_DAYS(col-1721060)

to convert from a julian date.

(I'm using the Chronological Julian date which starts at midnight because it's more useful.)

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.

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.

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

This formula is correct from the year 2000 until 2099:

Julian_date = (YEAR(date)-2000)*1000 + DAYOFYEAR(date)

SELECT
    DATE_ADD(CONCAT(substring(julian_date,1,2), '-01-01'), INTERVAL substring(julian_date,3,3)-1 DAY) 
FROM table