# Dates stored as UNIX timestamps

**URL:** <https://www.ruby-forum.com/t/dates-stored-as-unix-timestamps/145972>\
**Category:** Rails\
**Created:** [August 15, 2008, 6:15pm UTC](https://www.ruby-forum.com/t/dates-stored-as-unix-timestamps/145972 "2008-08-15T18:15:54Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Christian\_RishSSSSj](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/f1d935/32.png) [@Christian\_RishSSSSj](https://www.ruby-forum.com/u/Christian_RishSSSSj)\
**Post date:** [August 15, 2008, 6:15pm UTC](https://www.ruby-forum.com/t/dates-stored-as-unix-timestamps/145972/1 "2008-08-15T18:15:54Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Christian\_RishSSSSj](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/f1d935/32.png) [@Christian\_RishSSSSj](https://www.ruby-forum.com/u/Christian_RishSSSSj)\
**Post date:** [August 15, 2008, 6:18pm UTC](https://www.ruby-forum.com/t/dates-stored-as-unix-timestamps/145972/2 "2008-08-15T18:18:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Christian\_RishSSSSj](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/f1d935/32.png) [@Christian\_RishSSSSj](https://www.ruby-forum.com/u/Christian_RishSSSSj)\
**Post date:** [August 15, 2008, 6:52pm UTC](https://www.ruby-forum.com/t/dates-stored-as-unix-timestamps/145972/3 "2008-08-15T18:52:49Z")

</div>

On Aug 15, 6:17 pm, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
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/](http://blog.rayapps.com/2007/11/16/some-issues-with-oracle-views-as-activerecord-source/)

---

<div class="post-metadata">

**Author:** ![Christian\_RishSSSSj](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/f1d935/32.png) [@Christian\_RishSSSSj](https://www.ruby-forum.com/u/Christian_RishSSSSj)\
**Post date:** [August 15, 2008, 7:27pm UTC](https://www.ruby-forum.com/t/dates-stored-as-unix-timestamps/145972/4 "2008-08-15T19:27:23Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Christian\_RishSSSSj](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/f1d935/32.png) [@Christian\_RishSSSSj](https://www.ruby-forum.com/u/Christian_RishSSSSj)\
**Post date:** [August 16, 2008, 4:15pm UTC](https://www.ruby-forum.com/t/dates-stored-as-unix-timestamps/145972/5 "2008-08-16T16:15:02Z")

</div>

On Aug 15, 7:26 pm, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
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