StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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.</p> <p>Using MySQL as an example (if for no other reason because it's most popular), you have <code>DATE</code>, <code>DATETIME</code>, <code>TIME</code> and <code>TIMESTAMP</code> column data types; just as you have <code>CHAR</code>, <code>VARCHAR</code>, <code>FLOAT</code> and <code>INTEGER</code>.</p> <p>So, you ask, what's the difference? Well, some of them are self-explanatory. <code>DATE</code> only stores a date, <code>TIME</code> only stores a time of day, while <code>DATETIME</code> stores both.</p> <p>The difference between <code>DATETIME</code> and <code>TIMESTAMP</code> is a bit more subtle: <code>DATETIME</code> is formatted as <code>YYYY-MM-DD HH:MM:SS</code>. Valid ranges go from the year 1000 to the year 9999 (and everything in between. While <code>TIMESTAMP</code> <em>looks</em> similar when you fetch it from the database, it's really a just a front for a <a href="http://en.wikipedia.org/wiki/Unix_time" rel="nofollow noreferrer">unix timestamp</a>. 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 <code>DATETIME</code> stores every digit in the year, month day, hour, minute and second, it uses up a total of 8 bytes. As <code>TIMESTAMP</code> only stores the number of seconds since 1970-01-01, it uses 4 bytes.</p> <p>You can read more about the differences between time formats in MySQL <a href="http://dev.mysql.com/doc/refman/5.1/en/date-and-time-types.html" rel="nofollow noreferrer">here</a>.</p> <p>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 <code>DATETIME</code>. Do you need to worry about database size and you're within that timerange? Use <code>TIMESTAMP</code>. Do you only need to store a date? Use <code>DATE</code>. Do you only need to store a time? Use <code>TIME</code>.</p> <p>Having said all of this, <strong>Rails actually makes some of these decisions for you</strong>. Both <code>:timestamp</code> and <code>:datetime</code> will default to <code>DATETIME</code>, while <code>:date</code> and <code>:time</code> corresponds to <code>DATE</code> and <code>TIME</code>, respectively.</p> <p>This means that within Rails, you only have to decide whether you need to store date, time or both.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload