Hey Ryan, Thanks for the post and yeah totally I agree it seems too complex.
Unfortunately though it's not as easy as just calling a sql date function on an *_at column as that returns the day as utc, which for Carl's example above would return results from 10:00am in the morning local time (as before that would be seen as the previous day) and would add results from 12:00am to 10:00am of the next morning. For example say today was the 2009-02-06 and the local time zone was +10 calling: select * from articles where date(created_at) = '2009-02-06' on the db would treat the local time of 2009-02-06 9:59am as 2009-02-05 11:59pm UTC which is the previous day, yet we would like it to be included since we want our query to be based on the day as local time (+10) sees it. So, what we're trying to do is shift the database dates into the local time before calling the date function in an effort to capture the local time zones day not UTC. Which in Postgres looks something like this: select * from articles where date(created_at + interval '10 hours') = '2009-02-06' Does that make sense? and if it does I wish you'd explain it to me some time... Cheers, Jeremy On Oct 6, 9:50 pm, Ryan Bigg <[email protected]> wrote: > If all times in the DB are UTC then you should be able to do > loaded_on.utc... I feel as if I'm missing a crucial point of this. Your > "solution" seems too complex. > > 2009/10/6 Carl Woodward <[email protected]> > > > > > > > > > Hey guys, > > > Jeremy and I have been having discussions about the right way to get > > time zones working in rails. > > > We are storing dates as UTC and displaying the time back works fine. > > > The problem occurs when you need to query for items on a date. We end > > up with code like: > > > named_scope :by_date, lambda{ |loaded_on| > > {:conditions => ["date(preferred_time + interval '10 hour') = > > date(?)", loaded_on.to_date]} > > } > > > NOTE: we are using postgres. > > > Or: > > > Time.zone.local_to_utc loaded_at.to_date.to_time > > > All of which are crap. > > > We are even thinking of putting this stuff into a plugin and making it > > happen automagically but I struggle to believe that this problem > > hasn't been solved before. In other apps that I have done before I > > tend to just hack it till it works but I would really like to solve > > this problem. > > > I'm just wondering if anyone has a nicer solution for this stuff? > > > Let me know? > > > Cheers, > > Carl. > > > Carl Woodward > > 0412218979 > > [email protected] > > -- > Ryan Bigg --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby or Rails Oceania" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/rails-oceania?hl=en -~----------~----~----~----~------~----~------~--~---
