Querying time of day from in a Rails datetime column (with time zones)

Question

I have some news events that are stored in a database via a postgresql datetime column. However, I would like to run a query on the time of day the event happened. Something like

Event.where("occurred_at < '11:00'")

which you can do if this was a postgresql time column.

To add an additional wrinkle, I have my Rails time zone set to Eastern time for the whole application

config.time_zone = 'Eastern Time (US & Canada)'

So if you were to try to extract the database time somehow, that time would be in UTC, so for during half the year the time comparison would be off by 1 hour (because of daylight savings).

The only thing I can think of is to maintain a separate database column of 'time' type.

Edit and clarification

The tricky part of this question is that the UTC offset changes during half the year because of daylight savings. Time.zone.parse("2013-01-01 10:00") gives Jan 1, 2013 10:00 EST -05:00 (i.e. 15:00 UTC) while six months later Time.zone.parse("2013-07-01 10:00") gives Jul 1, 2013 10:00 EDT -04:00 (i.e. 14:00 UTC). So you cannot just query the time without knowing the date as well.

Please see : http://stackoverflow.com/questions/4002958/rails-utc-to-local-and-daylight-savings-time for demo on how Rails will account for timezone offsets in areas that use daylight savings time. — Beartech, Dec 22 '13 at 21:45
Are you wanting to query the time, regardless of the day it happened on? — Beartech, Dec 22 '13 at 21:57

Beartech · Answer 1 · 2013-12-24T04:01:04.560

3

I have completely deleted and rewritten this answer, as the old answer only worked with SQLite and not Postgres which the OP asked about.

The only easy way around this I could find (and the most Rails-like) was to use a gem. The gem is "tod" and can be found here: https://github.com/JackC/tod.

First you need to add gem 'tod' to your Gemfile and run bundle. Then you would create a column in the DB of the type time.

Add this to your Event.rb model

class Event < ActiveRecord::Base
  serialize :time, Tod::TimeOfDay

Now you have a way to grab a timestamp and save just the time (i.e. 13:45:00) in a serialized format. The TimeOfDay class will give you the methods you need to recall and manipulate the field.

In your create action you could have something like (this assumes column in DB called "tod_stamp" of type time and a DateTime object called :occured_at):

def create
  @event = Event.new(event_params)
  tod_stamp = @event.occured_at.strftime('%H:%M:%S')
  @event.tod_stamp = TimeOfDay.parse(tod_stamp)

  respond_to do |format|
    if @event.save
      format.html { redirect_to @event, notice: 'Event was successfully created.' }
      format.json { render action: 'show', status: :created, location: @event }
    else
      format.html { render action: 'new' }
      format.json { render json: @event.errors, status: :unprocessable_entity }
    end
  end
end

Now when you ask the DB for something like:

my_events = Event.where("tod_stamp < ?", "10:00:00")

you will get the results based on the actual time of day recorded for the event. It won't matter if you are in a time zone that uses daylight savings time. You also get the other methods for TimeOfDay parsing, comparison and manipulation as documents on the tod homepage.

As usual I should have looked for a gem before I tried reinventing the wheel. :-) Hope this helps.

edited Dec 24 '13 at 04:01

answered Dec 23 '13 at 01:56

Beartech

6,173
1
18
41

Nice answer. Yes the timezone portion is the tricky part of it. Time.zone.parse("2013-01-01 10:00") gives Jan 1, 2013 10:00 EST -05:00 while six months later Time.zone.parse("2013-07-01 10:00") gives Jul 1, 2013 10:00 EST -04:00. So by converting just the time (and not the date) to UTC, I will get off-by-1-hour errors for half the year. – idrinkpabst Dec 23 '13 at 02:48
I think you are still confusing what Rails returns when you ask it for a time vs what is in the DB in UTC. No matter what time of year it is, 08:00 EST will always translate in to 03:00 UTC. Let's say you are in January and you save a timestamp of 08:00:00 for an even that just happened, Rails will save a UTC date-time of 03:00 in the DB column. In June when you save another timestamp at 08:00, Rails will still translate it into 03:00. It is making the changes of -04:00 and -05:00 because you (or your computer) has physically changed your clock forward or back and hour. – Beartech Dec 23 '13 at 03:29
Good comments, but (1) The UTC time *does* change with daylight savings. 10:00 EST on Jan 1 is 15:00 UTC but 10:00 EST on Jul 1 is 14:00 UTC. In Rails, you can use Time.zone.parse("2013-01-01 10:00").utc to validate. (2) why are you querying 'created_at' in your example? I believe if you queried 'occurred_at' you would get a different answer. – idrinkpabst Dec 23 '13 at 04:39
My point is either Rails is making the corrections to give you the right results, or you are not being clear on what it is you are trying to accomplish. The code I posted in my first original answer allowed you to pick a time to query, let's say 09:00, and query against the occurred_at column, returning results that made the appropriate corrections for both time zone and Daylight Savings Time, regardless of what time of year the time stamp was created or the query was made. – Beartech Dec 23 '13 at 04:52
Made the corrections and re-ran those queries with the same results. You said "The UTC time does change with daylight savings." that is wrong. UTC time is UTC time. Eastern Standard Time changes with daylight savings, as does any time zone that observes daylight savings. Rails is aware of this. That is why Rails is doing the juggling between the timestamp stored in the DB and they code I am writing, so I don't have to worry about that. – Beartech Dec 23 '13 at 05:03
Just a clarification in my comment above. I said "Eastern Standard Time" but that is actually 'Eastern Time (US & Canada)'. There is such a time zone as EST, it is used in the parts of Eastern Time that do not observe daylight savings. – Beartech Dec 23 '13 at 05:11
Shouldn't the query be: Event.where("occured_at::time > ?", start_time). Still your answer doesn't quite make sense. In your models, event1.occurred_at = '17:00:00' and event2.occurred_at = '16:00:00' so to query WHERE (occurred_at > '17:00:00') would not work – idrinkpabst Dec 23 '13 at 05:55
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/43723/discussion-between-beartech-and-idrinkpabst) – Beartech Dec 23 '13 at 06:06
The above comments apply to the old answer. I completely rewrote the answer because the old one really didn't answer the OP's full question. – Beartech Dec 24 '13 at 04:02

Querying time of day from in a Rails datetime column (with time zones)

1 Answers1