Set the DATETIME field to date zero in Rails

Question

I have a Rails application that manages a MySQL database through ActiveRecord. I have a DATETIME column in this database that I would like to set to date zero . (Not NULL, date zero. Scroll down the page to see what I mean.)

It seems pretty simple, but I can't figure out how to do this without using raw SQL.

Here are some of the things I've already tried:

• field = 0 (Crash with ActiveRecord Column 'field' cannot be null error)
• field = "0000-00-00" (with an error undefined method 'year' for nil:NilClass )
• field = DateTime.parse("0000-00-00") (with DateTime invalid date error)

Is there a way to do this with ActiveRecord, or will I be forced to use raw SQL for this?

EDIT: I am not allowed to modify the structure of the SQL database to fix this. This is a business thing ...

Answer 1

It looks like "default: 0" does what you want:

 class CreateAnothers < ActiveRecord::Migration def change create_table :anothers do |t| t.date :start, null: false, default: 0 t.timestamps null: false end end end 😉 ➤ rake db:migrate == 20150813201736 CreateAnothers: migrating =================================== -- create_table(:anothers) -> 0.0943s == 20150813201736 CreateAnothers: migrated (0.0944s) ========================== [1] pry(main)> Another.create (0.2ms) BEGIN SQL (0.5ms) INSERT INTO `anothers` (`created_at`, `updated_at`) VALUES ('2015-08-13 20:19:02', '2015-08-13 20:19:02') (34.6ms) COMMIT => #<Another:0x0000000ab8eb38 id: 1, start: nil, created_at: Thu, 13 Aug 2015 15:19:02 CDT -05:00, updated_at: Thu, 13 Aug 2015 15:19:02 CDT -05:00> mysql> select * from anothers; +----+------------+---------------------+---------------------+ | id | start | created_at | updated_at | +----+------------+---------------------+---------------------+ | 1 | 0000-00-00 | 2015-08-13 20:20:14 | 2015-08-13 20:20:14 | +----+------------+---------------------+---------------------+ 1 row in set (0.00 sec) 

After clarifying the requirements

So in other words, what is your goal? Do you want to run the update through the rails to change the table? If so, you can do

  ActiveRecord::Base.connection.execute("UPDATE table SET field='0000-00-00' WHERE id=#{self.id}") 

Is this allowed? (YES)

Update

Well, if you want it to be pure Rails, let it be like this:

 [5] pry(main)> a.update_attribute(:start, nil) (1.5ms) BEGIN SQL (3.6ms) UPDATE `anothers` SET `start` = NULL, `updated_at` = '2015-08-14 16:31:22' WHERE `anothers`.`id` = 1 (36.8ms) COMMIT => true [6] pry(main)> a.update_attribute(:start, "0000-00-00") (0.2ms) BEGIN (0.1ms) COMMIT => true mysql> select * from anothers; +----+-------+---------------------+---------------------+ | id | start | created_at | updated_at | +----+-------+---------------------+---------------------+ | 1 | NULL | 2015-08-14 16:30:39 | 2015-08-14 16:31:22 | +----+-------+---------------------+---------------------+ 1 row in set (0.00 sec) 

As you can see, update_attribute does not work, but

 [8] pry(main)> a.update_column(:start, "0000-00-00") SQL (47.1ms) UPDATE `anothers` SET `anothers`.`start` = '0000-00-00' WHERE `anothers`.`id` = 1 => true mysql> select * from anothers; +----+------------+---------------------+---------------------+ | id | start | created_at | updated_at | +----+------------+---------------------+---------------------+ | 1 | 0000-00-00 | 2015-08-14 16:30:39 | 2015-08-14 16:31:22 | +----+------------+---------------------+---------------------+ 1 row in set (0.00 sec) 

update_column does!

Please note that after you do a.update_column(:start, "0000-00-00") , the date field in your variable (in my case "a.start") will be set to "0000-00-00 " And if you want it to display the actual nil value, you need to reload it, like a.reload .

Answer 2

I think this does not work with the proper gregorian calendar since there is no year 0: https://en.wikipedia.org/wiki/0_(year)

Perhaps try the year 01-01-0001.