Should Dates be Stored as a Datetime or as an int in SQL?

Question

I store all my dates for post in my forum in datetime (0000-00-00 00:00:00). I see that phpBB, punBB and all popular forums store dates in int?

What is better?

Answer 1

If you store the dates as INT then every application or tool that ever connects to your database has to know how to convert that INT into something meaningful. I would suggest sticking with the datatypes that fit the data unless your specific RDBMS has serious flaws with a particular datatype.

One more issue to consider... if you store them as INT then you will also lose access to many date-specific functions and would have to write them yourself. For example, returning the day name (Monday, Tuesday, etc.) of a particular date.

Answer 2

I'm not sure if there is a "better" answer. But I would recommend the datetime, because if you stored them as an int, you might be in trouble with the Year 2038 issue.

Answer 3

Most DBMSs nowadays let you do more funky queries against real datetime fields. With the functions available for date manipulation and querying it is hard to justify the use of integers.

I would imagine that most of the BB systems use INTs as they are easier to implement across several database engines, and if you only care about the date part, and not the time part you might get marginally better performance from INTs as opposed to datetimes (which are normally 8 byte floats).

Personally I prefer to store dates in datetime fields, because I hardly ever migrate from one DB platform to another, and the benefits are there to ask the DB for the last Friday of every month for example.

Answer 4

The best answer is "it depends". Given no other information, I would say datetime is better. I see those forums' implementations as a failure of recognizing better, more mature data types. You'd have to come up with a really good reason why you would want ints over dates. Maybe they have and I just don't know.

Answer 5

I use DATETIME for all of my time fields (and, using MySQL, I always, always avoid TIMESTAMP). One trick I use, though, is to set the column as NULL DEFAULT NULL. This way, I don't ever need to worry about or check for "0000-00-00 00:00:00" for instances where I consider the date empty or blank; I only check for IS NULL.

The only reason I can think of that people, in the past, may have considered using INT for their date columns is because at one time, DATETIME (and DATE and TIME) were implemented as strings internally by MySQL. In that context, DATETIME fields would be much larger than INT fields, and so if space is a concern I could see that decision being made. These days, that isn't the case anymore (I would say MySQL 4.x and on), and there is no good reason not to choose DATETIME anymore.

Answer 6

FYI, If you go with int/timestamp then you can still get all the functionality your RDBMS can offer for Date/Time. In MySQL for example, there is FROM_UNIXTIME(timestamp) which takes an integer timestamp and returns a DATETIME which you can then use as you please.

For a lengthy discussion, no where else is better than the fine folks at the Drupal project: http://groups.drupal.org/node/731

Hope this helps.

Answer 7

Also, there is the problem with pre-epoch dates. Keeping something like member birth dates in INT is difficult, because some members might be born before January 1, 1970.

Answer 8

I work with a data warehouse that stores dates as an int in e.g. 20090331 format. Then there's a table dedicated to converting that int to any date-related information, including datetime representation. This provides a lot of flexibility, and lets us add extra information such as quarter number, holiday designation, etc. If you need anything but the integer date, just join to the date table.

select MyTable.Stuff,DimDate.AsDateTime,DimDate.BusinessQuarter,DimDate.IsHoliday from MyTable
inner join DimDate on MyTable.DateKey = DimDate.DateKey

Answer 9

SQL 2008 introduced a "date" datatype, that is smaller than a full "datetime" field, if you don't need the time portion (which, if you are thinking about using an INT, it sounds like you don't need anyway).

See this article for details.

Answer 10

A datetime, internally, is an int, a count of seconds or milliseconds from some epoch, often the linux epoch of midnight 1 January 1970.

But it allows you all sorts of wonderful date functions to add, subtract, and decompose intervals of time, which you can't do with an int (without re-writing all those functions yourself).

So you're losing nothing, and gaining a lot as others have answered, by using a date/datetime instead of an int.

Answer 11

I want to follow up on tpdi's answer and describe my experience having done it both ways several times.

When an integer has been used, it's been done with the convention described by tpdi - a given number of seconds past some point in time around 1970.

Back when a lot of production software was at least partially written in C, this mode was the (UNIX-style) standard and a reasonable number of functions were provided for mapping and date calculations.

One issue not discussed much above is that interpolations by hour, minute, and second are not equally well-handled by all languages and DBMS libraries. Integer-based dates handle this more nicely without worrying about rounding errors - at least as long as you don't need a resolution less than 1 second. It's also helpful to not need to deal with dates before 0, although it can be handled without too much trouble with negative integers.

The final benefit can be that most languages/DBMSs have a feature for handling this convention, which makes it easier to use multiple languages and DBMS products with fewer compatibility issues.

In some reasonable cases it's as @tpdi desribes; but it can also be turned on its head - you can lose precision and cross-language compatibility through libraries that handle integer datetimes if they fit your context.

Answer 12

I would use Datetime unless there are other dates and possibly times that may be better stored as int.