# Perspective Table shows incorrect datetime from a Named query **URL:** \ **Category:** Ignition\ **Tags:** database, perspective\ **Created:** [July 31, 2021, 10:44pm UTC](https://forum.inductiveautomation.com/t/perspective-table-shows-incorrect-datetime-from-a-named-query/49428 "2021-07-31T22:44:11Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![carlos.aguero](https://avatars.discourse-cdn.com/v4/letter/c/cab0a1/32.png) [@carlos.aguero](https://forum.inductiveautomation.com/u/carlos.aguero)\ **Post date:** [July 31, 2021, 10:44pm UTC](https://forum.inductiveautomation.com/t/perspective-table-shows-incorrect-datetime-from-a-named-query/49428/1 "2021-07-31T22:44:11Z")
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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/8/d8e35a2fb4dedf477435f69846d9445535da9ffb.png) 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… ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/2/f299e7e406cfd6e77b7fe34bd6e30cd65529d291.png) 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. ---
**Author:** ![Kleppe85](https://avatars.discourse-cdn.com/v4/letter/k/c4cdca/32.png) [@Kleppe85](https://forum.inductiveautomation.com/u/Kleppe85)\ **Post date:** [November 29, 2021, 2:34pm UTC](https://forum.inductiveautomation.com/t/perspective-table-shows-incorrect-datetime-from-a-named-query/49428/2 "2021-11-29T14:34:10Z")
Same issue here… Did you figure it out @carlos.aguero ? ---
**Author:** ![Arnaud\_Declerck](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/arnaud_declerck/32/44872_2.png) [@Arnaud\_Declerck](https://forum.inductiveautomation.com/u/Arnaud_Declerck)\ **Post date:** [November 29, 2021, 3:03pm UTC](https://forum.inductiveautomation.com/t/perspective-table-shows-incorrect-datetime-from-a-named-query/49428/3 "2021-11-29T15:03:13Z")
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? ---
**Author:** ![Kleppe85](https://avatars.discourse-cdn.com/v4/letter/k/c4cdca/32.png) [@Kleppe85](https://forum.inductiveautomation.com/u/Kleppe85)\ **Post date:** [November 29, 2021, 3:12pm UTC](https://forum.inductiveautomation.com/t/perspective-table-shows-incorrect-datetime-from-a-named-query/49428/4 "2021-11-29T15:12:50Z")
Three images, 1 = Testing of named query in designer 2 = Perspective table 3 = pgAdmin ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/9/491545061c004dfd31b2873a9936217c8284f471.png) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/f/0fd8b3074faa027e8c431c453df6b0ff566f7777.png) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/d/3df9a10bf9905c89219097ece174c287438bdceb.png) ---
**Author:** ![Kleppe85](https://avatars.discourse-cdn.com/v4/letter/k/c4cdca/32.png) [@Kleppe85](https://forum.inductiveautomation.com/u/Kleppe85)\ **Post date:** [November 29, 2021, 3:24pm UTC](https://forum.inductiveautomation.com/t/perspective-table-shows-incorrect-datetime-from-a-named-query/49428/5 "2021-11-29T15:24:06Z")
Might actually be that my setup is messing things up. Both ignition and postgresql is running in containers, designer and pgadmin running locally. ---
**Author:** ![Arnaud\_Declerck](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/arnaud_declerck/32/44872_2.png) [@Arnaud\_Declerck](https://forum.inductiveautomation.com/u/Arnaud_Declerck)\ **Post date:** [November 29, 2021, 3:41pm UTC](https://forum.inductiveautomation.com/t/perspective-table-shows-incorrect-datetime-from-a-named-query/49428/6 "2021-11-29T15:41:34Z")
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 ---
**Author:** ![Arnaud\_Declerck](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/arnaud_declerck/32/44872_2.png) [@Arnaud\_Declerck](https://forum.inductiveautomation.com/u/Arnaud_Declerck)\ **Post date:** [November 29, 2021, 3:46pm UTC](https://forum.inductiveautomation.com/t/perspective-table-shows-incorrect-datetime-from-a-named-query/49428/7 "2021-11-29T15:46:56Z")
[Log\_2021-11-29\_1642.zip](https://forum.inductiveautomation.com/uploads/short-url/zqJaYDS44VBU4mJyEYgivK0ez8q.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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/b/7bbe06b4bf52569e5da410a1aa07b2f3d21a350c.png) ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [November 29, 2021, 4:03pm UTC](https://forum.inductiveautomation.com/t/perspective-table-shows-incorrect-datetime-from-a-named-query/49428/8 "2021-11-29T16:03:20Z")
Note that IA’s auto-generates t\_stamp columns in PostgreSQL with type `timestamp`. It really should always be type `timestamptz`. ---
**Author:** ![carlos.aguero](https://avatars.discourse-cdn.com/v4/letter/c/cab0a1/32.png) [@carlos.aguero](https://forum.inductiveautomation.com/u/carlos.aguero)\ **Post date:** [November 29, 2021, 10:37pm UTC](https://forum.inductiveautomation.com/t/perspective-table-shows-incorrect-datetime-from-a-named-query/49428/9 "2021-11-29T22:37:31Z")
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. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/2/f2f6375aa9bd65e6f3a88443f84c85d8d8a8b151.png) 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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/7/4755e0cf427773ac5318a3dc85e8b75990250a6e.png) Hopefully this helps! Regards! ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [November 30, 2021, 1:08am UTC](https://forum.inductiveautomation.com/t/perspective-table-shows-incorrect-datetime-from-a-named-query/49428/10 "2021-11-30T01:08:53Z")
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](https://forum.inductiveautomation.com/t/ignition-min-and-max-date-from-dataset/18499/7): > > 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](https://forum.inductiveautomation.com/t/sql-server-store-datetime-object-with-time-zone/49319/2): > > 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…