John Davidson

Message:

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.

---

My Answer:
To get all orders created in every 8th hour interval, you can modify your query as follows:

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


In this query:
- We convert the current datetime and o_created datetime to Unix timestamp using `UNIX_TIMESTAMP()` function for easier calculation.
- We calculate the difference in hours between the current datetime and o_created datetime, then divide it by 3600 to get the number of hours.
- We use the `MOD()` function to check if the result of the division is divisible by 8, which means it falls within the 8th hour interval.
- If the condition is met, the order will be included in the result set.

Make sure to adjust the date and time values in the query according to your requirements.