MYSQL select entries from last 24 hours

Question

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.

Answer 1

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:

• FROM_UNIXTIME(unix_timestamp),
• FROM_UNIXTIME(unix_timestamp,format)
  • Format UNIX timestamp as a date
• UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)
  • Return a UNIX timestamp