MySQL – select data from database between two dates

by

Your problem is that the short version of dates uses midnight as the default. So your query is actually:

SELECT users.* FROM users 
WHERE created_at >= '2011-12-01 00:00:00' 
AND created_at <= '2011-12-06 00:00:00'

This is why you aren’t seeing the record for 10:45.

Change it to:

SELECT users.* FROM users 
WHERE created_at >= '2011-12-01' 
AND created_at <= '2011-12-07'

You can also use:

SELECT users.* from users 
WHERE created_at >= '2011-12-01' 
AND created_at <= date_add('2011-12-01', INTERVAL 7 DAY)

Which will select all users in the same interval you are looking for.

You might also find the BETWEEN operator more readable:

SELECT users.* from users 
WHERE created_at BETWEEN('2011-12-01', date_add('2011-12-01', INTERVAL 7 DAY));

More Related Contents:

  1. MySQL Query GROUP BY day / month / year
  2. Error in MySQL when setting default value for DATE or DATETIME
  3. Difference between two dates in MySQL
  4. How to select date from datetime column?
  5. mysql: get record count between two date-time
  6. How to cast DATETIME as a DATE in mysql?
  7. How to compare timestamp dates with date-only parameter in MySQL?
  8. Working with INTERVAL and CURDATE in MySQL
  9. Left Outer Join doesn’t return all rows from my left table?
  10. How to convert a string to date in MySQL?
  11. Converting a date in MySQL from string field
  12. MySQL: Select All Dates In a Range Even If No Records Present
  13. MySQL SELECT WHERE datetime matches day (and not necessarily time)
  14. MySQL Query – Records between Today and Last 30 Days
  15. MySQL Query to select data from last week?
  16. CURRENT_DATE/CURDATE() not working as default DATE value
  17. How to convert number of week into date?
  18. Set NOW() as Default Value for datetime datatype?
  19. Get the latest date from grouped MySQL data
  20. SQL Query to show nearest date?
  21. How to subtract hours from a datetime in MySQL?
  22. How to select the most recent set of dated records from a mysql table
  23. How do I get the first day of the week of a date in mysql?
  24. MySQL date format
  25. How to get current date & time in MySQL?
  26. how to cast the hexadecimal to varchar(datetime)?
  27. mysql – INSERT date range into date columns IF dates don’t overlap with existing ones
  28. #1292 – Incorrect date value: ‘0000-00-00’ [duplicate]
  29. MySQL cumulative sum grouped by date
  30. MySQL – How to parse a string value to DATETIME format inside an INSERT statement?

Leave a Comment