created_at is stored differently in mysql then in sqlite.
sqlite stores the dates like: 2011-04-14 22:52:52.758612
and mysql stores the date like: 2011-04-14 22:52:52 (possible rounded)
When I output the date with json formatting, it''s returned as
2011-04-14T22:52:52Z regardless of the underlaying db. But in another part
of my application I request all items with a date newer then the above.
However since "2011-04-14 22:52:52.758612" is bigger then
"2011-04-14
22:52:52" I get the same item again when I query against sqlite (or
postgresql actually).
In my model I have the following scope defined: scope :since, lambda {|time|
where("updated_at > ?", time) }
which I''m using for getting all news items since a current date.
Any suggestions about how to fix this?
thanks in advance,
Seb
--
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 15 April 2011 00:07, Seb <sebastianthegreatful-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> created_at is stored differently in mysql then in sqlite. > sqlite stores the dates like: 2011-04-14 22:52:52.758612 > and mysql stores the date like: 2011-04-14 22:52:52 (possible rounded) > When I output the date with json formatting, it''s returned as > 2011-04-14T22:52:52Z regardless of the underlaying db. But in another part > of my application I request all items with a date newer then the above. > However since "2011-04-14 22:52:52.758612" is bigger then "2011-04-14 > 22:52:52" I get the same item again when I query against sqlite (or > postgresql actually). > In my model I have the following scope defined: scope :since, lambda {|time| > where("updated_at > ?", time) } > which I''m using for getting all news items since a current date.Are you saying that if you fetch a record and then ask for records where created_at is greater than that records created_at (so no messing with json in between) that you get the same record again. Or using your scope record1 = Model.find( some conditions ) records = Model.since( record1.created_at ) that you get record1 again? 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 Fri, Apr 15, 2011 at 1:26 PM, Colin Law <clanlaw-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote:> On 15 April 2011 00:07, Seb <sebastianthegreatful-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> created_at is stored differently in mysql then in sqlite. >> sqlite stores the dates like: 2011-04-14 22:52:52.758612 >> and mysql stores the date like: 2011-04-14 22:52:52 (possible rounded) >> When I output the date with json formatting, it''s returned as >> 2011-04-14T22:52:52Z regardless of the underlaying db. But in another part >> of my application I request all items with a date newer then the above. >> However since "2011-04-14 22:52:52.758612" is bigger then "2011-04-14 >> 22:52:52" I get the same item again when I query against sqlite (or >> postgresql actually). >> In my model I have the following scope defined: scope :since, lambda {|time| >> where("updated_at > ?", time) } >> which I''m using for getting all news items since a current date. > > Are you saying that if you fetch a record and then ask for records > where created_at is greater than that records created_at (so no > messing with json in between) that you get the same record again. Or > using your scope > record1 = Model.find( some conditions ) > records = Model.since( record1.created_at ) > that you get record1 again? >Yes, since record1.created_at returns the seconds without decimals. In sqlite: sqlite> select * from news; 1|shalala|sss|2011-04-14 22:52:52.758612|2011-04-14 22:52:52.758612||||1 But in rails the same record is returned as: irb(main):001:0> News.first.created_at => Thu, 14 Apr 2011 22:52:52 UTC +00:00 So if I query for records created after 2011-04-14 22:52:52 I get the same record again. Mysql uses less decimals then pgsql and sqlite. So with mysql it isnt a problem. But I use pgsql in production. -- 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 15 April 2011 15:49, Sebastian <sebastianthegreatful-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On Fri, Apr 15, 2011 at 1:26 PM, Colin Law <clanlaw-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote: >> On 15 April 2011 00:07, Seb <sebastianthegreatful-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >>> created_at is stored differently in mysql then in sqlite. >>> sqlite stores the dates like: 2011-04-14 22:52:52.758612 >>> and mysql stores the date like: 2011-04-14 22:52:52 (possible rounded) >>> When I output the date with json formatting, it''s returned as >>> 2011-04-14T22:52:52Z regardless of the underlaying db. But in another part >>> of my application I request all items with a date newer then the above. >>> However since "2011-04-14 22:52:52.758612" is bigger then "2011-04-14 >>> 22:52:52" I get the same item again when I query against sqlite (or >>> postgresql actually). >>> In my model I have the following scope defined: scope :since, lambda {|time| >>> where("updated_at > ?", time) } >>> which I''m using for getting all news items since a current date. >> >> Are you saying that if you fetch a record and then ask for records >> where created_at is greater than that records created_at (so no >> messing with json in between) that you get the same record again. Or >> using your scope >> record1 = Model.find( some conditions ) >> records = Model.since( record1.created_at ) >> that you get record1 again? >> > > Yes, since record1.created_at returns the seconds without decimals.Can you confirm that you have you tried exactly what I have suggested? Note that the Time class does allow for fractions of a second.> > In sqlite: > > sqlite> select * from news; > 1|shalala|sss|2011-04-14 22:52:52.758612|2011-04-14 22:52:52.758612||||1 > > But in rails the same record is returned as: > irb(main):001:0> News.first.created_at > => Thu, 14 Apr 2011 22:52:52 UTC +00:00All that shows is that it is displayed without fractions when using the default format. It does not prove that created_at does not include seconds.> > So if I query for records created after 2011-04-14 22:52:52 I get the > same record again.Querying for records after 2011-04-14 22:52:52 is not necessarily the same as querying for records after record.created_at. I am not saying you are wrong, as I am unable to test it myself easily. Just making sure that what is happening is clear. If Rails writes fractions of a second to the mysql db but does not read them back into created_at then I would say that this is a bug. According the docs for Time.strftime one should be able to display the milliseconds of a time using %L, [1], however in the console I get ruby-1.8.7-p302 > Time.now.strftime("%S.%L") => "02.%L" Is %L a Ruby 1.9 enhancement? Colin [1] http://www.ruby-doc.org/core/classes/Time.html#M000392 -- 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 15 April 2011 16:19, Colin Law <clanlaw-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote:> On 15 April 2011 15:49, Sebastian <sebastianthegreatful-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> On Fri, Apr 15, 2011 at 1:26 PM, Colin Law <clanlaw-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote: >>> On 15 April 2011 00:07, Seb <sebastianthegreatful-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >>>> created_at is stored differently in mysql then in sqlite. >>>> sqlite stores the dates like: 2011-04-14 22:52:52.758612 >>>> and mysql stores the date like: 2011-04-14 22:52:52 (possible rounded) >>>> When I output the date with json formatting, it''s returned as >>>> 2011-04-14T22:52:52Z regardless of the underlaying db. But in another part >>>> of my application I request all items with a date newer then the above. >>>> However since "2011-04-14 22:52:52.758612" is bigger then "2011-04-14 >>>> 22:52:52" I get the same item again when I query against sqlite (or >>>> postgresql actually). >>>> In my model I have the following scope defined: scope :since, lambda {|time| >>>> where("updated_at > ?", time) } >>>> which I''m using for getting all news items since a current date. >>> >>> Are you saying that if you fetch a record and then ask for records >>> where created_at is greater than that records created_at (so no >>> messing with json in between) that you get the same record again. Or >>> using your scope >>> record1 = Model.find( some conditions ) >>> records = Model.since( record1.created_at ) >>> that you get record1 again? >>> >> >> Yes, since record1.created_at returns the seconds without decimals. > > Can you confirm that you have you tried exactly what I have suggested? > Note that the Time class does allow for fractions of a second. > >> >> In sqlite: >> >> sqlite> select * from news; >> 1|shalala|sss|2011-04-14 22:52:52.758612|2011-04-14 22:52:52.758612||||1 >> >> But in rails the same record is returned as: >> irb(main):001:0> News.first.created_at >> => Thu, 14 Apr 2011 22:52:52 UTC +00:00 > > All that shows is that it is displayed without fractions when using > the default format. It does not prove that created_at does not > include seconds. > >> >> So if I query for records created after 2011-04-14 22:52:52 I get the >> same record again. > > Querying for records after 2011-04-14 22:52:52 is not necessarily the > same as querying for records after record.created_at. I am not saying > you are wrong, as I am unable to test it myself easily. Just making > sure that what is happening is clear. If Rails writes fractions of a > second to the mysql db but does not read them back into created_at > then I would say that this is a bug. > > According the docs for Time.strftime one should be able to display the > milliseconds of a time using %L, [1], however in the console I get > ruby-1.8.7-p302 > Time.now.strftime("%S.%L") > => "02.%L" > Is %L a Ruby 1.9 enhancement?Answering my own question, yes this appears to be a Ruby 1.9 enhancement. If you are using 1.9 then what happens if in the console you do record.created_at.strftime(%H:%M:%S.%L") 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 Fri, Apr 15, 2011 at 5:34 PM, Colin Law <clanlaw-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote:> On 15 April 2011 16:19, Colin Law <clanlaw-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote: >> On 15 April 2011 15:49, Sebastian <sebastianthegreatful-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >>> On Fri, Apr 15, 2011 at 1:26 PM, Colin Law <clanlaw-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote: >>>> On 15 April 2011 00:07, Seb <sebastianthegreatful-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >>>>> created_at is stored differently in mysql then in sqlite. >>>>> sqlite stores the dates like: 2011-04-14 22:52:52.758612 >>>>> and mysql stores the date like: 2011-04-14 22:52:52 (possible rounded) >>>>> When I output the date with json formatting, it''s returned as >>>>> 2011-04-14T22:52:52Z regardless of the underlaying db. But in another part >>>>> of my application I request all items with a date newer then the above. >>>>> However since "2011-04-14 22:52:52.758612" is bigger then "2011-04-14 >>>>> 22:52:52" I get the same item again when I query against sqlite (or >>>>> postgresql actually). >>>>> In my model I have the following scope defined: scope :since, lambda {|time| >>>>> where("updated_at > ?", time) } >>>>> which I''m using for getting all news items since a current date. >>>> >>>> Are you saying that if you fetch a record and then ask for records >>>> where created_at is greater than that records created_at (so no >>>> messing with json in between) that you get the same record again. Or >>>> using your scope >>>> record1 = Model.find( some conditions ) >>>> records = Model.since( record1.created_at ) >>>> that you get record1 again? >>>> >>> >>> Yes, since record1.created_at returns the seconds without decimals. >> >> Can you confirm that you have you tried exactly what I have suggested? >> Note that the Time class does allow for fractions of a second. >> >>> >>> In sqlite: >>> >>> sqlite> select * from news; >>> 1|shalala|sss|2011-04-14 22:52:52.758612|2011-04-14 22:52:52.758612||||1 >>> >>> But in rails the same record is returned as: >>> irb(main):001:0> News.first.created_at >>> => Thu, 14 Apr 2011 22:52:52 UTC +00:00 >> >> All that shows is that it is displayed without fractions when using >> the default format. It does not prove that created_at does not >> include seconds. >> >>> >>> So if I query for records created after 2011-04-14 22:52:52 I get the >>> same record again. >> >> Querying for records after 2011-04-14 22:52:52 is not necessarily the >> same as querying for records after record.created_at. I am not saying >> you are wrong, as I am unable to test it myself easily. Just making >> sure that what is happening is clear. If Rails writes fractions of a >> second to the mysql db but does not read them back into created_at >> then I would say that this is a bug. >> >> According the docs for Time.strftime one should be able to display the >> milliseconds of a time using %L, [1], however in the console I get >> ruby-1.8.7-p302 > Time.now.strftime("%S.%L") >> => "02.%L" >> Is %L a Ruby 1.9 enhancement? > > Answering my own question, yes this appears to be a Ruby 1.9 > enhancement. If you are using 1.9 then what happens if in the console > you do > record.created_at.strftime(%H:%M:%S.%L") >irb(main):001:0> News.find(1).created_at.strftime("%H:%M:%S.%L") => "22:52:52.758" irb(main):002:0> News.find(1).created_at.strftime("%H:%M:%S.%N") => "22:52:52.758612000" %N or %6N seems to return the desired amount of decimals. But since my output is in json I need to do something like the below: format.json { render :json => @news.map! { |n| n.created_at.strftime("%Y:%m:%d %H:%M:%S.%6N") } } But that only returns the created_at attributes. I need the rest of my news data as well. -- 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 16 April 2011 00:55, Sebastian <sebastianthegreatful-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On Fri, Apr 15, 2011 at 5:34 PM, Colin Law <clanlaw-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote: >> On 15 April 2011 16:19, Colin Law <clanlaw-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote: >> ... >> Answering my own question, yes this appears to be a Ruby 1.9 >> enhancement. If you are using 1.9 then what happens if in the console >> you do >> record.created_at.strftime(%H:%M:%S.%L") >> > > irb(main):001:0> News.find(1).created_at.strftime("%H:%M:%S.%L") > => "22:52:52.758" > irb(main):002:0> News.find(1).created_at.strftime("%H:%M:%S.%N") > => "22:52:52.758612000" > > %N or %6N seems to return the desired amount of decimals. But since my > output is in json I need to do something like the below: > > format.json { render :json => @news.map! { |n| > n.created_at.strftime("%Y:%m:%d %H:%M:%S.%6N") } } > > But that only returns the created_at attributes. I need the rest of my > news data as well.So is it correct that your problem has now resolved to a json issue? If so then if you need more help I suggest a new thread. 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 Sat, Apr 16, 2011 at 9:52 AM, Colin Law <clanlaw-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote:> On 16 April 2011 00:55, Sebastian <sebastianthegreatful-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> On Fri, Apr 15, 2011 at 5:34 PM, Colin Law <clanlaw-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote: >>> On 15 April 2011 16:19, Colin Law <clanlaw-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote: >>> ... >>> Answering my own question, yes this appears to be a Ruby 1.9 >>> enhancement. If you are using 1.9 then what happens if in the console >>> you do >>> record.created_at.strftime(%H:%M:%S.%L") >>> >> >> irb(main):001:0> News.find(1).created_at.strftime("%H:%M:%S.%L") >> => "22:52:52.758" >> irb(main):002:0> News.find(1).created_at.strftime("%H:%M:%S.%N") >> => "22:52:52.758612000" >> >> %N or %6N seems to return the desired amount of decimals. But since my >> output is in json I need to do something like the below: >> >> format.json { render :json => @news.map! { |n| >> n.created_at.strftime("%Y:%m:%d %H:%M:%S.%6N") } } >> >> But that only returns the created_at attributes. I need the rest of my >> news data as well. > > So is it correct that your problem has now resolved to a json issue? > If so then if you need more help I suggest a new thread. >Yes, indeed. Thanks for the support, greatly appreciated. -- 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.