Remote SQL with different timezones for each user?

[EzLogic]

(I posted this on another fox..forum)
forgive me..

I am developing a new VFP9 / sp2 application that is running on SQL Server 2012, which is hosted in a data center.

The data is remote hosted at data center/hosting company.

the Client will install the app on their local computers/network and run the app and access data remotely. App is local to the user.


However, many of the users will reside in different time zone. (eastern, center, pacific, mountain, etc..)

Currently, alot of my "AddDate" fields are being stored as GetDate(), and it just hit me.

So, i Binged (not googled lol) different time zones etc.. and i came to GetUTCDate() which is the GMT date.

Then, in the Application configuration of each Store's profile (each store is an installed application), I ask their timezone and than the display of all the dates (ouch), will have to be the offset difference GMT-5 or -4 or -8 etc..

How did/would you handle that?



Ez Logic
Michigan

 

[Olaf Doschke]

SQLServer has some specific types and functions to handle this, since at least SQL2008.
See this blog article for example:

http://blogs.msdn.com/b/sqlprogramm.../using-time-zone-data-in-sql-server-2008.aspx

So along the lines I'd store a UTC time and a datetimeoffset, which indeed might come from a user profile, but there are also system functions giving you the offset configured in windows settings, you don't have to ask that from the user, provided he set its region correctly.

See here for functions of interest:

http://technet.microsoft.com/de-de/library/ms178635.aspx

I am actually using both getdate() as default and DATETIME() on the VFP side for an UpdatedAt column. That indeed gives wrong values in datetime columns of users in different time zones using the database remotely or via TS.

Ideal handling would always split a datetime in a UTC datetime value and an offset. It may never bite you, if this is not essential info or does not trigger something at a certain wrong time of a date, eg deadlines or appointments. Indeed a date without a time may be sufficient for local whole day appointments and not need a time portion or offset. The same date of course would mean a different timespan from the point of view of the timezone differences, but that would matter to people attending in a video conference only. I'm saying see through the consequences this has and if they are of interest at all, before spending time in changing the application to take time zones into account. Even an application used in different locations may not need the offset to work correctly.

What could bite you indeed is sometimes using getdate() from the server side and sometimes inserting or updating with VFP DateTime() values having the time zone shift to the sql server.

Bye, Olaf.

 

[BillPSU]

If your time stamp must be based on the local time you will have issues with people traveling. Someone from New Yord travels to California with a laptop. How do you control the time stamp?

Bill

 

[Olaf Doschke]

The laptop would accomodate, if a time server is not only used to sync system time, but to also automatically set region/time zone by IP or GPS?

At least it would be the responsibility of the travelling user to configure the locale.

One thing is for sure anyway: Storing the main datetime in UTC will always be a solid ground, only the time zone offset can be wrong for travelling users.

Bye, Olaf.

 

[EzLogic]

No traveling. They are retail stores



Ez Logic
Michigan

 

[EzLogic]

So, in my store's location profile, if I have the timezone offset from GMT, example, if the location is Eastern timezone, the offset is: -5 (GMT-5)

1- I store the date/time fields in GetUTCDate() && gives me GMT time
2- When store logs in, i get their nOffset, in this case -5, and I store it in my goApp.nZoneOffset
3- in my queries or sql statements, i always will have to do this:

text to sql noshow 
select InvNo,CustNo,InvAmount,dateadd(hh,?goApp.nZoneOffset,InvDate) as InvDate 
   from ARMast where fk_Store = ?goApp.nfk_Store
endtext

goSQL.Execute(lcSQL,'myCursor')

Thoughts?

Ez Logic
Michigan

 

[JRB-Bldr]

One simple thought would be to change the ARMast field name, since you are using UTC DateTime, from InvDate to InvUTCDate so that you could more readily differentiate between field values (Invoice @ UTC DateTime and Invoice @ Local DateTime).

Assuming that goApp is a Local data table (VFP or SQL Server) I would acquire the ZoneOffset and nfk_Store values before attempting to run the SQL Query.

Then I'd use something like the following:

* --- Execute either to VFP data table or, via SQLEXEC(), to Local SQL Server ---
SELECT ZoneOffset, nfk_Store FROM goApp WHERE <if selection criteria required> INTO CURSOR StoreParams 
SELECT StoreParams
nZoneOffset = StoreParams.ZoneOffset
nStoreNo = StoreParams.nfk_Store
USE

* --- NOTE - Since the above are FIXED Parameters for the individual store, the values need not be acquired EVERY time you want to query the SQL Server ---
* --- Getting the values Once upon application launch and then storing them to GLOBAL variables or to Form Properties, etc. a single time will make them available for use any time within the application ---

* --- Build SQL Query String ---
DIMENSION arySQLCommand(1)  && Using an Array eliminates any possible String Max Length issues
arySQLCommand(1) = "SELECT InvNo,CustNo,InvAmount,dateadd(hh," + ALLTRIM(STR(nZoneOffset)) + ",InvUTCDate) as InvDate ";
  + "FROM ARMast WHERE fk_Store = " + ALLTRIM(STR(nStoreNo));
  + <whatever other selection criteria needed>

* --- Execute SQL Query String via SQLEXEC() ---
*  Maybe you could do this with your  [b]goSQL.Execute()[/b]  but not knowing what it is or how it might work, 
*  I'll describe the 'generic' approach
*
nConnHandle = SQLCONNECT([cConnectionName | cDataSourceName [, cUserID [, cPassword ]][, lShared]])
nRet = SQLEXEC(nConnHandle, arySQLCommand(1), 'Results')
IF nRet = 1
   SELECT Results
   <do whatever>
ENDIF
=SQLDISCONNECT(0)  && Close ALL Connections

Good Luck,
JRB-Bldr

 

[EzLogic]

Thanks JRB-Bldr,

Yes, in sql server, I have a stores table. so, when the user logins in, in my stored procedure, I obtain the settings for the store, and in my VFP app, I store them in a public object, I call it "goApp".

so, as soon as the app launchs, I already have static settings for the users,
such as:
TaxRate
nfk_Store
StoreInformation (Address, city, state, phone,etc.. etc..)
nfk_User (iid of the user)
nTimeZoneOffset (just added it)
plenty other of properties that are static and I use them..
etc..etc..

so at any point in the app, I always can get the values from: goApp.fnk_Store, goApp.cStoreID, goApp.nTimeZoneOffset, goApp.nfk_User, goApp.cUserID, goApp.etc.. etc..

and you are right, in my query, I use those variables...

I have my own SQL wrapper class, goSQL, which handles most, if not all, the sql connections, queries, parameters, etc...

To you point, I like the idea of calling the date fields with the UTC in them, so anyone would know that they are UTC based date/time...





Ez Logic
Michigan

 

[Olaf Doschke]

>in my queries or sql statements, i always will have to do this:

To go back to that qustion: Yes, of course.

You store UTC time to have a time zone independent reference value, therefore you have to compute a local time, like you have to compute other locale specific things. That's the price you must pay.

The new SQL Server datetimeoffset field type also does not free you from that need. It just stores a datetime in a way having a UTC portion and a time offset. This field type enables you to store a locale time and still be able to retrieve the UTC time from it, as the local time is stored in two parts with the UTC time and an offset. But it only would free you from the dateadd calculation at the time zone the value came from.

So indeed it's easier if not making use of being able to store a locale time, instead simply store the UTC datetime and always do the timezone calculation, and that can also be done with a normal datetime field. What really helps you getting at the UTC time is the getUTCDate() function.

Bye, Olaf.