Select results from mysql using a regular date from timestamp column

Question

I seem to be having some problems with something simple here.

I have some data with timestamps. I have a pair of dates and I want to get the data between those dates, IE:

I've tried:

SELECT 
    SUM(total_amount) as total_sales_amount 
FROM 
    purchases 
WHERE 
    timestamp <= "2011-11-13"   
AND 
    timestamp >= "2011-11-06"

and:

SELECT 
    SUM(total_amount) as total_sales_amount 
FROM 
    purchases 
WHERE 
    date(timestamp) <= "date(2011-11-13)" 
AND 
    date(timestamp) >= "date(2011-11-06)"

This doesn't work.

It doesn't throw an error, it just doesn't return the results between those dates.

What am I misunderstanding?

http://stackoverflow.com/questions/1080207/mysql-select-all-data-between-two-dates — Casey Robinson, Nov 13 '11 at 22:38

score 1 · Accepted Answer · answered Nov 13 '11 at 22:37

Try this, seeing as date is actually a MySQL function:

SELECT 
    SUM(total_amount) as total_sales_amount 
FROM 
    purchases 
WHERE 
    timestamp <= date("2011-11-13") 
AND 
    timestamp >= date("2011-11-06")

Note: When asking for help like this, please include the full error message for each query; as well as the output you are expecting.

Select results from mysql using a regular date from timestamp column

1 Answers1