I have a date stored in a table. The timezone is UTC+1. The database where this table is stored is in Atlanta, USA. The client is in Sweden.
When i query this table from Ignition it changes the date by the difference in the timezones so a date like 2019-03-12 14:23:21 shows as 2019-03-12 09:23:21.000 if i don’t do a subraction of the date with 5 hours.
Result from Microsoft Managment Studio: 2019-03-12 14:23:21.000
Result from Ignition: 2019-03-12 09:23:21
Why does Ignition do this? Is there some settings that i have missed?
The client time zone setting is Client.
If your application runs in multiple time zones, you will need to store your data in a time-zone aware way. In PostgreSQL, I always use the column type “Timestamp with Timezone”, which stores everything in UTC under the hood, and it all “just works” everywhere.
The client only runs in Sweden.
Ok i can understand the type of column answer but that don’t explain why Ignition needs to change ‘my’ date.
It’s the JDBC layer that is doing this. The java.util.Date object is internally UTC and only converted to a local time zone at the point of display. If you feed a database a date as a string without explicit timezone, then pull it back out as an object, you will get different results if parts of your system are in different timezones. Don’t forget that the gateway’s timezone also matters. And how are the dates being stored?
Well, the stored time was in fact stored very local in the beginning as it was located in Sweden. Since a couple years back the server was moved to Atlanta. Still no problem. We store the date without UTC and our ASP pages served us with correct date. But when we connected ignition to view our data we run into this problem.
There's your problem.
Apples and oranges. Web pages are generated on a server. Ignition clients are truly local.
Still, Ignition (Java) is modifying the date.
No, the date is being interpreted in the Atlanta timezone, then sent to Sweden. In Java, that will always make the adjustment. Where’s the Ignition gateway located? That is the entity that is actually connected to the DB, and where the DB => Java interpretation would occur.
The flaw in the system is the use of non-timezone-aware components in a multiple-timezone environment.
1 Like
No, the date is sent from Sweden to Atlanta. All of our system except Ignition creates views that is is correct.
When we use Ignition to view the same data the date is corrupt.
It is only us in Sweden that use this data. We create it, we use it.
It is more than 25 years ago i started to store production data in SQL tables. There was never any discussion in that time about timezone date format.
Ignition is built on Java. Java connections to databases apply a timezone to all data that comes out. It doesn't matter that 25 years of custom code didn't assign any timezone to any of your data. Java is going to assign one at the point of conversion from the DB, which is in the Ignition Gateway. Your simplest solution is probably to set your Ignition server to Atlanta's timezone. You might also try setting your DB to your Swedish timezone. Or use extra connection properties in your DB connection setup to tweak the timezone.
You can whine all you want that Java (JDBC) shouldn't require a timezone, but that's not going to convince anyone in control of Java itself to change. There isn't and won't be a magic wand that turns it off.
1 Like
I don’t whine at work. That something i drink on friday evening 
(English is not my native language)
4 Likes