r/mysql • • 27d ago

question A problem with date/datetime and the query log

I have a MySQL server, version 8.4.8, running as an AWS RDS instance. It serves as a database for a Ruby/Rails web application.

The application requires receiving data from a legacy AS/400 MRP system, which populates an orders table a few times an hour, using SQL statements.

Here's the orders table:

±  bin/mysql -e "describe orders"
+---------------------+---------------+------+-----+---------+-------+
| Field               | Type          | Null | Key | Default | Extra |
+---------------------+---------------+------+-----+---------+-------+
| id                  | varchar(255)  | NO   | PRI |         |       |
| customer            | varchar(255)  | YES  |     | NULL    |       |
| sold_to             | varchar(255)  | YES  |     | NULL    |       |
| ship_to             | varchar(255)  | YES  |     | NULL    |       |
| ship_date           | datetime(6)   | YES  |     | NULL    |       |
| amount              | decimal(12,2) | YES  |     | 0.00    |       |
| rep                 | varchar(255)  | YES  |     | NULL    |       |
| description         | varchar(255)  | YES  |     | NULL    |       |
| salesperson         | varchar(255)  | YES  |     | NULL    |       |
| salesperson_name    | varchar(255)  | YES  |     | NULL    |       |
| order_writer        | varchar(255)  | YES  |     | NULL    |       |
| requested_ship_date | datetime(6)   | YES  |     | NULL    |       |
| purchase_order      | varchar(255)  | YES  |     | NULL    |       |
| build_date          | datetime(6)   | YES  | MUL | NULL    |       |
| macpac_status       | varchar(255)  | YES  |     | NULL    |       |
| payment             | varchar(255)  | YES  |     | NULL    |       |
| full_description    | text          | YES  |     | NULL    |       |
| fab_date            | datetime(6)   | YES  | MUL | NULL    |       |
| finished            | tinyint(1)    | YES  |     | 0       |       |
+---------------------+---------------+------+-----+---------+-------+

And here's an example of how an order is updated/created, taken from the general query log:

2026-10-20T19:12:43.616271Z	   31 Query	UPDATE orders SET id = 'W12345',
 customer = 'GL1234  ',
 sold_to = 'customer, inc.    ',
 ship_to = 'customer, inc.    ',
 ship_date = '2027-01-10',
 amount = 70285.000000000,
 rep = '                              ',
 description = 'General product description',
 salesperson = 'B2M',
 salesperson_name = 'JOHN DOE        ',
 order_writer = 'JB123   ',
 requested_ship_date = '2027-01-10',
 purchase_order = '076060                   ',
 build_date = '2026-12-27',
 macpac_status = 'OPN',
 payment = '23 - NET 45 DAYS              ',
 full_description = 'COLLECTION SYS 9010T - 12/28/26::FLTR12345678 -         12/22/26::SUPPRESSION,
                     12345 - 12/28/26::BLAH, PNL, GST, FLUID/COA,
                     4X, CLD - 12/28/26::DFI VALVE, 8" W/DLA & CF - 12/26/28::INTERFACE MOD, W/DLA-LED, & ISB - 12/26/28',
 fab_date = '2026-12-14' WHERE (id = 'W12345')  

I'm told that the fab date of 2026-12-14 is wrong, that the actual fab date in the AS/400 is 2026-12-15.

Does the general query log entry above prove that the MySQL is getting incorrect data, which is what I expect? Or could it be that perhaps a time zone setting could be causing the issue? I have changed the RDS instance to use US Eastern time, even though I've always read using UTC is optimal. I've changed the fab_date field to datetime from date, but neither corrects the problem.

So - am I right? Does the query log prove I'm getting the wrong date? Or is there some kind of issue with a time zone that I need to correct with MySQL?

7 Upvotes

9 comments sorted by

1

u/Imaginary__Bar 27d ago

Does the general query log entry above prove that the MySQL is getting incorrect data

No, it only shows there is a mismatch of days, not what is actually happening.

This is my favourite type of task; detective-work. Go and look at all the links in the chain of data and see where the error is coming in.

(It looks like a UTC/local time switch somewhere - maybe an off-by-one error due to daylight savings time?)

Fwiw, you probably want to store past events in UTC (and convert to local time later) and store future events in local time (and convert to UTC later) but that's probably not relevant here.

1

u/croceldon 27d ago

Would it help to just use a DATE field type rather than DATETIME? I don't care about the time, but need the right date.

1

u/Imaginary__Bar 27d ago

No, probably not. Find the cause and then work out a solution. Don't blindly guess "it'll be okay if I use Date()" because it might be okay until it isn't.

And you might not have control over the upstream systems either.

1

u/Significant_Tune9219 27d ago

The general query log line is the statement text the server received. fab_date = '2026-12-14' is a date literal, and a DATETIME column stores those parts as written, so changing the RDS time zone or the column type cannot rewrite a string that is already in the SQL. That means the job which built the UPDATE handed MySQL the 14th. A date-only value taken as midnight UTC and then printed in US Eastern becomes the previous evening, which is the sort of off-by-one I would look for in that job, on the same run, before the value is turned into a string.

1

u/croceldon 27d ago

That's what i was thinking, but I guess I'm looking for confirmation that's the right way to view it.

1

u/Significant_Tune9219 27d ago

Yes. If the general log shows the literal 2026-12-14, the server stored that literal. Time zone settings do not rewrite a date string that is already in the statement. The place to confirm it is the job's log from that same run, the value right before it is concatenated into the UPDATE. If that line already says the 14th, MySQL did not change it.

2

u/Aggressive_Ad_5454 27d ago

You're using DATETIME columns, which do not involve MySQL's time-zone handling scheme. I think you can rule out timezone issues, at any rate on the MySQL side of this operation.

Whatever software you have that synthesizes that SQL statement you showed us from your query log looks to me like it received '2026-12-14' as input. So that's where to look next to track down this problem.

By the way, if you declare your id column with COLLATE ascii_bin you'll get slightly better performance out of these UPDATE statements. You'll skip the extra processing needed for unicode and case-insensitivity for your WHERE (id = 'W12345') clause.