Return Yesterdays Sales

[Adam Blakey]

Hi,

I have a table with all my sales from yesterday. In the field i have the date of the order stored in this format: 1286193719

I want to run a statement on the database to find all the sales from yesterday.

I thought this would work:

"SELECT fTotalPrice FROM te5_tbShop_Orders WHERE tmOrderDate = CURRENT_DATE() - INTERVAL 1 DAY"

or

"SELECT fTotalPrice FROM te5_tbShop_Orders WHERE date(tmOrderDate) = CURRENT_DATE() - INTERVAL 1 DAY"

But it returns 0 even though there are 20 sales yesterday.

Please can someone advise?

Cheers,
Adam

[code green]

The date and time functions will only work on the date and time data types.
That looks like a unix timestamp which is an integer.

So it is not an order date it is an order date and time to the nearest second.

This can be converted using FROM_UNIXTIME() which still returns seconds.
This means you need to select a time period midnight-midnight, so BETWEEN rather than '=' should be used.

You could try

WHERE tmOrderDate = CURRENT_DATE() - INTERVAL 1 DAY = DATE(FROM_UNIXTIME())