MySQL between date formats

Question

I have the query...

SELECT COUNT(order_id) as orders, 
       SUM(order_total) as sales 
  FROM uc_orders 
 WHERE FROM_UNIXTIME(created) BETWEEN "2010-08-14" AND "2010-08-15" 
   AND order_status = "payment_received"

The problem is that's not returning the orders from the provided date period. I'm not getting any SQL errors. Is there something about BETWEEN that I'm doing wrong?

Answer 1

Your query should work without any changes and even without adding hours/minutes/seconds. I just tested that on MySQL 5.1.

More likely you just don't have any data between these dates and with order_status = "payment_received"

mysql> SELECT COUNT(session_id) as orders FROM phpbb_sessions 
WHERE FROM_UNIXTIME(session_time) BETWEEN "2010-08-20" AND "2010-08-21";
+--------+
| orders |
+--------+
|    224 | 
+--------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(session_id) as orders FROM phpbb_sessions 
WHERE FROM_UNIXTIME(session_time) BETWEEN "2010-08-20 00:00:00" AND "2010-08-21 23:59:59";
+--------+
| orders |
+--------+
|    224 | 
+--------+
1 row in set (0.00 sec)