meluluji meluluji - 4 months ago 8
PHP Question

Select to obtain data for each day

I have a table that store dates in this format:

7/29/2016 00:00:00 AM

What I need is show to the user all data from one day, like

+-------+-------------+---------------------+
| id | product | date |
+-------+-------------+---------------------+
| 1 | one |7/27/2016 9:29:14 AM |
| 2 | two |7/28/2016 7:53:42 AM |
| 3 | three |7/28/2016 9:53:24 PM |
+-------+-------------+---------------------+


So I want to return: in date 7/28/2016, 2 new products was recorded.
How can I achieve that?

This are the fields with the type of data:

+------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| product | varchar(255) | NO | MUL | NULL | |
| date | varchar(255) | NO | MUL | NULL | |
+------------------+------------------+------+-----+---------+----------------+


And the date was stored in DateTime format in c#.
I've tried with between:

SELECT * FROM products where date between '7/29/2016 00:00:00 AM' and '7/29/2016 11:59:00 PM';


Doesn't work. But, when I put this:

SELECT * FROM products where date between '7/01/2016 00:00:00 AM' and '7/31/2016 11:59:00 PM';


It returns the products of the month.

Answer

This MySQL query should work for you:

SELECT * FROM products
WHERE date >= '2016-07-28 00:00:00' AND date < '2016-07-29 00:00:00'

Or use BETWEEN:

SELECT * FROM products
WHERE date BETWEEN '2016-07-28 00:00:00' AND '2016-07-28 23:59:59'

If your date field is currently stored as a string, simply convert first with STR_TO_DATE:

SELECT * FROM products
WHERE STR_TO_DATE(date, '%c/%e/%Y %h:%i:%s %p') BETWEEN '2016-07-28 00:00:00' AND '2016-07-28 23:59:59'

Or, if you only want a single day (And have an index on the date field), simply use LIKE syntax to utilize these indexes:

SELECT * FROM products
WHERE date LIKE '7/28/2016 %'