Through trial and error, my colleague and I have found that the MSSQL JDBC driver included in 7.9 will return an ISO 8601 compliant string when reading a DATETIMEOFFSET column from MSSQL.
We just replaced the MSSQL JDBC driver in Ignition 8 with the (significantly older) MSSQL JDBC driver from Ignition 7.9.
Edit: Spelling
When you say Ignition handles UTC to the daylight savings times beautifully, what do you do in your queries that makes it easy?
Something like this?
SELECT CONVERT(datetime, '2024-06-01 12:00:00') AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS EasternTime
Nothing. Doing anything but nothing makes it not easy. To do nothing and have Ignition time zones Just Work™, you need a database column type that either ...
... maps the column type to/from java.util.Date or java.sql.Timestamp in their JDBC driver.
In MS SQL Server, the datetime2 column type meets this requirement. (It didn't use to, but does now.)
In PostgreSQL, the timestamptz aka timestamp with time zone column type meets this requirement.
In MariaDB, the timestamp column type meets this requirement.
I have a datetime column in UTC. Data collection was written to collect in UTC.
I need to use a where clause to filter for the shift start and end.
What is the equivalent of doing nothing to make it work for me?
I am thinking that I have to do some kind of converting because UTC could be 4 or 5 hours too high depending on the time of year.
Change the datetime column to datetime2.
(I recommend changing Ignition's translators column types for "Datetime" to those in the prior comment.)
I think you are saying the correct way is changing Ignitions translators datatype Datetime to a Datetime2.
I don't have permission yet to change that.
If you do that, then UTC time would still be UTC time though correct?
I tried to cast the UTC time as a datetime2. I am sure the unseen part changed.
I still need to do converting though to use a where clause on the UTC value though I think.
I am missing something that is not obvious to me.
Here is my where clause I want to use, but am stuck on currently.
Where
StartTimeUTC between
case when DATEPART(HOUR, GETDATE()) < 7 then DATEADD(hh,-17,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0))
when DATEPART(HOUR, GETDATE()) >= 19 then DATEADD(hh, 7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0))
else DATEADD(hh, -5,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0)) end
and
case when DATEPART(HOUR, GETDATE()) < 7 then DATEADD(hh, -5,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0))
when DATEPART(HOUR, GETDATE()) >= 19 then DATEADD(hh, 19,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0))
else DATEADD(hh, 7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0)) end
That is a convenience that causes Ignition's table-creation helpers to use those column types. The critical change is to the table--change the column type.
Yes, it can be a big deal for a system in production. It is best to get the column types correct up front. (For a corporation that has facilities spanning multiple time zones, the plain MS SQL Server datetime column type is a terrible design choice. Your client's DB admins should be chastized.)
I am probably going to get by using this conversion because I don't think I want to suggest changing the translator to datetime2 after years.
CAST(myDateTime AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATETIME2) AS test
I tested it in a union of dates during the winter and dates in the summer.
The conversions were correct for both with daylight savings.
Well this solution is not working.
Taking too long when using cast.
Trying to figure out a way to do it with convert which should be faster.
Cast the comparison values instead.
Instead of this (pseudocode):
SELECT ...
FROM ...
WHERE :startDate >= CAST(t_stamp)
This forces SQL to CAST every record in the table and it can't use the column's index.
This code only has to do one cast and then can use the column's index:
SELECT ...
FROM ...
WHERE CAST(:startDate) >= t_stamp
I don't know how exactly you would write this.
Thanks Transistor
That is a great idea for some of my queries, or maybe all my subqueries.
Under 1/3rd second for 12 max timestamps so far.
I wish it were faster though lol.
Ah I made a mistake. I think I saw pturmel about to post, and I spotted it.
CONVERT(datetime, SWITCHOFFSET(
case when DATEPART(HOUR, GETDATE()) < 7 then DATEADD(hh,-17,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0))
when DATEPART(HOUR, GETDATE()) >= 19 then DATEADD(hh, 7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0))
else DATEADD(hh, -5,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0)) end
, DATEPART(TZOFFSET,
case when DATEPART(HOUR, GETDATE()) < 7 then DATEADD(hh,-17,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0))
when DATEPART(HOUR, GETDATE()) >= 19 then DATEADD(hh, 7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0))
else DATEADD(hh, -5,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0)) end
AT TIME ZONE 'UTC')))
That's horrible. And there's a reason: You are converting your database column(s) to use with comparisons to various constants. Don't do that. Instead, convert your constants to be directly comparable to the bare column value.
If there are parts of your conditions that cannot be made to work that way, use a subquery to "prefilter" your source data using the comparisons that can be made efficient. Then the outer query can apply the more complicated WHERE clause(s) to just the rows that really matter.
With time-series data, it is especially important that comparisons to the t_stamp or equivalent column perform no conversions on that column.
(No, I didn't look close for mistakes, just commenting on the overall structure flaw.)
Thanks
150ms
I added in:
between DATEADD(dd,-22,GETDate()) and GETDATE() and
My convert statement above a couple posts did not work for converting to UTC.
When I use Getdate() I get the EST time.
When I apply the conversion to UTC, the time stays the same.
If I apply to convert to EST, then the date comes back wrong by 5 hours since it is winter right now in EST.
I need to rework my conversion technique on the constants that I derive from getdate().
I am prettty stuck as the db is deftinitely reading time system time in Eastern, but getdate() doesn't seem to have any tz. So ms sql keeps treating getdate() like it is UTC.
Adding the negative of the datedifference between getdate() and getDate() converted to EST won't work either.
Because then a shift that overlaps dst will be short an hour.
I had to add some at time zone 'Eastern Standard Time' because getdate() doesn't have a timezone and getUTCdate() has a UTC date.
Finally got it working with
, CONVERT(datetime, SWITCHOFFSET(
case when DATEPART(HOUR, GETDATE()) < 7 then DATEADD(hh,-17,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0))
when DATEPART(HOUR, GETDATE()) >= 19 then DATEADD(hh, 7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0))
else DATEADD(hh, -5,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0)) end at time zone 'Eastern Standard Time'
, DATEPART(TZOFFSET,
case when DATEPART(HOUR, GETDATE()) < 7 then DATEADD(hh,-17,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0))
when DATEPART(HOUR, GETDATE()) >= 19 then DATEADD(hh, 7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0))
else DATEADD(hh, -5,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0)) end at time zone 'Eastern Standard Time'
AT TIME ZONE 'UTC')))