Douglas on Web Applications

Query MySQL by Date Range Using Timestamp

Using MySQL TIMESTAMP, as opposed to DATETIME,  only requires a slight modification to our code in order to retreive records that fall within a specific date range (column_name becomes DATE_FORMAT(column_name,'%Y-%m-%d')).

Example usage:

• Today = " AND DATE_FORMAT(date_added,'%Y-%m-%d') = curdate() "
• Tomorrow= " AND DATE_FORMAT(date_added,'%Y-%m-%d') = date_sub(curdate(),INTERVAL 1 DAY) "
• This Week= " AND year(DATE_FORMAT(date_added,'%Y-%m-%d'))=year(curdate()) "
• Last Week= " AND year(DATE_FORMAT(date_added,'%Y-%m-%d'))=year(date_sub(curdate(),INTERVAL 7 DAY)) and week(DATE_FORMAT(date_added,'%Y-%m-%d'))=week(date_sub(curdate(),INTERVAL 7 DAY))) "
• This Month= " AND year(DATE_FORMAT(date_added,'%Y-%m-%d'))=year(curdate()) and month(DATE_FORMAT(date_added,'%Y-%m-%d'))=month(curdate()) "
• Last Month= " AND year(DATE_FORMAT(date_added,'%Y-%m-%d'))=year(date_sub(curdate(),INTERVAL 1 MONTH)) and month(DATE_FORMAT(date_added,'%Y-%m-%d'))=month(date_sub(curdate(),INTERVAL 1 MONTH)) "

Tags: date range, mysql, php, query, timestamp