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:


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:



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



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



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