461

In my experience, getting dates/times right when programming is always fraught with danger and difficulity.

Ruby and Rails have always eluded me on this one, if only due to the overwhelming number of options; I never have any idea which I should pick.

When I'm using Rails and looking at ActiveRecord datatypes I can find the following

:datetime, :timestamp, :time, and :date

and have no idea what the differences are or where the gotchas lurk.

What's the difference? What do you use them for?

(P.S. I'm using Rails3)

Arslan Ali
  • 17,418
  • 8
  • 58
  • 76
Nick
  • 4,765
  • 3
  • 17
  • 9
  • I think part of the confusion is also that the `ActiveRecord` types don't correspond to the Ruby types (e.g. `DateTime`) and Rails layers even other types on top (e.g. `TimeWithZone`). – Christopher Oezbek Mar 14 '21 at 12:05

3 Answers3

633

The difference between different date/time formats in ActiveRecord has little to do with Rails and everything to do with whatever database you're using.

Using MySQL as an example (if for no other reason because it's most popular), you have DATE, DATETIME, TIME and TIMESTAMP column data types; just as you have CHAR, VARCHAR, FLOAT and INTEGER.

So, you ask, what's the difference? Well, some of them are self-explanatory. DATE only stores a date, TIME only stores a time of day, while DATETIME stores both.

The difference between DATETIME and TIMESTAMP is a bit more subtle: DATETIME is formatted as YYYY-MM-DD HH:MM:SS. Valid ranges go from the year 1000 to the year 9999 (and everything in between. While TIMESTAMP looks similar when you fetch it from the database, it's really a just a front for a unix timestamp. Its valid range goes from 1970 to 2038. The difference here, aside from the various built-in functions within the database engine, is storage space. Because DATETIME stores every digit in the year, month day, hour, minute and second, it uses up a total of 8 bytes. As TIMESTAMP only stores the number of seconds since 1970-01-01, it uses 4 bytes.

You can read more about the differences between time formats in MySQL here.

In the end, it comes down to what you need your date/time column to do:

  • Do you need to store dates and times before 1970 or after 2038? => Use DATETIME.
  • Do you need to worry about database size and you're within that timerange? => Use TIMESTAMP.
  • Do you only need to store a date? => Use DATE.
  • Do you only need to store a time? => Use TIME.

Having said all of this, Rails actually makes some of these decisions for you. Both :timestamp and :datetime will default to DATETIME, while :date and :time corresponds to DATE and TIME, respectively.

This means that within Rails, you only have to decide whether you need to store date, time or both.

Christopher Oezbek
  • 23,994
  • 6
  • 61
  • 85
vonconrad
  • 25,227
  • 7
  • 68
  • 69
  • 8
    For the record, I prefer absolute timestamps (unix) because the YYYY-MM-DD format is dependent on the time zone used. So the client needs to know the time zone of the server, and needs to do conversions. Seconds-since-1970 absolute timestamps don't have that issue. – n13 Apr 05 '12 at 04:14
  • 37
    @n13 Good point, but not really an issue in Rails, since it converts to UTC before inserting datetimes into the database. – vonconrad Apr 12 '12 at 03:18
  • 16
    This is one of the most helpful posts in the Rails universe. This should be added to the rails guide... – Andrew May 07 '13 at 14:07
  • 6
    MySQL's `TIME` column is not strictly a "time of day", since it accepts hours > 24; it's also usable as "elapsed time". – nickgrim Nov 26 '13 at 10:11
  • 6
    Is this the same in other dbs? e.g. postgres? – Andy Hayden Feb 02 '14 at 21:02
  • In the second-last passage - did you mean that datetime column type, when read from the associated model, will get converted to (Ruby) DateTime? Rails (ActiveRecord) actually uses ActiveSupport::TimeWithZone for this field type. – akurtser May 27 '14 at 11:00
  • And what about ActiveSupport::TimeWithZone, what is that? I need something in the context of postgesql. – Donato Jun 02 '15 at 17:54
  • 1
    `ActiveSupport::TimeWithZone` is a class that acts like the standard ruby `Time` class but can handle any potential time zone unlike Time which only handles UTC and the system timezone. http://api.rubyonrails.org/classes/ActiveSupport/TimeWithZone.html – max Oct 16 '15 at 10:50
  • will UnixTimeStamp be reset to 0000000 after 2038 so they can use `timestamp` again? Or what's the plan? – Saravanabalagi Ramachandran Nov 12 '16 at 11:19
  • @AndyHayden No, it is not. In Postgres, `TIME` is allowed values from 0:00:00 to 24:00:00 (inclusive). – Qqwy Nov 20 '19 at 14:35
36

  1. :datetime (8 bytes)

    • Stores Date and Time formatted YYYY-MM-DD HH:MM:SS
    • Useful for columns like birth_date

  2. :timestamp (4 bytes)

    • Stores number of seconds since 1970-01-01
    • Useful for columns like updated_at, created_at
  3. :date (3 bytes)
    • Stores Date
  4. :time (3 bytes)
    • Stores Time
mingca
  • 542
  • 4
  • 12
3

Here is an awesome and precise explanation I found.

TIMESTAMP used to track changes of records, and update every time when the record is changed. DATETIME used to store specific and static value which is not affected by any changes in records.

TIMESTAMP also affected by different TIME ZONE related setting. DATETIME is constant.

TIMESTAMP internally converted a current time zone to UTC for storage, and during retrieval convert the back to the current time zone. DATETIME can not do this.

TIMESTAMP is 4 bytes and DATETIME is 8 bytes.

TIMESTAMP supported range: ‘1970-01-01 00:00:01′ UTC to ‘2038-01-19 03:14:07′ UTC DATETIME supported range: ‘1000-01-01 00:00:00′ to ‘9999-12-31 23:59:59′

source: https://www.dbrnd.com/2015/09/difference-between-datetime-and-timestamp-in-mysql/#:~:text=DATETIME%20vs%20TIMESTAMP%3A,DATETIME%20is%20constant.

Also...

table with different column "date" types and corresponding rails migration types depending on the database

Community
  • 1
  • 1
Stanislav Modrák
  • 69
  • 4