Read time out sql server different than trend in Ignition

Hello,

I have some data in a MySQL server with timestamp. When I connect the data(datatype float) and timestamp (datatype datetime) to an easychart I see different timestamps. The time in ignition is exact 2 hours later. Do someone know how that is possible?

I use this format 2019-06-17 12:05:23

On the surface, and without knowing your system, I’d say there is a timezone difference between the database and the gateway.

1 Like

Excuse for the late reply.
i have checked the server time and that is UTC+2 and that is correct. When i take a look into Ignition designer and go to help and then About Ignition Designer i see that that the Client and gateway Time zone is Europe/Belgrade[GMT+1:00]. How do i change the gateway timezone? I can not see it in the configuration tab in the gateway settings.

The gateway gets its timezone from the system it’s running on; Java looks it up automatically when defining the locale. The simplest solution is to change the timezone settings on the server actually running the gateway, then restart the gateway.

If that’s not an option, or doesn’t work for some reason, you can also force the timezone directly by either specifying it in the ignition.conf file in <install dir>/data/ as wrapper.timezone=Europe/Berlin or in the wrapper.java.additional section as wrapper.java.additional.5=-Duser.timezone=Europe/Berlin.

See here for more context.

I run the server software and ignition on the same system, the system time is ok. I don’t understand that when I look into the database table the time is ok. But Ignition visualize the time 2 hours later.

I have tried to change the Ignition.conf file but when I save the file it is a .text file.

There is one thing I did when I configure the mysql database. In the extra connection properties I need to put this: ServerTimezone=UTC.
because if I don’t I get error in the connection.

Do you know what is going wrong? I only can save the conf file as a .text file.

Use a different text editor (ie, not Notepad) or switch the ‘save as’ dialog to use ‘All Files’ and then manually specify the full filename (ignition.conf).

I have change the ignition.conf file but I still have the same problem.
I have made a screenshot :
Timezone%20igition\

When I go to About Ignition Designer I get the same timezone: Europe/Belgrade [GMT+1]

You still have a leading # character in the ignition conf entry. That results in the line being ‘commented out’, so it doesn’t actually get applied. Remove the # before wrapper.java.additional.5=-Duser.timezone=Europe/Berlin and restart the gateway and you should see it take effect.

2 Likes

I have change the gateway timezone but I have still the same problem.

When I change the timezone in windows with -2 hours I have the right time in ignition but I thing that is not a good solution. I tried to change the timezone in ignition but it doesn’t work right. The timezone changed but the data is 2 hours later. I don’t understand what is wrong.

Have you checked the timezone / offset settings in MySQL?

Yes I have checked the timezone in MySQL, this is the same time as the windows time. when I look at the easychart is displays the right time on the x as, but I see no data. When I click right on the easychart and go to autorange --> all axes I see the data 2 hours in the future.

Open the query browser in the designer and run this query:

SELECT cast(current_timestamp as char) as mysqltime, current_timestamp as jdbctime;

That will provide some insight into your problem. I suspect you should be using the TIMESTAMP column type instead of DATETIME, as TIMESTAMP automatically handles time zones. (Storage is always UTC for TIMESTAMP.)

When I do that I get this as a result:

Mysqltime: 2019-08-12 11:05:19

Jdbctime: 2019-08-12 13:05:19

It looks like that the jdbctime is not right.

I have tried to use a timestamp datatype but it makes no difference.

Does someone have a solution to change the jbdc time? I have tried to change the extra connection properties in the database connection but that doesn’t work.

Have nobody a solution?

You may also want to check if there is an updated jdbc driver at some point, but I found this link for you.

That being said, if things are getting into the database correctly, it would seem the jdbc driver is working correctly. :confused:

I think this may be at the heart of the issue. I don't recall ever having to do that when I used Mysql. What error does it give?

Hello JordanCClark,

I have deleted the: ServerTimezone= UTC in the extra connection properties to see the error but I don’t get any error now and the problem seems gone. The strange thing is that I tried this in the past but then I get the error every time. Is it possible that the jdbc driver updates automatically?

Thank you for the help