# SQL SELECT Getting Wrong Time **URL:** \ **Category:** Ignition\ **Tags:** database, perspective, ignition81\ **Created:** [August 7, 2024, 3:12pm UTC](https://forum.inductiveautomation.com/t/sql-select-getting-wrong-time/94299 "2024-08-07T15:12:24Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![Alex\_Leech](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alex_leech/32/39391_2.png) [@Alex\_Leech](https://forum.inductiveautomation.com/u/Alex_Leech)\ **Post date:** [August 7, 2024, 3:12pm UTC](https://forum.inductiveautomation.com/t/sql-select-getting-wrong-time/94299/1 "2024-08-07T15:12:24Z")
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! ---
**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:** [August 7, 2024, 3:29pm UTC](https://forum.inductiveautomation.com/t/sql-select-getting-wrong-time/94299/2 "2024-08-07T15:29:35Z")
`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. ---
**Author:** ![Alex\_Leech](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alex_leech/32/39391_2.png) [@Alex\_Leech](https://forum.inductiveautomation.com/u/Alex_Leech)\ **Post date:** [August 7, 2024, 3:35pm UTC](https://forum.inductiveautomation.com/t/sql-select-getting-wrong-time/94299/3 "2024-08-07T15:35:44Z")
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!) ```python 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? ---
**Author:** ![Alex\_Leech](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alex_leech/32/39391_2.png) [@Alex\_Leech](https://forum.inductiveautomation.com/u/Alex_Leech)\ **Post date:** [August 7, 2024, 3:48pm UTC](https://forum.inductiveautomation.com/t/sql-select-getting-wrong-time/94299/4 "2024-08-07T15:48:20Z")
Just going to add to this a bit further - Running: ```python 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](https://forum.inductiveautomation.com/t/ignition-timezone-of-the-server/80848): > > 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? ---
**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:** [August 7, 2024, 3:58pm UTC](https://forum.inductiveautomation.com/t/sql-select-getting-wrong-time/94299/5 "2024-08-07T15:58:07Z")
> [@Alex\_Leech](#): > > Running: > > ```python > 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](https://forum.inductiveautomation.com/t/ignition-perspective-page-to-show-the-client-system-date-and-time/73265/8): > > Sure. I recommend [DateTimeFormatter.ofPattern()](https://docs.oracle.com/en/java/javase/11/docs/api/java.base/java/time/format/DateTimeFormatter.html#ofPattern(java.lang.String)). 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…