Rails 2.3.2, Ruby 1.9.3 I want to get all records for one month from charges table. recs = Charge.where(["date >= ? and date < ?", t.beginning_of_month, t.next_month.beginning_of_month]) and this transforms into this query on PostgreSQL SELECT "charges".* FROM "charges" WHERE (date >= ''2012-03-31 22:00:00.000000'' and date < ''2012-04-30 22:00:00.000000'') Which is wrong. Dates should have been 2012-04-01 00:00:00.000000 and 2012-05-01 00:00:00.000000 What is wrong? by TheR -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Time zones :( On 12 April 2012 09:36, Damjan Rems <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> Rails 2.3.2, Ruby 1.9.3 > > I want to get all records for one month from charges table. > > recs = Charge.where(["date >= ? and date < ?", t.beginning_of_month, > t.next_month.beginning_of_month]) > > and this transforms into this query on PostgreSQL > > SELECT "charges".* FROM "charges" WHERE (date >= ''2012-03-31 > 22:00:00.000000'' and date < ''2012-04-30 22:00:00.000000'') > > Which is wrong. Dates should have been 2012-04-01 00:00:00.000000 and > 2012-05-01 00:00:00.000000 > > What is wrong? > > by > TheR > > -- > Posted via http://www.ruby-forum.com/. > > -- > You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. > To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en. >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Peter Hickman wrote in post #1056106:> Time zones :(But how can I avoid this? by TheR -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On 12 April 2012 09:36, Damjan Rems <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> Rails 2.3.2, Ruby 1.9.3 > > I want to get all records for one month from charges table. > > recs = Charge.where(["date >= ? and date < ?", t.beginning_of_month, > t.next_month.beginning_of_month]) > > and this transforms into this query on PostgreSQL > > SELECT "charges".* FROM "charges" WHERE (date >= ''2012-03-31 > 22:00:00.000000'' and date < ''2012-04-30 22:00:00.000000'') > > Which is wrong. Dates should have been 2012-04-01 00:00:00.000000 and > 2012-05-01 00:00:00.000000 > > What is wrong?Nothing is wrong. Those dates are the beginning of the month for the timezone that t is in, converted to UTC as times in the database are in UTC. In order to get the result you expect then make sure that t is a UTC time not a local time. Colin -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Colin Law wrote in post #1056120:> On 12 April 2012 09:36, Damjan Rems <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote: >> 22:00:00.000000'' and date < ''2012-04-30 22:00:00.000000'') >> >> Which is wrong. Dates should have been 2012-04-01 00:00:00.000000 and >> 2012-05-01 00:00:00.000000 >> >> What is wrong? > > Nothing is wrong. Those dates are the beginning of the month for the > timezone that t is in, converted to UTC as times in the database are > in UTC. In order to get the result you expect then make sure that t > is a UTC time not a local time. > > ColinThanks for this one. The problem lies one line before: t = Time.new(params[:year].to_i, params[:month].to_i, 1) And If I use: t = Time.utc(params[:year].to_i, params[:month].to_i, 1) it works OK. Is this a problem of database settings or local server settings or it''s the only right way to do it? by TheR -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On 12 April 2012 11:26, Damjan Rems <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> Colin Law wrote in post #1056120: >> On 12 April 2012 09:36, Damjan Rems <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote: >>> 22:00:00.000000'' and date < ''2012-04-30 22:00:00.000000'') >>> >>> Which is wrong. Dates should have been 2012-04-01 00:00:00.000000 and >>> 2012-05-01 00:00:00.000000 >>> >>> What is wrong? >> >> Nothing is wrong. Those dates are the beginning of the month for the >> timezone that t is in, converted to UTC as times in the database are >> in UTC. In order to get the result you expect then make sure that t >> is a UTC time not a local time. >> >> Colin > > Thanks for this one. The problem lies one line before: > > t = Time.new(params[:year].to_i, params[:month].to_i, 1) > > And If I use: > > t = Time.utc(params[:year].to_i, params[:month].to_i, 1) > > it works OK. > > Is this a problem of database settings or local server settings or it''s > the only right way to do it?The database is not the problem, datetimes should always be in UTC so that they are relate to particular instances in time. You could get round it by setting the server to UTC but that is also not the correct way, the app would fail if someone changed the timezone of the server which would obviously not be a good thing. The correct way is to use Time.utc as you have above. You may also have to think about what happens when a record is written. Are you sure you are constructing the timestamp correctly, in other words is the timezone that is being used to convert the time to UTC for the database the timezone of the user? Also when the user asks for records for a specified month, which month should records with timestamps in the window where local time and UTC are in different months be shown. Life would be much simpler if the world did not go round. Colin -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On 12 April 2012 13:41, Colin Law <clanlaw-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote:> Life would be much simpler if the world did not go round.Or if we decimalised time ;-) -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.