MySQL Forums
Forum List  »  Newbie

Re: DATETIME vs. INT(11)?
Posted by: yawnmoth
Date: August 17, 2009 10:31AM

Here's the "real" query:

SELECT from_unixtime(purchase_date,'%Y') AS purchase_year,
from_unixtime(purchase_date,'%m') AS purchase_month, COUNT(*) AS
FROM purchases
GROUP BY purchase_year, purchase_month;
Problem is that the query is slow - presumably because from_unixtime() has to be run on every column. On the surface, it seems like month() and year() would be slow for the same reason, but I initially thought that datetime might internally represent dates as three columns and that month(column_name) would just tell MySQL to look at that "hidden" column, specifically. If that's not what it's doing than I'm not really sure I see the point.

Sure, there are lots of functions that you can use with DATETIME and whatnot but you can emulate those functions pretty easily, as is. For example, AVG(DATEDIFF(end, start)) == AVG(end - start) / (60*60*24).

Options: ReplyQuote

Written By
August 14, 2009 10:00AM
August 15, 2009 03:26PM
Re: DATETIME vs. INT(11)?
August 17, 2009 10:31AM
August 17, 2009 08:04PM
August 26, 2009 09:23AM
August 26, 2009 11:30AM
August 26, 2009 11:55AM
August 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.