$usetimezone with ODBC DAM and SQL Server

Doug Easterbrook doug at artsman.com
Tue Jan 23 17:46:54 UTC 2024


ok. so here’s another idea for you — which we also do in postgres, which is the clause ‘at time zone’

as in

select myTimeStamp at time zone ‘America/Denver’ from …..



we have over ridden all our select statements within a table class to include this so that it will return what we want at the timezone we want.     We did that for background postgres workers in studio 5.2 ….. because you only got 1 statement in background workers in studio 5.2.

Since studio 8 and later allows multiple statements in a background workers, for postgres people, you can include the ’set time zone’ directive in your first sql statement for the background worker.


so, back to ‘at time zone’ for mssql and the purpose of it.    You get to return all the dates and times at the timezone you want in the select statement by explicitly stating it.


The crux of the problem is the problem is updating things at the right time zone.   I have not investigated ms-sql to see ramifications, but it seems you can do

update table set. mystimestamp= [@ mystimestamp]  at time zone ’name of time zone’




all this comes back to the question ‘’ how to do timestamps’. 

My preference would be 

for the entire ’session’. (postgres = yes, ms-sql - can’t do it)

then

use database primitives and functions (postgres=yes, ms-sql has them to) so that you can deal with ALL valid time zones in a generic solution.  if the timezone changes its rules, then you are isolated because it happens at the database.    Example: there is much talk in the USA of staying on daylight savings time.   if that happens, Canada is right behind and many provinces have passed laws to go as soon as USA goes that way.


That way, the implementation is to update Ms-Sql server (to get latest time zones) and everything works from your single source, along with windows updates.

it also handles 1/2 hour timezones (like newfoundland) and others that never change duringthe year (I think Arizona is that way).



final choice is omnis which gives you 24 time offsets.  and if time zone changes, you may have to rewrite how you handled timezones in your app, plus deploy to everybody.  and it doesn’t do 1/2 hour time zones.     If you have people in Arizona (which doesn't change) and California (which does change twice a year), then your app has to deal with two things manually
— WHEN the time changes has to be coded into your app
— HOW it changes also has to be coded into your app.



Ultimately, I think timezones should be handled by database functions within SQL because it is explicit to the real time zones which deals with both of the above questions at the database level.


just my $0.02








Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978

> On Jan 23, 2024, at 6:45 AM, Thad Bogert <thad at technosolver.com> wrote:
> 
> Thanks Doug,
> 
> From what I can tell, there is no session based time zone settings for SQL Server. Just functions to convert values.
> 
> 
> 
> 
> 
>> On Jan 22, 2024, at 7:14 PM, Doug Easterbrook via omnisdev-en <omnisdev-en at lists.omnis-dev.com> wrote:
>> 
>> Hi Thad.
>> 
>> I tried this with Postgres and it was not a good combination   
>> 
>> To also it right, we changed all our timestamp field to timestamp with time zone in postgres
>> 
>> Then the right way to use time zone in Postgres was to issue a sql command
>> 
>> Set time zone ‘my tome zone’ where my time zone is any one of the 500 odd time zones supported by the database 
>> 
>> Made any time zone math easy. In that we didn’t need to do anything else with our application.   Everything worked 
>> 
>> Each connection can have a different time zone and you can set your servers to utc or what ever you want
>> 
>> 
>> So. With ms sql server, if you have the same command (I assume you will), then do that instead of using $timezone
>> 
>> 
>> $timezone only deals with 24 time zones. You are responsible for dealing with daylight savings time, including those that are 30 minutes off (like Newfoundland)
>> 
>> Makes automatic time zone change not an issue
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> ------------
>> Doug Easterbrook
>> Arts Management Systems Ltd.
>> doug at artsman.com
>> 
>>> On Jan 22, 2024, at 1:38 PM, Thad Bogert <thad at technosolver.com> wrote:
>>> 
>>> Hi All,
>>> 
>>> Has anyone had the opportunity to use the timezone conversion properties in the ODBC DAM?
>>> 
>>> I am curious about how effective it has been.
>>> 
>>> The current documentation for the feature is a bit sparse. It just states the following:
>>> 
>>> If kTrue, $timezone will be applied to values inserted and fetched from TIME & TIMESTAMP columns, that is; datetime values are subject to modification by comparing the local timezone with the server’s timezone. Applies to MS-SQL Server connections only.
>>> 
>>> If it truly converts all time and timestamp values going in and out, it will save us considerable time for readying our application for use in multiple timezones.
>>> 
>>> 
>>> Thanks,
>>> 
>>> Thad
>>> _____________________________________________________________
>>> Manage your list subscriptions at https://lists.omnis-dev.com
>>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
>> _____________________________________________________________
>> Manage your list subscriptions at https://lists.omnis-dev.com
>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
> 
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com



More information about the omnisdev-en mailing list