Introduction to MySQL YEAR Data Type

The YEAR data type in MySQL is used to store year values. The YEAR data type occupies only 1 byte of storage space and can store values from 1901 to 2155. The YEAR data type supports zero padding, and the display width can be set.

Syntax

The syntax for the YEAR data type is as follows:

YEAR[(4)]

Where, 4 represents the number of digits to be displayed for the YEAR data type. If no digits are specified, the default display width for the YEAR data type is 4 digits.

Use Cases

The YEAR data type is mainly used for storing year values, especially when it is necessary to restrict the range of years. The YEAR data type can also be used for storing data related to years, such as age and year difference. Typically, the YEAR data type is used in conjunction with other data types, such as the DATE and TIMESTAMP data types, for calculating year differences when needed.

Examples

Here are two examples demonstrating the usage of the YEAR data type:

Example 1: Storing year values

Assuming we have a table named users with two columns id and birth_year, we can create the table using the following command:

CREATE TABLE users (
  id INT PRIMARY KEY,
  birth_year YEAR(4)
);

Now, we can insert data into the users table:

INSERT INTO users (id, birth_year)
VALUES
  (1, 1990),
  (2, 1985),
  (3, 2000);

Then, we can query the users table using the following command:

SELECT id, birth_year FROM users;

The output will be as follows:

+----+------------+
| id | birth_year |
+----+------------+
|  1 |       1990 |
|  2 |       1985 |
|  3 |       2000 |
+----+------------+

Example 2: Calculating year differences

Assuming we have a table named employees with three columns id, name, and hire_date, where the hire_date column stores the date of employment of employees, we can create the table using the following command:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  hire_date DATE
);

Now, we can insert data into the employees table:

INSERT INTO employees (id, name, hire_date)
VALUES
  (1, 'John', '2015-01-01'),
  (2, 'Mary', '2018-03-15'),
  (3, 'Peter', '2020-05-10');

Then, we can calculate the year differences for each employee using the following command:

SELECT name, YEAR(CURDATE()) - YEAR(hire_date) AS years_of_service
FROM employees;

The output will be as follows:

+-------+------------------+
| name  | years_of_service |
+-------+------------------+
| John  |                7 |
| Mary  |                4 |
| Peter |                2 |
+-------+------------------+

Conclusion

The YEAR data type is a data type used to store year values. It can store any year value between 1901 and 2155, and occupies only 1 byte of storage space. It is commonly used to store year information in dates, but does not include month or day. Since the YEAR data type only stores the year, it is more storage-efficient compared to the DATETIME or TIMESTAMP data types. However, it is important to note that the YEAR data type does not store any time or date information, so it cannot be used to store complete dates or timestamps.

In practical database applications, the YEAR data type is commonly used to store year information such as employee hire year, company establishment year, etc. Using the YEAR data type can help save storage space and make some year-related calculations more convenient.