Looks like toolmantim for the win. I owe you a beer (at webjam). We'll do some more testing today and see what we come up with.
Cheers, Carl. Carl Woodward 0412218979 [email protected] On Tue, Oct 6, 2009 at 11:52 PM, Jeremy Grant <[email protected]> wrote: > > 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 -~----------~----~----~----~------~----~------~--~---
