I just want a good time...

August 20, 2007 9:34 AM

DATETIME, TIMESTAMP, TIME, DATE. Which should I use for general-purpose date storage in a recent MySQL version?

So I'm building a calendar among other things... and I'd like to store the date that events start. It's in PHP and CodeIgniter. I'd like to store dates in something reasonably mainstream and efficient. I've seen both Timestamp and Datetime used, rarely Time or Date. What are the +/-s for these guys? Or, at least, which you would use?

Thanks, this is a total mystery to me.

posted by tmcw to computers & internet (16 comments total)

Use datetime or an integer field containing a Unix timestamp (MySQL timestamp is another thing altogether). Store the data as UTC and do any timezone trickery when retrieving/displaying; if MySQL isn't set to use UTC by default, I think you need to use a SET time_zone='+0:00' query.

If using datetime, you can convert to/from Unix timestamps within the queries if that's easiest, using UNIX_TIMESTAMP() and FROM_UNIXTIME().
posted by malevolent at 10:21 AM on August 20, 2007

They explain the different data types in detail here...
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html

The gist is that these specify a moment in time... like the time at which a forum post was made. If you just need a date, such as the last day of school, you use DATE... if you just need a TIME, such what time you wake up, you use time... and if your need requires both, such as for an appointment, you use DATETIME.

DATETIME is YYYY-MM-DD HH:MM:SS
DATE is YYYY-MM-DD
TIMESTAMP is HH:MM:SS

But TIME is used for elapsed time or an interval between two events...

TIME is HH:MM:SS
posted by vitrum at 10:22 AM on August 20, 2007

I'm personally a fan of UNIX timestamps, which are essentially the number of seconds elapsed since midnight (UTC) of January 1, 1970. Simple and straightforward. They're also easily integrated with the PHP date() and mktime() functions.

As an aside, most people seem to represent them in MySQL as int(10).
posted by scoria at 10:25 AM on August 20, 2007

Geez... pardon my horrific typos... someone interrupted me in the middle of my post :-(

The gist is that these specify a moment in time... like the moment at which a forum post was made. If you just need a date, such as the last day of school, you use DATE... if you just need a time, such what time you wake up, you use TIMESTAMP... and if your need requires both, such as for an appointment, you use DATETIME.
posted by vitrum at 10:27 AM on August 20, 2007

DATE stores only the date and requires 3 bytes of storage. TIME is the same idea an storage requirement but records only the time. DATETIME and TIMESTAMP are essentially the same, except that TIMESTAMP can be set to default to the server date and time (handy for INSERT's) and DATETIME requires 8 bytes whereas TIMESTAMP only requires 4.

Be careful with this as it does, by default I believe, take into account the server's timezone and apply that against UTC. So, if you moved the server across timezones, the values might no longer be "correct."

Since you aren't recording timestamps (but rather scheduled events/appointments), then going with either DATETIME or TIMESTAMP should be fine, the smaller storage requirement of TIMESTAMP notwithstanding.

See: [DATETIME], [TIMESTAMP] and [Date Type Storage] articles for more info.
posted by basicchannel at 10:30 AM on August 20, 2007

vitrum: TIMESTAMP's format is actually: YYYY-MM-DD HH:MM:SS
posted by basicchannel at 10:33 AM on August 20, 2007

Hmm, it sounds like DATETIME is a likely choice. The catch for storing a unix timestamp as int(10) is that I couldn't use MySQL date functions for it, right?
posted by tmcw at 10:35 AM on August 20, 2007

DATETIME.

You could use the functions, you just have to convert it first. And if you're going to do that, you might as well just use DATETIME.
posted by jeffamaphone at 10:51 AM on August 20, 2007

Huh, I've always used timestamp, with no problems. The truth is, even if you do move servers you're fine, because you'll probably be inserting dates and times as entered by a form, right? So if you want an event to be at 3:40 PM and then the server moves to Australia, the event is still at 3:40 PM. You would only want to adjust for timezones if you're creating a calendar application that should adjust to different timezones, so that someone can punch in an event beginning at 3:00 PM GMT and someone in New York will have it formatted out for them as 10:00 AM EST.

Of course, I guess it's just a saving of 4 bytes, so no biggie. Also, it's possible that more DB servers use DATETIME than TIMESTAMP, so if you set them up as datetimes it will be easier to port the database over if you ever had to do that for some strange reason.
posted by Deathalicious at 11:11 AM on August 20, 2007

There seems to be a lot of precedent and preference for DATETIME. Ruby on Rails, for example, uses it somewhat exclusively for columns representing times.
posted by wackybrit at 11:13 AM on August 20, 2007

I generally avoid TIMESTAMP for the following reasons:

1) It doesn't allow dates before 1970 or after 2038
2) If you modify a record with a timestamp column, the timestamp gets updated to the current time. Unless you are using "TIMESTAMP" as a "last modified" column, this is rarely what you want.
posted by justkevin at 2:00 PM on August 20, 2007

Also some databases, I'm pretty sure, allow you to store timezone data in your record. In Postgresql you can do 'timestamp with timezone' which stores a timezone.
posted by delmoi at 2:15 PM on August 20, 2007

Datetime is the standard format.
posted by Maia at 3:01 PM on August 20, 2007

I'm working on a similar project (also in PHP and CodeIgniter), with a lot of 19th-century events. I'll know dates for each event, but times for only a few of the events. I was planning on using DATE for the dates and TIME (with a default null value) for the times. Would DATETIME give me any advantages, given that I won't have times for most of the events?
posted by kirkaracha at 3:11 PM on August 20, 2007

Well, I went with Datetime. it was fairly quick to setup the interface, but now I discover that my MySQL server is in PDT instead of eastern, so I'm going to need to work around that somehow.
posted by tmcw at 7:27 AM on August 21, 2007

Hey kirkaracha, see this discussion about timezones, etc. It looks like all of CI's date helpers are written with unix timestamps in mind... but that's out of the question for you, I guess, since your events are pre-1970.
posted by tmcw at 10:35 AM on August 21, 2007