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

User · Answer

datetime  8 bytes    Stores Date and Time formatted YYYY-MM-DD HH MM SS Useful for columns like birth date   timestamp  4 bytes    Stores number of seconds since 1970-01-01 Useful for columns like updated at  created at   date  3 bytes    Stores Date   time  3 bytes    Stores Time

User · Answer

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

User · Answer

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  quot date quot  types and corresponding rails migration types depending on the database

[ruby-on-rails] In Ruby on Rails, what's the difference between DateTime, Timestamp, Time and Date?

Examples related to ruby-on-rails

Examples related to datetime

Examples related to date

Examples related to time

Examples related to timestamp