different date format in sqlite and mysql

rubyonrails-talk
1

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

2

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

3

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:

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.

4

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:

> 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] class Time - RDoc Documentation

5

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

6

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.

7

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

8

Yes, indeed. Thanks for the support, greatly appreciated.