Query Linked ODBC tabled in Local Time

[Scott_SCADA]

Hi all,

Short version of my question: is it possible to get Geo SCADA to NOT adjust the DateTime colums when querying a Linked ODBC Table?

Long version: I am building a small SCADA system for a customer using the latest Geo SCADA (86.9210). They have an existing MySQL database which they want to keep (even though Geo SCADA could replace its functionality completely with custom tables). The tables in this database are a mix of static (information) and dynamic (logging when they perform major tasks etc,).

I have set up Geo SCADA to talk to the MySQL tables via Linked ODBC tables and can successfully query, insert, delete and modify from these tables. The problem is that the DateTime columns in the MySQL tables are stored in local time (Australian Eastern Standard Ttme). But Geo SCADA seems to assume that the data is stored in UTC and adds 10  or 11 hours to it (depending if DST is in effect) when displaying query results in ViewX. They don’t have issues with the missing/repeated hour during the DST change because they are never doing anything that will log data in the middle of the night (or on a weekend) when the change-over to/from DST happens.

One of the things that we will provide in the SCADA is mimics with embedded lists that query their MySQL data and they can filter the lists on DateTime and other columns. It is these lists that are displaying incorrectly (on DateTime) and I would like to get to match the actual stored data. The date displays fine if I query via MySQL Workbench. I have explored a few options to fix this:

• Change their MySQL tables to store the data in UTC. This would be an annoying exercise as it is decades of data across a bunch of tables. I am also hesitant to take responsibility for making such a big change that isn’t really in the scope of this project.
• Set all Geo SCADA users to “Use UTC time”. This does fix the queries, but causes other annoying issues because the times are wrong everywhere else, such as the Last Updated statistics of points/scanners in View Status.
• Set any local client PCs to GMT timezone. This would probably fix the issue. But cause other issues.
• Adjust the time as part of the query by subtracting the correct number of hours. This would probably work, but would make the queries take longer as it would need to do the calculation for every row and they may be displaying years or decades of data. Also, the number of hours (to substract) changes depending whether DST is active or not.

Ideally, I would like Geo SCADA to treat any data to/from the Linked ODBC as local time and any query would display the data as it appears in the MySQL tables (not adjust it for local time in ViewX). Is there any way to get Geo SCADA to do this (maybe a registry setting?). Or else, are there any other suggestions to deal with this issue?

[AdamWoodlandToo]

I don't have any MySQL to hand to test, but have you checked that MySQL isn't set to a default timezone of UTC? So when the date is queried it thinks it needs to add some hours?

It might be a case of changing my.cnf to have an updated default timezone but that may have a lot of consequences for other clients?

https://www.connectionstrings.com/mysql/ doesn't list any options for timezones

From a quick Google search I probably have found similar things to you, serverTimezone=UTC seems to exist in JDBC which may also work in the connection string for ODBC? Try it and see?

The following seems to have some interesting discussions on my MySQL and timezone

• https://stackoverflow.com/questions/930900/how-do-i-set-the-time-zone-of-mysql
• https://youtrack.jetbrains.com/issue/TW-55050/Make-sure-time-zone-is-handled-correctly-when-MySQL-JD...

Nothing to specifically help, maybe just help you down a track.