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.

[Scott_SCADA]

Thanks for your response Adam. I did have a look into the timezone settings in MySQL before posting the question and it doesn't seem to be an issue.

Trying some of the commands in this Stack Overflow question (I am running these in MySQL command line client on the MySQL server): 

https://stackoverflow.com/questions/2934258/how-do-i-get-the-current-time-zone-of-mysql

I tried running: 

select now();

It returns the correct local date and time (AEST), matching the system clock.

I tried running: 

select timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00'));

It returns 10:00:00 which is the correct offset for AEST at the moment.

So the issue seems to be some sort of incompatibility between Geo SCADA linked ODBC tables and an SQL database with DateTime stored in local time. Unless I am completely missing a setting somewhere? At this stage the only practical solution seems to be to set the users to Use UTC time. But this will be super confusing for operators browsing the rest of the SCADA system.

[Scott_SCADA · Accepted solution]

So I did come up with a work-around for this on the MySQL side. It is a bit hacky, but it works. I will provide details here, even though it is not really a Geo SCADA solution.

For each table in MySQL that contains DateTime columns, I create a View in MySQL that queries the same rows, but converts the DateTime column to UTC. For Example:

CREATE VIEW ExampleTableGS AS SELECT Col1, Col2, CONVERT_TZ(DateTime, 'SYSTEM', '+00:00') AS "DateTime" FROM ExampleTable;

I then create another Linked ODBC table for each of these views. I use the View table when Geo SCADA needs to display the data in an embedded list and use the existing table when creating new rows (making sure to use local time for DateTime columns). 

So essentially I am getting MySQL to take off 10H from each row, only for Geo SCADA to add it back on to end up with the same Date/Time. During daylight savings, it will be 11 hours but both systems have the same timezone, so will adjust automatically and the data should still be correct in Geo SCADA.