Proper DateTime format to insert into a DATETIME mySQL field

General Discussion
1

I am trying to log data on a daily basis, but for the previous day. I have created a Named Query to utilize a parameter for the date (which will be set to 1 day previous). When I “test” the named query and type in a date of “2026-02-02 00:15:00” it inserts into the mySQL table just fine.

When I try to pass anything into the named query, it fails. I have tried sending it an actual DateTime tagtype from Ignition as well as a formatted string. The numerical data inserts correctly, but my LogDateTime column in the DB has “NULL”.

I am relatively new to mySQL, so kinda flustered. Since this is a once a day logged event, I would love to be able to just set up the LogDateTime with an initial value of same time but 1 day previous, but haven’t figured that part out.

Any guidance is greatly appreciated!

2

I figured out how to properly set initial DATETIME field in DB correctly. Just took some more digging. Thanks!

3

Good to know you fixed it, somehow. Would be good for others to know how, to help them as well.

Also, with datetimes, be cautious of daylight savings or timezones in general, especially if you’re in a TZ with dst or are working with multiple gateways/dbs across multiple timezones. If you're storing datetimes without timezone info in the DB, then they should be stored in UTC

3 Likes
4

I set the Default value for the datetime field to (now() - interval 1 day). This sets the timestamp to 24 hours previous to when the data is logged, which would essentially give me the proper date value for the data.

5

I give up - how is

the correct date for the data?
I can’t think of a case where that’s truthful.
If I need to report the day before an event, I would subtract a day for display or report but store the true event timestamp.

6

He's likely collecting yesterdays data just after midnight. So he wants to store against yesterday. It's a common practice.

7

Sure could be, but setting it as a default value for the created at column is a bad idea insofar as the next person who has to troubleshoot it. You insert a row, insepect it without a created at column and now it says yestedray? The person is going to start checking the clocks of the client/server etc.

Calculate it and then INSERT via prep query, either via jython and system.date.* functions or directly in the INSERT text like

INSERT INTO Table (LogDateTime) VALUES (now() - INTERVAL 1 DAY)so that its obvious.

Don’t hide business logic in default values / triggers.

2 Likes
8

Yes, it’s common practice to have scheduled batch processes to move data, but, those processes shouldn’t calculate (guess/estimate) when data events occurred. That timestamp is one additional field of data that goes along for the ride.