MySQL YEAR Function

 MySQL YEAR Function

Summary: in this tutorial, you will learn how to use the MySQL YEAR function to get the year out of a date value.

Introduction to MySQL YEAR function

The YEAR() the function takes a date argument and returns the year of the date. See the syntax of the YEAR() function:

YEAR(date);

The YEAR() the function returns a year value in the range 1000 to 9999. If the date is zero, the YEAR() function returns 0.

The following example returns the year of January 1st 2017 which is 2017.

SELECT YEAR('2017-01-01');

+--------------------+
| YEAR('2017-01-01') |
+--------------------+
|               2017 |
+--------------------+
1 row in set (0.00 sec)

The following statement returns the current year:

SELECT YEAR(NOW());
+-------------+
| YEAR(NOW()) |
+-------------+
|        2017 |
+-------------+
1 row in set (0.00 sec)

In this example, the YEAR() the function returns the year information of the current date and time provided by the NOW() function.

If the date is NULL, the YEAR() function will return NULL as shown in the following example:

SELECT YEAR(NULL);
+------------+
| YEAR(NULL) |
+------------+
|       NULL |
+------------+
1 row in set (0.00 sec)

As mentioned earlier, the YEAR() of zero dates is zero:

 SELECT YEAR('0000-00-00');
+--------------------+
| YEAR('0000-00-00') |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

Let’s see the orders table in the sample database.

The following query uses the YEAR() function to get the number of orders shipped per year.

SELECT 
    YEAR(shippeddate) year, 
    COUNT(ordernumber) orderQty
FROM 
    orders
WHERE
    shippeddate IS NOT NULL
GROUP BY YEAR(shippeddate)
ORDER BY YEAR(shippeddate);

In this example, we use the YEAR() function to extract year information out of the shipped date and use the COUNT() function to count the number of delivered orders. The GROUP BY clause group the number of orders by year.

MySQL YEAR function and indexes

Currently, MySQL does not support function index. It means that the expression YEAR(column) will not leverage the index if the index is available for the column.

We will create a new table named dates for demonstration purposes:

CREATE TABLE dates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    dt DATE
); 

The dt the the the column will store the date data. The following statement creates an index on the dt column of the dates table.

CREATE INDEX idx_td ON dates(dt);

We will insert lots of dates into the dates table. The easiest way is to use a recursive CTE to generate the date series and insert this dating series into the dates table.

The following recursive CTE generates the dates between '1800-01-01' and '2020-12-31':

WITH RECURSIVE dates (dt) AS
(
  SELECT '1800-01-01'
  UNION ALL
  SELECT dt + INTERVAL 1 DAY FROM dates
  WHERE dt + INTERVAL 1 DAY <= '2020-12-31'
)
SELECT dt FROM dates;

To insert this dating series into the dates table, you use the following INSERT statement:

INSERT INTO dates(dt)
WITH RECURSIVE dates (dt) AS
(
  SELECT '1800-01-01'
  UNION ALL
  SELECT dt + INTERVAL 1 DAY FROM dates
  WHERE dt + INTERVAL 1 DAY <= '2020-01-01'
)
SELECT dt FROM dates;

You can find the number of rows in the dates table by using the following query:

SELECT 
    COUNT(*)
FROM
    dates; 

The dates table has 80354 rows.

To get all the dates in 2014, you use the following query:

SELECT 
    *
FROM
    dates
WHERE
    YEAR(dt) = 2014;

Or

SELECT 
    *
FROM
    dates
WHERE
    dt BETWEEN '2014-01-01' and '2014-12-31';

Both queries return 365 rows, which is correct.

However, there is a difference in terms of performance. The first query examines all rows in the dates table and some rows in the index while the second one uses the index only which is much faster.

See the EXPLAIN of both queries:

EXPLAIN SELECT 
    *
FROM
    dates
WHERE
    YEAR(dt) = 2014;

And

EXPLAIN SELECT 
    *
FROM
    dates
WHERE
    dt BETWEEN '2014-01-01' and '2014-12-31';

Even though the MySQL YEAR() the function is handy, when it comes to performance, you should always consider using it.

In this tutorial, we have introduced you the MySQL YEAR() function and gave you some examples of using it.