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

user_231022 0
Viewed 136585

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)

timestamp time date datetime ruby-on-rails
3 Answers

  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
user_67532390

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

user_123263570
Related