SQL SELECT Getting Wrong Time

[Alex_Leech]

Hi all,

Probably being a bit stupid here. I have a datetime2 'createdAt' field with a time of '2024-08-07 13:37:49' in a SQL server. This SQL server has a timezone of 'Romance Time' i.e. Paris. All dates I have stored in this SQL server are UTC dates I.e. The local time this happened was '2024-08-07 15:37:49' .

In ignition named query generator, on a seperate VM, I have a simple select query. This VM is set to 'Romance Time' as well. On doing a simple select, the named query returns '2024-08-07 12:37:49'. Why? I believed ignition assumed datetime2 was UTC time, but even if it was assuming localtime, it should have subtracted 2 hours. Do I somehow have to tell ignition in the query its getting UTC time so it can correct properly?

Following this - When the same query is displayed in a table, it adds an hour again and shows '2024-08-07 13:37:49'.

Little confused! Any help?

Thanks!

[pturmel]

datetime2 columns store a local time + timezone combination that properly describes an "instant" in universal time. When queried, JDBC converts such values correctly to that "instant" in UTC, then discards the original time zone. In java, the object is then UTC under the hood (typically java.sql.Timestamp, a more precise subclass of java.util.Date).

Then, wherever Ignition/Java needs to express the date object as a string, it uses the timezone of the environment it happens to be in. If a script in a gateway, it will use the gateway's time zone. If a script or expression in a designer, it will use the designer's time zone, or, perhaps, an explicit project time zone. For clients, same thing.

[Alex_Leech]

Thats @pturmel ! Great to know whats going on under the hood

So I have a column defined as the below in MSSQL Server (Yes I know - Future projects are planned to be postgres but we're stuck with this for now!)

	createdAt datetime2 NOT NULL DEFAULT GETUTCDATE(),

This should then store localtime and UTC as its timezone instant based on the above. That makes me a little confused then why the JDBC driver is subtracting an hour?

EDIT: MSSQL datetime2 seems to not store anytime zone info?

[Alex_Leech]

Just going to add to this a bit further - Running:

SELECT GETUTCDATE()

Returns the UTC date - 1 hour

This post seems ver relevant. Hopefully not what I'm going to have to do for all queries!

Ignition Timezone of the server

Hi guys! My main server at the Michigan, an it's time zone is Chicago. However, my plants are ate the different time zones. I'm already have a lots of widgets that uses function now() for Tag History requests. In expression now() always return server time zone time. I tried to change Project->Project Properties->Designer->Timezone Behavior as well as Project->Project Properties->General-> Project Timezone. But this has zero influence on function now(). Does anyone has similar problem?

[pturmel]

@Alex_Leech

Running:

SELECT GETUTCDATE()

Returns the UTC date - 1 hour

Well, it has to the render the datetime+zone in the user's timezone when printing that result.

Use the techniques in this topic to render in a particular time zone:

Ignition Perspective Page to show the client System date and time

Sure. I recommend DateTimeFormatter.ofPattern(). But I would move the complexity to a project script module with a DateTimeFormatter instance with your preferred pattern saved as a top level variable. Like so, perhaps: from java.time import ZoneId from java.time.format import DateTimeFormatter defaultFormatter = DateTimeFormatter.ofPattern("YYYY-MM-dd HH:mm") def zonedFormat(ts, zoneid, format=None): formatter = defaultFormatter if format is None else DateTimeFormatter.ofPattern(format) r…