In Ruby on Rails, what's the difference between DateTime, Timestamp, Time and Date?

Question

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)

Solution 1

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.

Solution 2

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

Solution 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