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.