Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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)

like image 903
Nick Avatar asked Oct 13 '10 21:10

Nick


People also ask

What is the difference between datetime and TIMESTAMP?

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' . The TIMESTAMP data type is used for values that contain both date and time parts.

What is the difference between time and TIMESTAMP?

TIMESTAMP: It is also used for values that contain both date and time parts, and includes the time zone. TIMESTAMP has a range of 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC. TIME: Its values are in HH:MM:SS format (or HHH:MM:SS format for large hours values). TIME values may range from -838:59:59 to 838:59:59 .

Which is better datetime or TIMESTAMP?

TIMESTAMP is four bytes vs eight bytes for DATETIME . Timestamps are also lighter on the database and indexed faster. The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in YYYY-MM-DD HH:MM:SS format.


2 Answers

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.

like image 74
vonconrad Avatar answered Sep 27 '22 01:09

vonconrad


  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
like image 34
mingca Avatar answered Sep 24 '22 01:09

mingca