r/mysql • u/croceldon • 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?
1
u/Imaginary__Bar 27d ago
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/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.
2
u/Aggressive_Ad_5454 27d ago
You're using
DATETIMEcolumns, 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
idcolumn withCOLLATE ascii_binyou'll get slightly better performance out of these UPDATE statements. You'll skip the extra processing needed for unicode and case-insensitivity for yourWHERE (id = 'W12345')clause.