Perspective Table shows incorrect datetime from a Named query

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…
image

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.

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

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?

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

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

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

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 ?
image

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

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.
image

Hopefully this helps!
Regards!

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