Dates stored as UNIX timestamps

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

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?

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/

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')

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

...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.

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

Fred

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