Date/Time from SQL Database in Perspective

Ignition
1

I have an Ignition Cloud application running currently. I'm adding a function that will allow a user to add, store, and retrieve text notes. I'm using a SQL database for this. I have all that functionality working, but the main issue is one of the columns I'm getting is a timestamp. First off, here is my named Query called "Logbook/Get":

SELECT Logbook.ID,
  Logbook.[Timestamp],
  Logbook.Subject
FROM Logbook
WHERE Logbook.Site = :site

Here is what my result looks like:

image
image664×921 14.5 KB

Also, here is my query that inserts into the table:

INSERT INTO
	Logbook
	(Site, Submitted_User, [Timestamp], Subject, Notes)
VALUES
	(:site, :user, CURRENT_TIMESTAMP, :subject, :notes)

I'm passing the site, user, subject, and notes parameters when I click the button. Again, that's all working.

The issue is my timestamp. When I log something into the table, the CURRENT_TIMESTAMP it applies is UTC time, which is actually fine, not a surprise. The Cloud server is set to UTC. Log entries are going to be coming from multiple sites, all over the world (hence the WHERE clause where I'm filtering by site), so I think it's a good idea to just keep all those entries at UTC on the database itself.

But is there a way when I query the data to bring that timestamp in as whatever my local session time zone is? I've been searching all over and I don't see a really straightforward solution, but everything I found online is how to convert to a static, known time zone. I need the conversion to be dynamic based on the location of the session. I've seen a bunch of stuff for AT TIME ZONE in SQL, but that requires a verbose time zone name that isn't generated the same way by Ignition, so I can't just pass the session property for the device's current time zone and be done with it. I suppose what I could do is make a custom session property that does a giant map transform, taking all the Ignition timezone names and converting them to SQL timezone names. That seems like a lot of work, and I feel like I'm missing something because I find it really hard to believe I'm the first person to ever need Ignition and SQL to work with time zones dynamically. So I'm hoping I'm just missing something.

2

Main menu | Project | Project Properties | Perspective General | Project Timezone = Client Timezone should fix it.

3

Is this a MS SQL Database or some other flavor of DB?

What is the column type of the Timestamp column (also, just because I'm feeling pedantic, it's generally considered poor practice to use reserved words as column names).

1 Like
4

Building on what lrose asked, if you are using MSSQL, you need to use DATETIME2 type for your timestamp, as it is timezone aware.

1 Like
5

Should I see it change in the Designer or do I have to actually run a Session?

6

The column type is datetime

It's an Azure SQL database

7

If you use the datetime column type in the DB, and the DB is running in UTC, just set your gateway to also run (entirely) in UTC. The datetime objects exchanged between Ignition and the DB will all be UTC, and Ignition will propagate correctly to all clients in whatever project timezone you require. Or dynamically based on the client.

Ignition uses the timezone of the gateway as the connection time zone for all traffic. The object type in Ignition then carries UTC epoch milliseconds everywhere, for local interpretation.

8

Thanks for your help! When I ran a session all the times in the table showed my local date time while in Designer it was UTC. I made the changes suggested by Transistor and lrose.