[mysql] MySQL - select data from database between two dates

I have saved the dates of a user's registration as a datetime, so that's for instance 2011-12-06 10:45:36. I have run this query and I expected this item - 2011-12-06 10:45:36 - will be selected:

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

But is not. Exist any elegant way, how to select this item? As a first idea that I got was like 2011-12-06 + 1, but this doesn't looks very nice.

This question is related to mysql date datetime

The answer is


Searching for created_at <= '2011-12-06' will search for any records that where created at or before midnight on 2011-12-06 . You want to search for created_at < '2011-12-07'.


Another alternative is to use DATE() function on the left hand operand as shown below

SELECT users.* FROM users WHERE DATE(created_at) BETWEEN '2011-12-01' AND '2011-12-06'

 


You can use MySQL DATE function like below

For instance, if you want results between 2017-09-05 till 2017-09-09

SELECT DATE(timestamp_field) as date FROM stocks_annc WHERE DATE(timestamp_field) >= '2017-09-05' AND DATE(timestamp_field) <= '2017-09-09'

Make sure to wrap the dates within single quotation ''

Hope this helps.


Have you tried before and after rather than >= and <=? Also, is this a date or a timestamp?


SELECT users.* FROM users WHERE created_at BETWEEN '2011-12-01' AND '2011-12-07';

You need to use '2011-12-07' as the end point as a date without a time default to time 00:00:00.

So what you have actually written is interpreted as:

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

And your time stamp is: 2011-12-06 10:45:36 which is not between those points.
Change this too:

 SELECT users.* 
 FROM   users
 WHERE  created_at >= '2011-12-01'  -- Implied 00:00:00
   AND  created_at <  '2011-12-07'  -- Implied 00:00:00 and smaller than 
                                   --                  thus any time on 06

Maybe use in between better. It worked for me to get range then filter it


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to date

How do I format {{$timestamp}} as MM/DD/YYYY in Postman? iOS Swift - Get the Current Local Time and Date Timestamp Typescript Date Type? how to convert current date to YYYY-MM-DD format with angular 2 SQL Server date format yyyymmdd Date to milliseconds and back to date in Swift Check if date is a valid one change the date format in laravel view page Moment js get first and last day of current month How can I convert a date into an integer?

Examples related to datetime

Comparing two joda DateTime instances How to format DateTime in Flutter , How to get current time in flutter? How do I convert 2018-04-10T04:00:00.000Z string to DateTime? How to get current local date and time in Kotlin Converting unix time into date-time via excel Convert python datetime to timestamp in milliseconds SQL Server date format yyyymmdd Laravel Carbon subtract days from current date Check if date is a valid one Why is ZoneOffset.UTC != ZoneId.of("UTC")?