Hey peeps, I just realised that my post from a week and a half was approved finally. Although now it's old, so I thought I'd send through my current thoughts.
Here is my test suite which to take into account a bunch of edge cases, like testing across different timezones, including ones that aren't the system time. http://github.com/jeremygrant/proper_time_zones/blob/master/test.rb My current preferred version includes Tim's range version, see below: {:conditions => {:published_at => start_of_published_on... start_of_published_on.tomorrow}} It's still not perfect, but it is soooo much better than it was before. Although, if anyone has anything better I'm all ears and if you want to test using the test suite all you need to do is replace the named_scope by_published in with your code and run bacon test.rb. Let me know if you have any questions. Cheers, Jeremy On Wed, Oct 7, 2009 at 11:08 AM, Jeremy Grant <[email protected]>wrote: > > Lawrence, > > Although what Tim mentioned about indexes on Postgres is no doubt > correct (I haven't had time to confirm though) that syntax has still > cleaned up my solution for time zone +11 to: > > select * from articles where between '2009-02-06 13:00:00.000000' and > '2009-02-07 13:00:00.000000'; > > and in the named_scope it's > > class Article < ActiveRecord::Base > named_scope :by_local_date, lambda{ |created_at| > {:conditions => ["created_at between ? and ?", created_at.to_time, > created_at.to_time + 1.day]} > } > end > > Now both of the below will work > Article.by_local_date Time.zone.today > Article.by_local_date Time.zone.now > > Which although it is still too much work at least the readability is > better. > > Although, what I would prefer to be able to write is kinda like what > Tim mentioned originally, except using created_on not created_at (even > though the database only knows of the created_at) and have a plugin > turn created_on into the between condition above for you, so it would > look like below: > class Article < ActiveRecord::Base > named_scope :by_local_date, lambda{ |created_at| > {:conditions => {:created_on => created_at} > } > end > > However, I guess that'll have to wait til I have more time :( > > Thanks > > On Oct 7, 9:08 am, Lawrence Pit <[email protected]> wrote: > > > select * from articles where date(created_at) = '2009-02-06' > > > > > select * from articles where date(created_at + interval '10 hours') = > > > '2009-02-06' > > > > Both these queries will suck if you have loads of articles, as the > > function use of +date+ will prohibit the use of an index on created_at. > > > > Use the BETWEEN ... AND ... syntax instead, using beginning and end of > > day of local time converted to utc. > > > > Cheers, > > Lawrence > > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
