Dates stored as UNIX timestamps

Rails
1

Dear list

I am working with a legacy MySQL database which has dates stored as
UNIX timestamps. Ideally, I would like to have the dates converted to
proper Ruby objects when fetched from the database, and converted to
UNIX timestamps when saved to the database.

What would be the proper way to accomodate this?

Best regards

Christian

2

On 15 Aug 2008, at 16:47, Christian Rishøj wrote:

something like

def some_date
Time.at(self[:some_date])
end

def some_date= value
self[:some_date]= value.to_i
end

3

On Aug 15, 6:17 pm, Frederick C. [email protected]
wrote:

something like

def some_date
Time.at(self[:some_date])
end

def some_date= value
self[:some_date]= value.to_i
end

This provides only rudimentary support for UNIX timestamps in the
database.

For example, it breaks when I do:

named_scope :recent, lambda { { :conditions => [‘date > ?’,
1.month.ago] } }

The resulting condition on the database becomes:

WHERE (date > ‘2008-07-15 16:27:58’)

…which is not the intention: I want the Ruby time converted to a
UNIX timestamp whenever used in the database. I suspect this needs to
be done on a deeper level, possibly by monkey patching MysqlAdapter
and MysqlColumn.

In [1] Raimonds Simanovskis has a modified Oracle ConnectionAdapter
which emulates booleans stored in the database as NUMBER(1). Also, he
catches field names ending in “name” and let’s them
emulate :datetime.

I’d like to do the same with the MysqlAdapter, but I am not quite sure
what is the best way to do it.

I suppose I’d have to let MysqlColumn#simplified_type(field_type)
catch the integer fields I’d like to emulate :datetime.

But where do I perform the actual conversion? One options is to
override MysqlAdapter#select_rows – but it seems a little tedious.

Also, I need to hook in somewhere to perform the conversion the other
way – from Ruby times to UNIX timestamps in the database.

Any clues would be appreciated!

Best regards

Christian

[1]
http://blog.rayapps.com/2007/11/16/some-issues-with-oracle-views-as-activerecord-source/

4

On 15 Aug 2008, at 17:52, Christian wrote:

1.month.ago] } }

The resulting condition on the database becomes:

WHERE (date > ‘2008-07-15 16:27:58’)

I think you could do that by overriding the quote method on your
database adapter.

emulate :datetime.

I’d like to do the same with the MysqlAdapter, but I am not quite sure
what is the best way to do it.

I suppose I’d have to let MysqlColumn#simplified_type(field_type)
catch the integer fields I’d like to emulate :datetime.

But where do I perform the actual conversion? One options is to
override MysqlAdapter#select_rows – but it seems a little tedious.

The conversion there happens in the type_cast method in
schema_definitions.rb

Fred

5

On Aug 15, 7:26 pm, Frederick C. [email protected]
wrote:

database adapter.

I suppose I’d have to let MysqlColumn#simplified_type(field_type)
catch the integer fields I’d like to emulate :datetime.

But where do I perform the actual conversion? One options is to
override MysqlAdapter#select_rows – but it seems a little tedious.

The conversion there happens in the type_cast method in
schema_definitions.rb

Indeed it does. Thank you!

Here’s a quick implementation:

module ActiveRecord
module ConnectionAdapters

class MysqlColumn

  def is_unix_timestamp?(name, field_type)
    field_type.downcase.index("int") && name =~ /(^|_)date(_|$)/
  end

  def simplified_type_with_unix_timestamp(field_type)
    if is_unix_timestamp?(name, field_type)
      :datetime
    else
      simplified_type_without_unix_timestamp(field_type)
    end
  end
  alias_method_chain :simplified_type, :unix_timestamp

  def type_cast_with_unix_timestamp(value)
    if is_unix_timestamp?(name, sql_type)
      Time.at(value.to_i)
    else
      type_cast_without_unix_timestamp(value)
    end
  end
  alias_method_chain :type_cast, :unix_timestamp

end

class MysqlAdapter

  def quote_with_unix_timestamp(value, column = nil)
    if value.kind_of?(Time)
      value.to_i
    else
      quote_without_unix_timestamp(value, column)
    end
  end
  alias_method_chain :quote, :unix_timestamp

end

end
end

All Ruby times are quoted as UNIX timestamps when quering the
database, and integer fields in the databse are converted to Ruby
times if they contain “date”.

Christian