14

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
mu is too short
  • 426,620
  • 70
  • 833
  • 800
Kamilski81
  • 14,409
  • 33
  • 108
  • 161

1 Answers1

23

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.

Community
  • 1
  • 1
tadman
  • 208,517
  • 23
  • 234
  • 262
  • +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. – Andrew Vit Feb 11 '13 at 04:46
  • 1
    The note about Time's range limitations predates 1.9.3. The range is much larger now. See http://stackoverflow.com/questions/1261329/whats-the-difference-between-datetime-and-time-in-ruby/1261435#1261435 – Dan Caddigan Apr 10 '13 at 20:21
  • 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. – tadman Apr 10 '13 at 21:12