42

Rails has been good with automatically inserting correctly formatted datetimes in MySql without the need for me to give it much thought.

However, for doing a validation, I need to check if a stored mysql datetime value (ie 2008-07-02 18:00:00) is greater than or less than "now". I can call DateTime.now or Time.now but how can I convert that into the format mysql likes?

Thanks

user94154
  • 16,176
  • 20
  • 77
  • 116

2 Answers2

109

You can use to_s(:db) to convert into a database friendly format.

Time.now.to_s(:db)

However, be careful if you have a timezone specified in Rails because the time will be stored in UTC in the database. You'll need to specify that to do proper comparisons.

Time.now.utc.to_s(:db)

You can also use NOW() function in MySQL instead of generating the current time in Ruby.

ryanb
  • 16,227
  • 5
  • 51
  • 46
  • 17
    thanks. its awesome to have the railscasts guy answer your questions :) – user94154 Aug 10 '09 at 19:46
  • 1
    This should really be a method in rails! – Hopstream Nov 07 '11 at 12:38
  • 100 thanks. This answer just reminded me of NOW()'s existence. ActiveRecord sure has spoilt me. I've been passing Time.now since I hardly touch SQL anymore. Gone are the days of hand-tweaking SQL (at least for me). – Swartz Nov 25 '11 at 09:21
  • perfect! Thanks for the solution @ryanb! And railscasts is the best! – abhir Jan 10 '13 at 08:30
  • Thanks so much. This is exactly what I needed! Seems like the discrepancy is from SQL's lack of ISO 8061 compliance, https://msdn.microsoft.com/en-us/library/ms187819.aspx Re: SQL "datetime is not ANSI or ISO 8601 compliant." – aaron-coding May 04 '15 at 23:23
  • This didn't quite work for me. I had to use `ActiveRecord::Base.sanitize(time)` to get the format I needed. (requires ActiveRecord of course, so mostly applies to Rails) – yuval Sep 29 '15 at 21:56
6

You don't need to. Let Rails do the work for you:

If your model is Widget this will find all the widgets that have been created in the last day:

Thing.find(:all, :condition => ["created_at > ?", Time.now - 1.day])

Rails will automatically convert the timestamp into the correct format.

levinalex
  • 5,889
  • 2
  • 34
  • 48
  • thanks for the response but this doesn't fit my use case. I already have my object retrieved from the DB. Long after it is retrieved, I need to run a time-based validation on it so the check has to be done in Ruby code and not in a query. Thanks again though. – user94154 Aug 10 '09 at 20:24
  • I had a different scenario, and this worked perfectly. Thanks. – aronchick Jul 20 '10 at 19:22
  • In Rails 4+ just use `Thing.where('created_at > ?', 6.months.ago)` – Sandro L May 18 '17 at 07:21