7

I am trying to find events on certain days with this code:

Event.where('starttime BETWEEN ? AND ?', DateTime.now.beginning_of_day, DateTime.now.end_of_day)

In rails console if I run DateTime.now.beginning_of_day I get exactly what I expect:

Mon, 09 Apr 2012 00:00:00 -0700

I can see where the problem is occurring but looking at the SQL in rails console. Somehow when the date makes it into the SQL query it gets automatically formatted to the wrong date and time.

SELECT "events".* FROM "events" WHERE (starttime BETWEEN '2012-04-09 07:00:00' AND '2012-04-10 06:59:59')

This is giving me results that vary from today until tomorrow, as the sql above says. I can see that when the DateTime.now.beginning_of_day also DateTime.now.end_of_day are being formatted incorrectly once they make it into the sql query. Do I need to be formatting this in a certain way? Any idea why it would go to 7:00 of today and 7:00 of tomorrow?

I don't know if it makes a difference but I'm using PostgreSQL.

Thanks!

botbot
  • 7,299
  • 14
  • 58
  • 96

3 Answers3

18

See: http://railscasts.com/episodes/106-time-zones-in-rails-2-1

I think the times you're quoting above are actually the same time, just one is UTC time (you're on pacific time right?). To fix this you could try setting:

config.time_zone = "Pacific Time (US & Canada)"

in your environment.rb

You could also try:

DateTime.now.utc.beginning_of_day
DateTime.now.utc.end_of_day

so the you're using UTC as per the DB

rainkinz
  • 10,082
  • 5
  • 45
  • 73
  • hey i appreciate the answer. i don't know if i completely follow you, but i don't think it would be a good idea to change my config to pacific time, because what about those users who are not in pacific time? i hope i get what you're saying. also, using DateTime.now.utc.beginning_of_day is causing the same problem when i run the queries that i listed in my initial post. – botbot Apr 10 '12 at 07:10
  • yes, i'm in pacific time. after doing some research it seems like this is a possible solution. i'm going to try this. thanks for the suggestion. i'm still worried about users in NY, i don't know if that would be a problem, but having the app work in pacific time is definitely good enough for prototyping at this point. thanks. – botbot Apr 11 '12 at 06:35
  • @masterkrang, if you need to worry about users in NY and different timezones, then record what timezone the user is in and set it appropriately for that users request, e.g. https://gist.github.com/809775 – rainkinz Apr 11 '12 at 15:56
  • i believe this is the right answer, at least for now with what i'm doing. when running my queries to find all events that happen today, i added 'utc' to Time.now.utc.beginning_of_day, when canverted to sql in my .where() call, it correctly queries the right date. thanks again for the answer. i have quite a bit more digging and understanding of how the dates work but this is good enough for now. – botbot Apr 11 '12 at 23:44
2

You are probably using UTC time in your database. This is a good thing but it leads to all sorts of confusion. The -0700 offset is converted to 07:00:00 since that's when your day starts in UTC time.

tadman
  • 208,517
  • 23
  • 234
  • 262
  • thanks for the answer. so if it's good to use utc, then how do i prevent the offset from being converted to a time when i run queries? – botbot Apr 10 '12 at 02:24
  • The reason you use UTC is so that you don't need to keep track of what time-zone the database was created in, and so you can easily convert from zero-offset UTC to local-times as required. Most locales change time-zone offsets throughout the year because of DST. "00:00:00 -0700" is equal to "07:00:00 -0000". Are your `starttime` values stored in UTC or have you inadvertently stripped off the time zone information before saving those? – tadman Apr 10 '12 at 03:08
  • the field in the postgresql database are "timestamp without time zone". i'm realizing this is a pretty big problem across all dates in my app. if i set a property in an ActiveRecord let's say Model.my_var = Time.now, then call save, the Time.now is saved in the database as tomorrow some time. for example, if Time.now = Wed Apr 11 22:48:12 -0700 2012, when i call save, i can see that the sql is saving the date as "2012-04-11 05:47:47", which is the next day 7 hours later. i do realize it's because the -700 that comes from Time.now. i am just trying to figure out how to solve this. any ideas? – botbot Apr 11 '12 at 05:58
  • perhaps i should be using before_save in my models to convert the date into something more postgresql friendly, like Time.now.utc? it just seems like a hack to be modifying all the dates before creating the record. at this point i'm not even sure if i want to use postgres on my final product. seems like an ideal solution would be to not modify the code, but the behavior of the database, but i'm not sure. i really appreciate your feedback. – botbot Apr 11 '12 at 06:00
  • You seem to be confusing the two times. It's not "tomorrow", it's just a UTC representation of the same time. The database should store times with zero offset, so the offset needs to be applied before being saved, and if required, applied after being loaded. Databases **should** store date-times in UTC. Your application is responsible for rendering them in whatever time-zone you want. – tadman Apr 11 '12 at 16:02
  • This is not a Postgres issue because the SQL `DATETIME` field works exactly the same way on a large number of platforms. MySQL, SQL Server, SQLite, DB2, and a host of others all behave the same way. – tadman Apr 11 '12 at 16:02
  • I hope you realize those two times are equivalent. – tadman Apr 11 '12 at 16:04
2

You could use the PostgreSQL AT TIME ZONE construct with UTC timestamps:

SELECT e.*
FROM   events e
WHERE  starttime >= '2012-04-09 00:00' AT TIME ZONE 'UTC'
AND    starttime <  '2012-04-10 00:00' AT TIME ZONE 'UTC'

I recently explained PostgreSQL timestamp and time zone handling in a related answer.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • hey Erwin, i appreciate the answer. i will try this out but the one thing that makes me feel reluctant to use this as the solution is that if i want to change to mysql or back to sqlite, it'd be a shame to have to change all these things around. there is something really nice about being able to call Model.where('datetime BETWEEN ? AND ?', Time.now.beginning_of_day, Time.now.end_of_day). perhaps i'm dreaming though, at this point it's not clear whether your solution is the most elegant. – botbot Apr 10 '12 at 07:14
  • 1
    @masterkrang: I am all for convenience. But correctness comes first and performance next. The idea to operate database agnostic leads to low performance, as you can hardly optimize your queries. Abstraction layers are good for basic SQL - but they are mostly primitive crutches. Also, `BETWEEN ... AND ...` usually returns slightly *incorrect* results. Both range limits are included. `00:00` shows up for the day before and after. I try to avoid this in my query - which is hardly more complex than what you have now. Finally: the whole problem with time zones *may* be a misunderstanding. – Erwin Brandstetter Apr 10 '12 at 14:03