Search code examples
phpmysqlmariadb-10.1

Get orders in every nth hour interval in MySQL

We collect orders from users and give them a window of 3 days to pay before order is delivered/canceled. We want to send them notification every 8th hour to remind them for payment. we use hourly cron to send notification.

How can we get all orders created in that 8th/16th/24th etc hour? We tried following query (simplified)

SELECT * FROM t_orders where o_created > '2021-05-01 11:11:11' AND MOD( CEIL(('2021-05-02 12:12:12' - CAST(o_created AS DATETIME))/3600), 8 ) = 0

where o_created is varchar field (unfortunately), 2021-05-01 11:11:11 is 3 days before date (we accept payment for 3 days) and 2021-05-02 12:12:12 is current datetime. Both datetime comming from php, so calculation is there and no issues there.

But it doesn't seems working. we are getting all orders within 3 days.

Solution

  • This is where TIMESTAMPDIFF can be your friend:

     WHERE TIMESTAMPDIFF(HOUR, o_created, Now()) IN (8, 16, 24, … )