Good afternoon,
I'm starting to play with different timezone and mssql database, looking here and there I see that in phyton is used the
pytz library
Should be possible to install it in Ignition?
Describe what you're actually trying to do in more detail.
Installing Python libraries is ~never the answer. You should be using the Java standard library, which has comprehensive timezone information already available to you, but without knowing what you're actually trying to do, any advice will be uselessly vague.
3 Likes
Don't use jython libraries where java has native support. Use java.util.Date, java.util.Calendar, or classes from java.time and its sub-packages.
2 Likes
Just for completeness' sake, the github repo for pytz shows it's C-based so it wouldn't work with Jython anyway.
3 Likes
Thank you all for the answers, you're right I try to explain what I need to do.
I'm reading with kepware some PLC status in China (but in any case in future I will need to do the same in USA and other countries).
I manage this status to chart the specific production lines (0 machine out of work, 1 running), so I write I script that every minute updates a mssql table in the way I need to have to visualize this data (for example I have the columns StartDate and EndDate.
I'm in Italy so in this script when I write system.date.now() I have my timezone and in the table I write the "wrong" time, this is why I need to know the "difference" between my hour and the PLC hour to modify the columns StartDate and EndDate accordly.
Thank you guys
The fundamental mistake here is using a database that isn't recording timestamps as UTC, or at least storing the origin time zone with the value.
In MS SQL Server, use datetime2 columns instead of datetime columns.
In PostgreSQL, use timestamptz columns instead of timestamp columns.
In MariaDB (or MySQL), use timestamp(3) columns instead of datetime columns.
Do not write your own code to massage timestamps for perceived timezone issues.
Is not my choise to use a MS SQL Server, is IT that takes this decisions so unfortunately I need to adapt.
I could use a datetime2 column if this avoid the problem, so you suggest to change the column type and insert directly my timezone datetime?
And later when I will read the data what should I do to manage it accordly with the timezone?
No, when the value is stored in the database, it should have the timezone included where the data originated.
Then when you pull it, it would be converted into the corresponding time for your timezone (or the timezone of the client to be more correct).
If I use this approce, how can I retrieve the origin time zone? Should I set it statically?
Ok, I'm missing this point, how can I know the origin timezone?
How are you collecting the data? The gateway that is connected to these PLC's in China should in theory have the correct timezone. So you would get it from there. The how depends on how you are collecting the data.
If your are inserting Ignition ordinary datetime objects[1], via Ignition, into SQL Server, the proper information will be included. (The datetime2 column will receive the correct value, using the Ignition gateway's time zone.)
When querying such data later, the timestamp+tz information in the datetime2 column will be merged back into the pure UTC format used by java.util.Date internally, and then presented in user timezones as needed in Ignition.
In other words, it Just Works™.
This means
java.util.Dateobjects delivered to inserts or updates using value parameters in Named Queries or?substitutions in scripted "Prep" queries. ↩︎
1 Like
Thanks for the explanation, of course I need to study the datetime2 and java.util.Date because until now I've always used system.date for me needs.
I try to make an example about this because probabily I don't understand completely what you mean.
I read data with Igniton (the gateway is in Italy), then I save the data into the DB that is in Italy too.
So for example now in Shangai is 13:35, but if I insert into the DB the datetime value will be the gateway hour, so in this case 19:36.
If I run the client from the Italy I will have no conversion or am I wrong?
And in this case I will see "wrong" charts.
Thank you for your patience.
All of the system.date.* functions supply java.util.Date instances. And the corresponding expression functions. And all of the timestamps that show up in the tag system or anywhere else that used QualifiedValue objects. That is what Ignition uses everywhere.
As long as the gateway's clock and timezone are correct, the datetime2 column will receive that timestamp with the gateway timezone.
A client running in Italy will see the original Shanghai timestamp converted to the Italian timezone. Inside Ignition, the java.util.Date object will be carrying the UTC millisecond value everywhere.
(Strictly speaking, the client will use the project timezone setting, which defaults to the timezone wherever the client happens to be.)
The difference is that datetime2 also stores the time zone. So if you're storing the data at the gateway, it will store it with the gateway time and timezone (GMT+2 or GMT+1 depending on the time of year). Then depending on where you view it and configuration of the client, it will show that timestamp of the data in the local timezone. (Phil can correct me if I'm wrong)
3 Likes
Another option is to run all of your gateways and databases set to UTC. Then you can use a regular (naïve) datetime column in your tables. Ignition, which handles everything with the UTC-based java.util.Date, will write UTC to the database, and read UTC from the database, and will handle all other time zone conversions (both directions) for you.
You must forgive me but the solution still eludes me.
From what I understand If I'm run the client (so I'm Italy) the datetime will be 02/08/2024 19:49 or not?
Why the system should give me the Shangai conversion? I mean, I read the status from Italy, I write to Italy and I read the database from Italy..
I don't understand what's the moment where the Shangai datetime is managed.
In the same why If a chinese run the client from Shangai in this case the timezone will be converted correctly right?
And the point is that if I run from Italy, I need to see the actual datetime of Shangai, not the italian datetime.
Sorry again, but this is a little bit tricky for me
This should be perfect but my problem is that I need to see always the Shangai datetime wherever the client is run in the world.
For example if I run from Italy, from USA or wherever the "actual" datetime should be the actual Shangai time.
Then set the project timezone to Shanghai.
Whenever you have to deal with time zones, handle the data always in UTC, or with attached time zone, so there's never any ambiguity.
Keep the presentation of time information, and the entry of time information, separate from the transmission of time information.
Actually I need to manage multiple timezone, Shangai, Atlanta, Italy and others in the same project.