[BUDI-6848] Ignore Time zone with existing date/time field on MS SQL Server

[melohagan]

Discussed in #10259

^{Originally posted by lanedsmu April 11, 2023}
Hi all,

I believe I understand that the "ignore time zone" option is available for some DB platforms but not others (and possibly not always available for those we'd expect?).

We have a lot of MSSQL tables with existing date/time data without timezone offset, and it appears Budibase always treats these as UTC. Is there a short-term way for us to handle this without utilizing queries to add the offset?

Thanks much! -Lane

_BUDI-6848

[melohagan]

Hey @lanedsmu

Going to close this one because there's a limitation with Knex (our SQL connector library) that prevents us adding the Ignore timezone option for MS SQL Server (and Oracle).

You can read more here: #6206
Look for the Details around limitations section.

What you may be able to do to work around this is ensure that the docker container has the same timezone set as your SQL Server.

There is a TZ environment variable that can be set to accomplish this: https://www.howtogeek.com/devops/how-to-handle-timezones-in-docker-containers/

[lanedsmu]

Thanks @melohagan. Do we know that setting the TZ value in the container appropriately will have Budibase assume date/time values (without TZ info) is from the local timezone? We added the TZ var to the docker yaml files and rebuilt our Budibase container, but it's still displaying existing datetime fields as though they contain UTC dates.

Put another way, if I set the TZ info in the container, and I'm still getting UTC-assumpttions about existing data, does that mean that the workaround doesn't actually work, or does it mean we've done something wrong with the TZ settings in the container?

-Lane

[melohagan]

@lanedsmu Argh timezone stuff is always tricky.

Those dates that have already been saved will remain as they are. You may need to update them manually with a query.

With the TZ variable set, it should save future data records in UTC with the correct offset. If my understanding is correct.

[lanedsmu]

Ah, we may be discussing different problems: I'm not doing data entry with this, but rather displaying existing data that doesn't have a TZ offset. Is there a way for me to get Budibase not to assume that's utc (without putting a query on front of it)?

Thanks again.

[melohagan]

Hey @lanedsmu When it comes to displaying data, then it may be dependent on the TZ of the web-browser as well.

Instead of using a query to offset the time, you could also add a Formula column for display purposes that does the offsetting if that helps? https://docs.budibase.com/docs/formula

[lanedsmu]

Thanks again @melohagan. I'll have to do some more digging and get better at using this interface; we'll see what we can come up with.

I'm left with a nagging concern, though, that I'll never be able to create an app reasonably trivially that'll just handle dates as they exist in the database.

I think I see what you're talking about with the problem presented by the connection library. If I do a simple select query in Budibase and manually change the schema to specify text as the datatype for a datetime field, the data is returned somewhat altered from what's in the database:

That Z timezone designator isn't a part of the data (and the DB server isn't in UTC). Though it's noteworthy that the concrete date itself is correct, excluding the UTC designator.

I've been imagining lots of ways we could use Budibase to monitor and delegate control over technical processes throughout our org, but all of that involves displaying dates from a variety of sources, none of which we'd have control over, in terms of formatting. I don't yet know how to handle that, apart from utilizing SQL queries for everything (which might be fine...?).

Is utilizing queries the right way to go, in terms of least friction for implementation? Given the use-case: querying date data that is historical and not under our control in terms of formatting (read: no TZ offset), what would you recommend as the easiest method to work around this problem?

-Lane

[lanedsmu]

@melohagan, I've done some more digging on this (if a discussion is a better location than this closed issue, I'm pleased move back to that venue).

I believe Knex is using the Tedious js libraries for MSSQL, and those do support a "options.useUTC" connection setting. If my reading of this is right, this changes the default behavior of the js library, such that it doesn't assume non-tagged dates are UTC.

Are connection string settings exposed anywhere in a Docker deployement of Budibase (or elsewhere)? We'd be very pleased to try this out and see if it makes a difference...

[melohagan]

Hey @lanedsmu Thanks for digging into this further. I tried setting that useUTC setting to false but it didn't make any difference that I could see.

[lanedsmu]

Thanks @melohagan; that was fast! Can you point me to where that is? I'd like to experiment some more with possibilities on this front.

[melohagan]

Certainly 😄 @lanedsmu

Here is the connector: https://github.com/Budibase/budibase/blob/develop/packages/server/src/integrations/microsoftSqlServer.ts

I had tried simply adding the useUTC flag to false in the options object of the config.

The contributing readme is here: https://github.com/Budibase/budibase/blob/HEAD/docs/CONTRIBUTING.md

[lanedsmu]

hi @melohagan. I've made progress, though I (like you) tried modifying microsoftSqlServer.ts, and I didn't get anywhere.

Instead, I modified the useUTC setting in /app/node_modules/tedious/lib/connection.js from the default (true) to false.

and that worked marvelously.

Before that was updated:

After the useUTC change hard-coded in connection.js

Which is the actual set of values stored in the database.

and this appears to work with queries as well as with direct-to-table connections.

We obviously generally don't want to hard code values like that (though true /is/ hard-coded in connection.js, and hard-coding it as false is exactly a correct outcome for our use cases here). I'm not familiar enough yet with these libraries or Budibase UI configuration yet to suss out exactly how best to address the problem as a product offering.

[melohagan]

Hey @lanedsmu Thank you very much for digging into this. I have also found this issue, so there may be a bug with the MS SQL library we are using: tediousjs/node-mssql#1478

[lanedsmu]

Absolutely. It does seem as though we should be able to pass this config value on through the connection as a config object (see Tedious readme), but I'm clearly doing something incorrectly in my modifications of microsoftSqlServer.ts... I'll keep hammering at that.

Reading that issue does make it sound like there's some data consistency concerns that they're working on, doesn't it? In any event (assuming that gets fixed), do you expect the "right" solution here is to surface that setting through the data connection configuration in Budibase?

[melohagan]

I may be understanding the issue incorrectly, but from what I can tell, the setting is being ignored when it shouldn't in the tediousjs node-mssql library that we use.
Therefore we need to wait for a fix in the library, then we can update the version in Budibase and simply pass the setting through in the config of microsoftSqlServer.ts.

Edit: recent comment here: tediousjs/tedious#1528 (comment)