Perspective Table shows incorrect datetime from a Named query

[carlos.aguero]

I have a Table that is getting its data from a Named query, the table has 1 DateTime column, when testing the named query returns the datetime as shown in the SQL server, no problem so far…

In Perspective I inserted a table and added a Query Binding type to show the data from the Named query, but the table DateTime column doesn’t match with what is in the SQL server, is off by 3 hours…

any ideas why the named query is changing the date time column?
Where can I change this setting in the Table to correct this ?.. What am I missing?

I’ll appreciate the help.

[Kleppe85]

Same issue here… Did you figure it out @carlos.aguero ?

[Arnaud_Declerck]

Are you sure that our db and ignition are in the same timezone settings ?
How is you date generated in your DB ? by your db query or by ignition?

[Kleppe85]

Three images,
1 = Testing of named query in designer
2 = Perspective table
3 = pgAdmin

[Kleppe85]

Might actually be that my setup is messing things up. Both ignition and postgresql is running in containers, designer and pgadmin running locally.

[Arnaud_Declerck]

witch type of column do you use in your DB ? Datetime, Integer ?

At first it seems that your timezone is not the same between the two

[Arnaud_Declerck]

Log_2021-11-29_1642.zip (2.1 KB)

Here you have two queries that i use for some data log one to create my table into my db and another to write the logging into the table. If i retrieve the data into a perspective table the date stay correct.
But DB and gateway are on the same computer.

i suppose that you have set the column properties correctly ?

[pturmel]

Note that IA’s auto-generates t_stamp columns in PostgreSQL with type timestamp. It really should always be type timestamptz.

[carlos.aguero]

The only way I could fix the time difference was by changing the Project Timezone to match my SQL Server Timezone by going into the the menu options: PROJECT/Project Properties/Perspective/General/Project TimeZone.

It appears that the Timezone change updates only during the Gateway start up, when I changed the Project timezone I didn’t see the changes reflected immediately, only after stopping and starting the gateway.

Hopefully this helps!
Regards!

[pturmel]

If you are stuck with MS SQL Server, you should know that it doesn't play well with Java and time zones. More info:

Ignition Min and Max Date From Dataset

If by SQL you mean MS SQL Server, then yes, you are [expletive]. Its timezone-aware column type, DATETIME2, is not generically supported by JDBC. So you must use DATETIME, which uses local time. Pretty much all other databases have a timezone-compatible column type that stores UTC under the hood and converts to and from the connection timezone on the fly. This behavior perfectly matches Java’s date/time functionality and “just works” with Ignition. If you’ve ever wondered why Ignition’s Tag…

SQL Server store datetime object with time zone

Two suggestions: Don’t use jython’s datetime. Use java.util.Date and related types–that’s what Ignition passes around. Set MS SQL Server to use UTC and the server it runs on to UTC. That is the only way to have flawless date/time/timezone support in Ignition. Java’s datetime type is fundamentally built in milliseconds UTC (including serialization when passed over a network) with conversions to and from local time zones as needed. This allows clients and gateways to work with the same…