Archive

Archive for April, 2020

MySQL: Find data entered on this date one year ago

April 25th, 2020

To find data entered into a MySQL database on this date one year ago you can use the MySQL date functions in your SQL query. The functions to use are:-

 

NOW – this gets the current date and time.
DATE – this extracts the date portion of a time value.
DATE_SUB – this subtracts an interval to a date/ time value.
DATE_ADD – this adds an internval to a date/ time value.

 

For example, if you have a field EntryDate in a table called Payments you can find all payment made on this day one year ago using a SQL query like this:

SELECT * FROM `Payments` WHERE EntryDate > DATE_SUB(DATE(NOW()), INTERVAL 1 YEAR) AND EntryDate <= DATE_SUB(DATE(DATE_Add(NOW(), INTERVAL 1 DAY)), INTERVAL 1 YEAR)

The parts of the query that look for EntryDate values between the start of the day one year ago and the start of tomorrow one year ago are:

 
EntryDate > DATE_SUB(DATE(NOW()), INTERVAL 1 YEAR) 

 

This expression gets the date part of the time now using:

 
DATE(NOW())

 

which gets the start of today, i.e. midnight, then subtracts 1 year using DATE_SUB. and then:

 
EntryDate <= DATE_SUB(DATE(DATE_Add(NOW(), INTERVAL 1 DAY)), INTERVAL 1 YEAR)

 

which adds 1 day to the start of today then subtracts one year.