1

Anyone have an authoritative answer for which is more performant?

GROUP BY 
    YEAR(FROM_UNIXTIME(col))

/* or */

GROUP BY
    EXTRACT(YEAR FROM FROM_UNIXTIME(col))

I found this SO post, which was asking a more general question, with performance as an aside, but didn't find anything authoritative, only anecdotal.

Community
  • 1
  • 1
Mike Purcell
  • 19,847
  • 10
  • 52
  • 89
  • They're not equivalent. What's the type of the column? `YEAR(FROM_UNIXTIME(col))` would work on a numeric column containing a UNIX time; `EXTRACT(YEAR FROM col)` would work on a DATE or DATETIME column. –  Dec 15 '14 at 21:23
  • It's an bigint column storing a unix timestamp. I see what you are saying, per the mysql docs: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_extract – Mike Purcell Dec 15 '14 at 21:26

2 Answers2

0

MySQL treats YEAR(x) and EXTRACT(YEAR FROM x) identically. There is absolutely no difference between the two, in performance or otherwise.

I tested this on a table of several million rows with the queries:

SELECT DISTINCT YEAR(x) FROM tbl

and

SELECT DISTINCT EXTRACT(YEAR from x) FROM tbl

and the results were effectively identical (within 0.5% or so).

  • My only contention to your post, is the excerpt stating that "but extracts parts from the date rather than performing date arithmetic." Under the http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_extract section. I have to believe that the simple string manipulation is faster than actual math computation. – Mike Purcell Dec 15 '14 at 22:06
  • 1
    @MikePurcell: `extract(...)` does not do any "*string manipulation*". It operates on dates, not on strings. –  Dec 15 '14 at 22:40
  • 1
    @MikePurcell Also, that phrase is comparing `EXTRACT()` to `DATE_ADD()` and `DATE_SUB()`. It's not implying anything about `YEAR()`. –  Dec 15 '14 at 22:42
  • @duskwuff: eh? It's not comparing to date_add or date_sub, simply "function uses the same kinds of unit specifiers as", key phrase being "unit specifiers". – Mike Purcell Dec 15 '14 at 22:45
  • @a_horse_with_no_name: You're right, I mis-spoke, from mysql cli it's a datetime data type, when it gets interpreted by script (php) it's cast as a string. . – Mike Purcell Dec 15 '14 at 22:49
0

Extract is useful when you want to extract two or more parts of a date together. This has many uses for specific queries.

eg

SELECT EXTRACT(YEAR_MONTH FROM "2017-06-15 09:34:21");

ans: 201706

SELECT EXTRACT(MINUTE_SECOND FROM "2017-06-15 09:34:21");

ans: 3421.

This can be done directly through extract as there is no specific date time function for this. Extract always returns an integer which makes processing easier.

dhaval patange
  • 23
  • 4