MySQL - select data from database between two dates

Question

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  gt    2011-12-01  AND created at  lt    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

User · Answer

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   gt    2017-09-05  AND DATE timestamp field   lt    2017-09-09    Make sure to wrap the dates within single quotation     Hope this helps

User · Answer

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  gt    2011-12-01 00 00 00   AND created at  lt    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  gt    2011-12-01   AND created at  lt    2011-12-07    You can also use   SELECT users   from users  WHERE created at  gt    2011-12-01   AND created at  lt   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

User · Answer

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     nbsp

User · Answer

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

User · Answer

SELECT users   FROM users WHERE created at BETWEEN  2011-12-01  AND  2011-12-07

User · Answer

Have you tried before and after rather than    and  lt     Also  is this a date or a timestamp

User · Answer

Searching for created at  lt    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  lt   2011-12-07

User · Answer

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  gt    2011-12-01 00 00 00      AND  created at  lt    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  gt    2011-12-01   -- Implied 00 00 00    AND  created at  lt    2011-12-07   -- Implied 00 00 00 and smaller than                                     --                  thus any time on 06

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

Examples related to mysql

Examples related to date

Examples related to datetime