different date format in sqlite and mysql
Seb
Colin Law
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
Sebastian
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.
Colin Law
> On Fri, Apr 15, 2011 at 1:26 PM, Colin Law <cla...@googlemail.com> wrote:
>> On 15 April 2011 00:07, Seb <sebastiant...@gmail.com> 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?
Colin
Colin Law
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
Sebastian
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.
Colin Law
> On Fri, Apr 15, 2011 at 5:34 PM, Colin Law <cla...@googlemail.com> wrote:
>> On 15 April 2011 16:19, Colin Law <cla...@googlemail.com> 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
Sebastian
Yes, indeed. Thanks for the support, greatly appreciated.