# MariaDB/MySQL Time Zones with Ignition

**URL:** <https://forum.inductiveautomation.com/t/mariadb-mysql-time-zones-with-ignition/84034>\
**Category:** Ignition\
**Tags:** ignition81, perspective, scripting, vision\
**Created:** [January 10, 2024, 1:34pm UTC](https://forum.inductiveautomation.com/t/mariadb-mysql-time-zones-with-ignition/84034 "2024-01-10T13:34:16Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\
**Post date:** [January 10, 2024, 1:34pm UTC](https://forum.inductiveautomation.com/t/mariadb-mysql-time-zones-with-ignition/84034/1 "2024-01-10T13:34:16Z")

</div>

We have a project I'm working on internally that will allow me to set up servers however I would like and I'm using this as an opportunity to test and learn things at a deeper level. I know I'm super weak on time zones and their interaction with databases and Ignition/JDBC, so I'm using this to get things right and understand how time is stored in a database and then pulled through JDBC/Ignition so that they show properly to the client.

I know I can use Postgresql and much of this goes away, however, I want to make sure that I understand how things work so I can support existing and new systems that aren't so friendly to playing around.

I've got the different servers setup like this:  
MariaDB running on Ubuntu. MariaDB timezone is set for System, and the OS is set for Etc/UTC.  
Ignition 8.1.36 running on Windows, where the OS is set for EST.

I have a simple table to test creating new rows and inserting timestamps from different locations, and I'm not getting a consistent timestamp back.

The table is:

```auto
CREATE TABLE `tbltest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ts_client_insert` timestamp NULL DEFAULT NULL,
  `ts_current_timestamp` timestamp NULL DEFAULT current_timestamp(),
  `note` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

```

When new records are created, the ts\_current\_timestamp field is generated with the current\_timestamp from the MariaDB server and the time is correct according to UTC. However when reading that data into Ignition, or dBeaver, that column isn't converted from the UTC time to the time zone of client or gateway.

If I insert data from Ignition into the ts\_client\_insert column using the system.date.now() function, then that data is properly timezone adjusted if I change the client time zone. However, when looking at the column values, which I would assume to be the same or close they aren't.

In reading it seems that the timestamp column internally holds everything as UTC. If that is the case, then why when I insert data through JDBC that value doesn't match the current\_timestamp value from the database? Do I have a configuration wrong somewhere? Is this a functional misunderstanding from my end (most likely lol)?

---

<div class="post-metadata">

**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:** [January 10, 2024, 1:42pm UTC](https://forum.inductiveautomation.com/t/mariadb-mysql-time-zones-with-ignition/84034/2 "2024-01-10T13:42:40Z")

</div>

Sounds like a bug in the JDBC driver. I was under the impression that `timestamp` in MariaDB had similar behavior to PostgreSQL's `timestamptz`, while MariaDB's `datetime` behaved like PostgreSQL's non-tz plain `timestamp`.

---

<div class="post-metadata">

**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\
**Post date:** [January 10, 2024, 2:15pm UTC](https://forum.inductiveautomation.com/t/mariadb-mysql-time-zones-with-ignition/84034/3 "2024-01-10T14:15:02Z")

</div>

I'm running MariaDB JDBC 3.0.11. I'll update to the absolutely latest and see if it changes anything.

In the mean time, I assume that with the Database in UTC and the Gateway in EST, are there any further JDBC configurations that I would need to do just to double check?

---

<div class="post-metadata">

**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:** [January 10, 2024, 2:16pm UTC](https://forum.inductiveautomation.com/t/mariadb-mysql-time-zones-with-ignition/84034/4 "2024-01-10T14:16:12Z")

</div>

Does that JDBC driver offer a connection timezone override option in it connection extra properties? If so, try setting it to UTC.

---

<div class="post-metadata">

**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\
**Post date:** [January 10, 2024, 2:20pm UTC](https://forum.inductiveautomation.com/t/mariadb-mysql-time-zones-with-ignition/84034/5 "2024-01-10T14:20:56Z")

</div>

Yep

```auto
timezone	

permits to force session timezone in case of client having a different timezone compare to server. The option `timezone` can have 3 types of value: 'disabled' (default) : connector doesn't change time_zone. '<a timezone>': connector will set connection variable to value. see timezone consideration tp know more
Default: 'disabled'. Since 3.0.0

```

Source: [About MariaDB Connector/J - MariaDB Knowledge Base](https://mariadb.com/kb/en/about-mariadb-connector-j/)  
However this seems to mean that I need to put in something like America/New\_York to define that client timezone. I've tested that as well and it didn't change anything.

It also looks like Igntion might be appending serverTimezone to the connection string as well, but according to the documentation that is deprecated.  
I'll try setting both and see if that changes anything.

---

<div class="post-metadata">

**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\
**Post date:** [January 10, 2024, 2:24pm UTC](https://forum.inductiveautomation.com/t/mariadb-mysql-time-zones-with-ignition/84034/6 "2024-01-10T14:24:01Z")

</div>

Yeah... no change with the latest JDBC driver: 3.3.2

Data inserted with CURRENT\_TIMESTAMP aren't being properly timezone converted up to Ignition, however data inserted by Ignition using system.date.now() is.

---

<div class="post-metadata">

**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:** [January 10, 2024, 2:49pm UTC](https://forum.inductiveautomation.com/t/mariadb-mysql-time-zones-with-ignition/84034/7 "2024-01-10T14:49:57Z")

</div>

Very strange. /:

My gut says this is a MariaDB problem, not Ignition.

---

<div class="post-metadata">

**Author:** ![justin.brzozoski](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justin.brzozoski/32/13452_2.png) [@justin.brzozoski](https://forum.inductiveautomation.com/u/justin.brzozoski)\
**Post date:** [January 10, 2024, 2:56pm UTC](https://forum.inductiveautomation.com/t/mariadb-mysql-time-zones-with-ignition/84034/8 "2024-01-10T14:56:00Z")

</div>

In all my years of programming, timezone handling is one of the few places that I always assume there are still more bugs to be found.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [January 10, 2024, 4:52pm UTC](https://forum.inductiveautomation.com/t/mariadb-mysql-time-zones-with-ignition/84034/9 "2024-01-10T16:52:49Z")

</div>

> [@bschroeder](#):
>
> It also looks like Igntion might be appending serverTimezone to the connection string as well, but according to the documentation that is deprecated.

That actually sounds familiar... Like we did that deliberately to work around a bug with MySQL, I think around when they released MySQL 8.0.

---

<div class="post-metadata">

**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\
**Post date:** [January 10, 2024, 5:30pm UTC](https://forum.inductiveautomation.com/t/mariadb-mysql-time-zones-with-ignition/84034/10 "2024-01-10T17:30:00Z")

</div>

@paul-griffith  
Is there a way to see the actual full JDBC connection string that is being used to connect to a database? I'd like to just verify what things look like to make sure I'm not crazy.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [January 10, 2024, 5:53pm UTC](https://forum.inductiveautomation.com/t/mariadb-mysql-time-zones-with-ignition/84034/11 "2024-01-10T17:53:41Z")

</div>

No, it doesn't look like it, but I don't think you're crazy and I'm pretty sure I'm right.

If your DB is MySQL and your connection string doesn't include `serverTimezone`, we're automatically including it with the Gateway's default timezone ID:

```auto
if (field.equals(DatasourceRecord.ConnectionProps)
                        && record.getDriver().getVendor().equals(DatabaseVendor.MYSQL)
                        && val != null && !val.contains(mySqlTimezoneProp)) {

```

---

<div class="post-metadata">

**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\
**Post date:** [January 10, 2024, 6:00pm UTC](https://forum.inductiveautomation.com/t/mariadb-mysql-time-zones-with-ignition/84034/12 "2024-01-10T18:00:28Z")

</div>

hmmm... ok. Well then maybe there is something to this with using the Maria JDBC driver?

That is looking at this configuration parameter correct?

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/4/149a91acc843d374e52a8dcb73a50b780d8a4a42.png)

Is that something we can add a different Driver Type and then test to see if that additional parameter is making a difference? FWIW I don't think it is as I'm seeing the same issues when using a 3rd party database client, dBeaver (which uses the JDBC driver as well).

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [January 10, 2024, 6:04pm UTC](https://forum.inductiveautomation.com/t/mariadb-mysql-time-zones-with-ignition/84034/13 "2024-01-10T18:04:28Z")

</div>

You can try setting the driver type to `GENERIC`, which will defeat that logic I just posted and ensure that your connection string goes through verbatim. I don't know off the top of my head if that might affect other particular areas where we've got manual patches for buggy behavior.

---

<div class="post-metadata">

**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\
**Post date:** [January 10, 2024, 6:36pm UTC](https://forum.inductiveautomation.com/t/mariadb-mysql-time-zones-with-ignition/84034/14 "2024-01-10T18:36:54Z")

</div>

OK I'll take some time and test.

I do think that something might be amiss with my understanding of the timezone configurations in Ignition or somewhere along the line.

Something else I've noticed...  
If I use dBeaver, which uses the MariaJDBC driver, to insert and view the data in my test table, I'm getting the correct timezone applied data for all the data EXCEPT for the data inserted by Ignition. I'm able to add the timezone=America/New\_York and I get the data, current\_timestamp, entered data etc... all in that time zone. When I change the timezone to America/Chicago, the timezone is applied and the data is shown as proper time in that time zone.

It looks like _maybe_ Ignition is ignoring the timezone=America/New\_York as part of the connection properties.

This is what I'm specifically using in the extra connection parameters for this datasource:  
connectTimeout=120000;socketTimeout=120000;timezone=America/New\_York

I've tried GENERIC and MySQL types and neither of them are changing the behavior in Ignition.

So the more that I'm playing around with this I'm wondering if there is something in Ignition that I'm not understanding, or isn't quite right.

Edit:  
I've also taken a look at the @@session variables as well between dBeaver and Ignition. In dBeaver if I query the @@session.time\_zone variable, select @@session.time\_zone;, I get the same time zone as specified in the JDBC connection property timezone. However, when I run the same query in Ignition I get SYSTEM rather than the supplied timezone. So something funky is going on.

---

<div class="post-metadata">

**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\
**Post date:** [January 15, 2024, 1:12pm UTC](https://forum.inductiveautomation.com/t/mariadb-mysql-time-zones-with-ignition/84034/15 "2024-01-15T13:12:34Z")

</div>

Update.

After talking with support and doing some testing, this is what I've been able to get working.

MariaDB Database in UTC mode and set to the correct time.  
Ignition Gateway in a time zone other than UTC, in this case, it is in America/New\_York or EST.

With MariaDB JDBC Driver version 2.7.11 I was able to get the timezone to offset by using these extra connection parameters:

```auto
connectTimeout=120000;socketTimeout=120000;rewriteBatchedStatements=true;serverTimezone=UTC;useLegacyDatetimeCode=false

```

In addition, I was able to keep the Driver Type as "MYSQL":

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/3/53c94c05d56dd3e6c75d634469a3960e6a2d95d8.png)

With MariaDB JDBC Driver version in the 3.X.X version specifically 3.3.2 I was able to do the following to get things to work.

Change the driver type to "Generic":  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/7/f7e74d712a5f6068af7bd7a68b511d56c36ef945.png)

Then add/change the extra connection parameters to look like:

```auto
connectTimeout=120000;socketTimeout=120000;rewriteBatchedStatements=true;timezone=America/New_York

```

Additionally, before I changed the driver type to Generic for the 3.x versions, it seems that Ignition is setting parameters on the JDBC connection that aren't valid. I would get this exception when trying to query the database using a system.db.XXX function. Named queries and query bindings seemed to be operating ok.

Exception shortened, I can post the entire stack if anyone is interested.

```auto
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: SQL error for "SELECT * from tblemp": (conn=211) invalid fetch size
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:360)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:334)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:287)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runLimitQuery(GatewayInterface.java:893)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runQuery(GatewayInterface.java:859)
	at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runQuery(ClientDBUtilities.java:336)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runQuery(AbstractDBUtilities.java:350)
	... 24 more

```

At the end of the day, it looks like the newer/more recent MariaDB JDBC drivers and Ignition don't want to play well. I can do what I need to do with the older version of the JDBC driver. However, I think that IA might want to work on bringing compatibility with the newer MariaDB JDBC drivers and Ignition.