Search code examples
mysqlexpresso-store

MYSQL select entries from last 24 hours

I've read prior entries on this, but those fixes don't seem to work. I suspect (?) it is a way that the date is stored within the database, its format I suppose. Could be wrong.

Anyway:

WHERE exp_store_orders.order_status_name =  
'Approved' AND exp_store_orders.order_completed_date 
> DATE_SUB(NOW(), INTERVAL 24 HOUR) 
    LIMIT 0 , 30

This is the current attempt.

order_completed_date format example: 1396891300

This is from/for Expresso Store for ExpressionEngine.

Solution

  • order_completed_date format example: 1396891300

    Use FROM_UNIXTIME... on the order_completed_date 

    FROM_UNIXTIME( exp_store_orders.order_completed_date )
> DATE_SUB(NOW(), INTERVAL 24 HOUR)

    If order date is indexed, to make use of it, convert the other part of the date to unix_time format.

    Example 

    exp_store_orders.order_completed_date 
> UNIX_TIMESTAMP( DATE_SUB( NOW(), INTERVAL 24 HOUR ) )

    Refer to: