MySQL Forums :: Newbie :: DATETIME vs. INT(11)?


Advanced Search

DATETIME vs. INT(11)?
Posted by: yawnmoth ()
Date: August 14, 2009 10:00AM

I'm curious... what's the advantage of using DATETIME over INT(11)?

I ran some tests and near as I can tell, there isn't one. Indeed, it seems to me that there's actually a bit of a disadvantage.

The first test uses the following schema:

CREATE TABLE test (
   test DATETIME
);
INSERT INTO test VALUES ('2007-12-31 23:59:59'),('2010-11-30 23:59:59'),('2003-12-31 01:02:03');
I then run the following query:

EXPLAIN SELECT YEAR(test) AS `year`, MONTH(test) AS `month`
FROM test
GROUP BY `year`, `month`
Here's what I get:

id = 1
select_type = SIMPLE
table = test
type = ALL
rows = 3
Extra = Using temporary; Using filesort
Curious to see how it'll perform if test is a key, I do the following:

ALTER TABLE test ADD KEY(test);

Here's what I get:
id = 1
select_type = SIMPLE
table = test
type = index
key = test
key_len = 9
rows = 3
Extra = Using index; Using tempory; Using filesort

Now I try it with INT(11):
CREATE TABLE test2 (
   test INT(11)
);
INSERT INTO test2 VALUES (UNIX_TIMESTAMP('2007-12-31 23:59:59')),(UNIX_TIMESTAMP('2010-11-30 23:59:59')),(UNIX_TIMESTAMP('2003-12-31 01:02:03'));
I then run the following query:

EXPLAIN SELECT from_unixtime(test, '%Y') AS `year`, from_unixtime(test, '%m') AS `month`
FROM test2
GROUP BY `year`, `month`
Here's what I get:

id = 1
select_type = SIMPLE
table = test2
type = ALL
rows = 3
Extra = Using temporary; Using filesort
So, there's no difference between this and the last one. Same thing happens if I make test a key. Only difference then is that the key_len is 5 and not 9.

So I'm left to wonder... what, really, is the point to DATETIME? It doesn't seem to offer any advantages over INT(11) and indeed, using it yields a larger key_len, which is a clear disadvantage of it.

Options: ReplyQuote


Subject Written By Posted
DATETIME vs. INT(11)? yawnmoth 08/14/2009 10:00AM
Re: DATETIME vs. INT(11)? Rick James 08/15/2009 03:26PM
Re: DATETIME vs. INT(11)? yawnmoth 08/17/2009 10:31AM
Re: DATETIME vs. INT(11)? Rick James 08/17/2009 08:04PM
Re: DATETIME vs. INT(11)? yawnmoth 08/26/2009 09:23AM
Re: DATETIME vs. INT(11)? Rick James 08/26/2009 11:30AM
Re: DATETIME vs. INT(11)? yawnmoth 08/26/2009 11:55AM
Re: DATETIME vs. INT(11)? Rick James 08/26/2009 11:34PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.