When should i use DateTime vs date, time fields in ruby/rails?

Question

I currently have separate game_date and game_time fields and I am having a hell of a time comparing my DateTime.now to a concatenated DateTime because of time zone issues. Should I redesign my database to just use DateTime? I have a time field separately because the time can be NULL at some points in time. What is the typical practice, and also, how should I resolve my issue with the time zones below?

    now = DateTime.now
    @upcoming_games = []
    @past_games = []
    games.each do |game|
      game.game_time = DateTime.now if game.game_time.nil?
      dt = DateTime.parse("#{game.game_date}T#{game.game_time.strftime("%H:%M:00")}")
      if dt >= now
        @upcoming_games << game
      else
        @past_games << game
      end
    end

Solution

The general idea is to use a DateTime as a general purpose representation of time. Where you might be confused is that Time also includes a date component as it is an encapsulation of the UNIX time_t concept of seconds since epoch, or UNIX_TIME() in MySQL terms.

As I explain in another answer, Time is a more limited representation than DateTime and can only represent dates and times up to Jan 18, 2038. A DateTime can represent 4,712 BCE as well as 21,000 years in the future.

If you want a separate field that represents time of day, which it seems like you might want here, you should create a single numerical field that represents either seconds past midnight if that kind of precision is required, or a more convenient "HHMM" representation that doesn't concern itself with the differences between base-60 and base-10.

Another alternative is to have two fields, one being a DateTime and one being a Date. If you're creating a calendar entry with no particular time, populate only the Date field. If it has a time, populate both.

Remember that a date field is populated with a literal date and does not concern itself with time zones, so this can cause trouble if it is not expressed in the user's local time. A DateTime can always be converted to a user's local time if required.

Comments

[Andrew Vit]

+1 for date fields. A "time" can actually be a different "date" in the local zone than what is stored in the database. This makes it impossible to select everything that happened on "January 1" across different time zones.

[Dan Caddigan]

The note about Time's range limitations predates 1.9.3. The range is much larger now. See stackoverflow.com/questions/1261329/…

[tadman]

The internal Time class can handle a much wider range now, it's true. The differences between these classes are fairly minor now, but DateTime still has more date calculation methods added in by Rails.