Different date format in sqlite and mysql

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

On 15 April 2011 00:07, Seb [email protected] wrote:

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

On Fri, Apr 15, 2011 at 1:26 PM, Colin L. [email protected]
wrote:

In my model I have the following scope defined:scope :since, lambda {|time|

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.

On 15 April 2011 15:49, Sebastian [email protected]
wrote:

postgresql actually).
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

[1] http://www.ruby-doc.org/core/classes/Time.html#M000392

On 15 April 2011 16:19, Colin L. [email protected] wrote:

22:52:52" I get the same item again when I query against sqlite (or
records = Model.since( record1.created_at )

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

On Fri, Apr 15, 2011 at 5:34 PM, Colin L. [email protected]
wrote:

However since “2011-04-14 22:52:52.758612” is bigger then “2011-04-14
record1 = Model.find( some conditions )
In sqlite:
include seconds.
then I would say that this is a bug.
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.

On Sat, Apr 16, 2011 at 9:52 AM, Colin L. [email protected]
wrote:

irb(main):001:0> News.find(1).created_at.strftime("%H:%M:%S.%L")
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.

On 16 April 2011 00:55, Sebastian [email protected]
wrote:

=> “22:52:52.758”
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

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs