Dates stored as UNIX timestamps

rubyonrails-talk
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

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

3

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] Some issues with Oracle views as ActiveRecord source

4

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

5

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