Summary: Dealing with time can be difficult. ActiveSupport helps. Store daily time values (e.g., daily schedules, irrespective of date) in a database as offsets from midnight (in seconds).

—————————————————

I’m working on a trip planner app using NJ Transit data to find the next departure time for a trip, given its origin and destination. For example, what’s the next train leaving Hoboken for Secaucus?

It works!

At least as a proof of concept (full code here). I know, it’s Jersey—not that great. Also messy SQL/Active Record queries… Those things aside, the fact that it’s working means an annoying hurdle is behind me (or I at least better understand how to get around it). That hurdle is comparing time.

About Time Comparisons

To determine the next departure time, I need to determine a time from the database with the current time. Not as easy as it seems.

The code below is a big part of what enabled the screenshot above. That’s good, it works. But it’s not pretty.

Time.now is straightforward enough. And .where(“departure_time” > ?, <time>) makes sense—it’s an Active Record query that fires a SQL statement comparing the departure times from the database with the current time. But what about the .change(year: 2000, month: 01, day: 01), especially with the appended – 18000?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

# Given an origin and destination, I want to get all departure times from origin
def self.all_departures(origin, destination)
  destination.upcase!
  where("stop_id = ?", Stop.find_old_stop_id_by_name(origin)).joins(:trip).where('trip_destination = ?', destination).order(:departure_time)
end

def self.next_departure_from_to(origin, destination)
  current_time = (Time.now.change(year: 2000, month: 01, day: 01) - 18000)
  self.all_departures(origin, destination).where("departure_time > ?", current_time).pluck(:departure_time_string).first

  # want to find departure time from stop_times where departure time is between now and 2 hours from now
      # self.all_departures(origin).where(departure_time: current_hour..(current_hour + 2)).pluck(:departure_time)


  # helpful answer: https://stackoverflow.com/questions/11973225/rails-how-to-store-time-of-day-for-schedule/15350965#15350965
      # "open_at > ? and close_at < ?", Time.now.seconds_since_midnight, Time.now.seconds_since_midnight

  # newtime = Time.new.midnight
  # oldtime = Time.new(2000,01,01,0,0,0,"-04:00")
  # time_to_add = (newtime - oldtime).to_i
end

Active Support and the change helper

Enter Active Support, specifically its Time class (documentation and full source code). Thanks to this StackOverflow answer, I was directed to the ActiveSupport helpers. These inlude beginning_of_day(), seconds_until_end_of_day(), days_in_month(month, year = now.year), and dozens of other awesome helpers.

As my code shows, I chose the change(options) helper. This helper returns a new Time based on a number of parameters. It got the job done, but it was only necessary because of a formatting issue (I also left my previous attempts in the code as artifacts—evidence of my failed attempts at hacking through the time comparison).

The original values I used to populate my database were CSVs, meaning that the time values were strings, like 09:30:00 (hour/minute/second). Simple enough, except that when I seeded the database with those strings as time values, they created values like this: 2000-01-01 09:30:00 UTC. Basically, those time values (as strings) were converted to instances of the Time class, which include year, month, day, and timezone with defaults of 2000, 01, 01, and UTC.

My solution—hacky as it was—was to changemy Time.now to match that format, hence my Time.now of 2014-03-27 09:30:00 -400 became 2014-03-27 09:30:00 -500. This was still off because of the time zone, hence the – 18000. 18000 seconds is how many hours? Five, the exact number I needed to offset for the timezone (UTC, or Coordinated Universal Time, is treated as equivalent to Greenwich Mean Time).

There’s got to be a better way

There is. A lot of what I did is unnecessary. In theory, the dates don’t even need to match up—I could just calculate the difference and append a different value than the 18000 to compensate (though it’d be more difficult to conceptualize). Even that is bad. What I really want to do is store those initial values (the time strings) as offsets from midnight in seconds.

In a roundabout way, I’m back to the original StackOverflow answer, which suggests storing database values as seconds from midnight. I’m still working out how, given strings like “06:48:00”, I can populate my database with offsets from midnight (24480), but I’ve played with irb enough (requiring ‘active_support/time’) to know that Time.parse(“06:48:00”).seconds_since_midnight works. Now I just need to refactor to make that work in my app.