MySQL Between

[ids]

I'm working on a module that grabs Order data from the orders table. I've used the greater than/less than technique but now I am using "between." I get the same results for either. The condition is for the "orderdate" field. It's the system time of the order.

The results I receive of my date filtering condition is close to what I expect. The start and end date are specific 10 digit representation of the dates in question. The start date system time works perfectly as expected. The end date is where the results are dubious. If I use the end date as July 31, 2019, converted to system time using mktime() , I am receiving order records through August 2, 2019. Some months have been fewer or more days past the input end date. It doesn't appear to be a time zone issue, but I haven't ruled that out.

I am not sure what is happening. The condition being evaluated seems simple, while it's a system time, they are just numbers in regards to the data input and system function. If I am not recalling incorrectly, when I use Order Processing screen with the exact date filters I am seeing similar dubious results where the results are more records than expected.

Maybe I am wondering if I should use a system start date and the number of days equivalent in seconds instead?

Scott

[wmgilligan]

Scott,
I suspect one or all of the following:
- the order date is actually when the basket was created, and not when the order was finalized.
- the order date is when the order was paid for (or last updated) and not when it was created.
- a time zone issue

Can you run your query and locate an order that is past the expected time?
And then look at that order within the MySQL table itself? Is the order date "between" your settings? Are you grabbing the basket creation date (if that exists - been awhile since I did Merchant stuff)? Is the time somehow off?

[ILoveHostasaurus]

From Jon, this may be a string comparison issue. In MM9 that field is a string, not integer, even though it's storing a unix timestamp (integer), so comparison functions may not behave exactly as expected. Could probably tinker with a direct SQL query to test that out, or I can help do so.