Relational databases can store date and date time formats, but there are a few gotchas to watch out for when working with dates and times. Servers use UTC as the timezone, but macs use the local timezone, so you must be careful to avoid a potentially elusive bug.
The following code snippet demonstrates how to add a table to a Rails application with columns that are stored in the date and time formats.
$ rails g model DataRow datetime_column:datetime date_column:date $ rake db:migrate $ rails c >> data_row = DataRow.create!(datetime_column: Time.now, date_column: Date.today) >> data_row.datetime_column => Fri, 05 Jul 2013 23:02:36 UTC +00:00 >> data_row.date_column => Fri, 05 Jul 2013
If you store a Time object in a datetime column, the object will surprisingly be stored as a time object and the time will be maintained. If you store a date object in the Time column, the 00:00:00 in your local timezone will be converted to UTC and saved. Once the code is pushed to production, the app would presumably just store the UTC 00:00:00 time.
>> weird_data_row = DataRow.create!(datetime_column: Date.today, date_column: Time.now) >> weird_data_row.datetime_column => Fri, 05 Jul 2013 04:00:00 UTC +00:00 >> weird_data_row.date_column => 2013-07-05 19:10:29 -0400
To find all objects that with a datetime_column the same as today, you can use a query like this:
>> t = Time.now => 2013-07-05 19:26:53 -0400 >> DataRow.where("datetime_column < ? AND datetime_column > ?", t.end_of_day, t.beginning_of_day) DataRow Load (0.3ms) SELECT "data_rows".* FROM "data_rows" WHERE (datetime_column < '2013-07-06 03:59:59.999999' AND datetime_column > '2013-07-05 04:00:00.000000') => [#<DataRow id: 1, datetime_column: "2013-07-05 23:02:36", date_column: "2013-07-05", created_at: "2013-07-05 23:02:36", updated_at: "2013-07-05 23:02:36">]
This can be a bit misleading because of the timezone correction. The utc method can be used to convert local times to utc, so the same time is used locally and on the server. The following example illustrates that the utc method should be used before other time methods are used, to avoid unexpected results.
# locally >> Time.now.beginning_of_day.utc => 2013-07-06 04:00:00 UTC >> Time.now.utc.beginning_of_day => 2013-07-06 00:00:00 UTC # on server irb(main):003:0> Time.now.beginning_of_day => 2013-07-06 00:00:00 +0000