Invalid date in legacy database

[dgmdan]

I'm using DM to access a legacy MySQL database. One of the datetime columns frequently has values of 0000-00-00 00:00:00. When DM encounters one of these, I get an "invalid date" error. I would like to gracefully handle it instead. Here's the code that causes the error:

class UserProfile
  include DataMapper::Resource
  storage_names[:default] = 'subscriber_profile'

  belongs_to :user, key: true
  belongs_to :school, key: true

  property :profile_start, DateTime
  property :profile_end, DateTime

  def self.active
    all(
      :profile_start.lt => Time.now,
      :profile_end.gt => Time.now
    )
  end
end

When I call UserProfile.active, it finds some of records with zeros for "profile_end" and gives the error. I would like to exclude these records altogether because if it has a profile_end of zeros, it's not an "active" record anyway.

[dceddia]

I'm encountering this problem as well. I'm working with a WordPress MySQL database where WordPress decided "0000-00-00 00:00:00" would be a great default date, and when DataMapper encounters one of these records it raises the "invalid date" exception.

[dkubb]

Can ruby DateTime instances represent that date properly?

Is DataMapper throwing that "invalid date" exception, or is ruby itself throwing that exception when the database driver tries to create a DateTime object?

[dceddia]

From what I can tell, Ruby's DateTime is causing the error. If I start an irb session and try that date, I get the error as well. Would it be acceptable to set the particular DateTime property to nil in this case, instead of throwing an exception? Maybe have an option available as Sequel does.

$ irb
1.9.3-p194 :001 > require 'date'
 => true
1.9.3-p194 :002 > Date.parse('0000-00-00 00:00:00')
ArgumentError: invalid date
    from (irb):2:in `parse'
    from (irb):2
    from /home/dceddia/.rvm/rubies/ruby-1.9.3-p194/bin/irb:16:in `<main>'

[dgmdan]

I'd be happy with that behavior.

[largon]

JDBC driver for MySQL also allows to control this weirdness with "zeroDateTimeBehavior" property in connection string: http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html

[nitsujw]

do_mysql 0.10.9 fixes 0-date issue in mysql (datamapper/do#37).