Inconsistent(?) datetime and timestamp handling

rubyonrails-core
1

Hi,

I noticed that datetimes and timestamps are handled inconsistently between databases. Mysql treats them as datetime[1]. Postgres treats them as timestamp[2]. Sqlite treats them as datetime[3].

I read through the documentation around specifics of datetime and timestamps in various databases and was wondering if there’s a database specific nuance that I do not understand or something else I’m missing. Could someone throw some light at this?

Thanks!

[1] https://github.com/rails/rails/blob/v4.0.0.rc2/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb#L146-L147

[2] https://github.com/rails/rails/blob/v4.0.0.rc2/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L401-L402

[3] https://github.com/rails/rails/blob/v4.0.0.rc2/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb#L205-L206

  • Ketan
2

The best type to use to store DateTime objects is database-dependent:

- in Mysql, there are both TIMESTAMP and DATETIME column types, but TIMESTAMP is limited to the 32-bit UNIX epoch (1970-01-01 to 2038-01-19) while DATETIME accepts values between 1000-01-01 and 9999-12-31.

- in Postgres, there is only TIMESTAMP, which accepts values between -4713-01-01 and 294276-01-01 (or so...)

- SQLite doesn't exactly support a native DATETIME type, but it stores the values as strings. DATETIME is preferred (best I can tell) over TIMESTAMP because the SQLite gem parses TIMESTAMP columns into Time objects while DATETIMEs become DateTime objects.

--Matt Jones

3

Matt jones wrote:

The best type to use to store DateTime objects is database-dependent:

So Ketan’s finding seems to be correct, and this is something we should fix. I.e., Rails’ concept of timestamps should be database independent, just as its concept of the primary key type is. (?)

4

But that’s exactly what’s already happening. The different databases deal with date-time data in fundamentally different ways under the hood, and Rails can’t change that. But what it can do (and what is happening in the code we’re looking at here) is talk to each database using its own specific vocabulary about date-time data, and then translate that to a single Ruby representation of DateTime that is the same regardless of which database is being used.

The fact that the code in Rails uses different vocabulary to talk to different databases isn’t a problem to be fixed. Rather, it’s the only way to talk to those different databases, and is the right thing to be doing.

Now, if we were discussing a specific bug where some DateTime columns actually behave differently to the programmer using Rails depending on which database is being used, then that would be something worth fixing. It would be evidence that the abstraction is broken in some way. But I haven’t seen anything of that sort in this thread yet.

(An example would be: Matt pointed out that the different databases have different acceptable ranges for their date-time data. This is probably a difference that does leak through to the Rails world. Is that a problem, and is it worth fixing it by sticking to a common range that can be stored in all the databases Rails supports?)

Chris

5

I think you missed the point of my post: the choice of column MUST be database-dependent, since there’s no type that’s consistently available across all DB engines.

Handling these differences in a way that’s transparent to higher levels is one of ActiveRecord’s responsibilities - for another example, note that boolean columns are stored in database-dependent ways: Mysql uses tinyint, SQLite uses a string with ‘t’ or ‘f’, Postgres uses a native boolean type, etc.

For what it’s worth, not even the primary key type is consistent if you go a little farther afield. For instance, the built-in Mysql adapter uses an INTEGER(11) field for primary keys, but the oracle-enhanced adapter uses NUMBER(38) which doesn’t even have the same available range.

–Matt Jones