How to Compare Year from a Date in PostgreSQL and MySQL

When working with databases, you’ll often need to filter data by specific parts of a date, like the year. For example, you may need to find all records from 2023 or compare the year in a column with a given value. Both PostgreSQL and MySQL make it pretty easy to do this. Let’s break it down step by step.

Extracting the Year in PostgreSQL

PostgreSQL provides a function called EXTRACT() to fetch parts of a date, such as the year, month, or day.

Example:

Let’s say you have a table called orders with a column named order_date. To find all orders from 2023, you can write:

SELECT * 
FROM orders 
WHERE EXTRACT(YEAR FROM order_date) = 2023;

How It Works:

• EXTRACT(YEAR FROM order_date) pulls just the year from the order_date column.

• The WHERE clause compares the extracted year with 2023.

Using TO_CHAR for Year Comparison

Another way to extract the year is with the TO_CHAR() function:

SELECT * 
FROM orders 
WHERE TO_CHAR(order_date, 'YYYY') = '2023';

This approach treats the year as a string, which can be useful in certain scenarios.

Extracting the Year in MySQL

MySQL offers a similar feature with the YEAR() function, making it straightforward to work with years.

Example:

To fetch all orders from 2023 in MySQL, you’d write:

SELECT * 
FROM orders 
WHERE YEAR(order_date) = 2023;

How It Works:

• The YEAR(order_date) function extracts the year from the order_date column.

• The WHERE clause filters rows where the year equals 2023.

Comparing with the Current Year

If you want to compare the year in a column with the current year, MySQL allows you to use the YEAR() function along with CURDATE():

SELECT * 
FROM orders 
WHERE YEAR(order_date) = YEAR(CURDATE());

This will return all orders placed in the current year.

Key Differences Between PostgreSQL and MySQL

Conclusion

Both PostgreSQL and MySQL provide intuitive ways to compare years in a date. Whether you’re using EXTRACT() or YEAR(), it’s easy to filter records based on the year.