1. Mark Jones
  2. PowerBuilder
  3. Monday, 24 February 2020 23:02 PM UTC

We are planning on use the SQL Anywhere dbremote program to maintain two copies of the same database on servers in two different timezones.   We have a number of timestamps in our database and currently none store the timezone.   It is very easy to change various of our columns from "timestamp" to "timestamp with time zone" (UTC)  however it does not look like PB 2019 datawindows handle the timestamp with timezone data type properly.   We have a large number of datawindows and a lot of code that work with these timestamps that need to be switched to UTC but it does not look like we can just switch over to these UTC timestamps in the database without a significant rewrite.

 

Does anyone have any experience dealing with this situation and can you offer any advice?

 

Thanks,

 Mark

Roland Smith Accepted Answer Pending Moderation
  1. Tuesday, 25 February 2020 14:15 PM UTC
  2. PowerBuilder
  3. # 1

To get the current time from your computer in UTC you have to use the Windows function TzSpecificLocalTimeToSystemTime.

I have examples in my BCrypt and CryptoAPI examples.

https://www.topwizprogramming.com/freecode_bcrypt.html

 

Comment
  1. Mark Jones
  2. Tuesday, 25 February 2020 14:31 PM UTC
Thanks Roland. Actually I do not care about the local computer date/time, just the database server date/time. Just to re-iterate the problem - we are planning on replicating a database in 2 different geographies (Germany (Master)/Australia(Child) ) using SQL Remote. This is working beautifully but we need to store consistent audit trail in the database so that we can look at entry/datetime irrespective of location of data entry. We have a lot of code relating to various timestamps that currently use the database server date/time. It looks like it would be too much work to switch our various timestamp columns to timestamp with timezone therefore we need to convert the timestamp entered to a consistent format ourselves. The easiest solution would be to set the date/time of the Australian database server to the German timezone. This would require no coding on our behalf! However our concern is that the datacenter hosting the database server might have a problem with this since it might affect monitoring jobs etc that they are running. So we are hoping to come up with a way to find out the true time difference between Germany and Australia at run time (automatically taking account of daylights savings) so we can adjust date/times by that offset. SQL Anywhere has the function SwitchOffset but I do not think it is daylights savings aware. Germany and Australia switch their daylights savings on different dates and we could manage our own table with the various switch on/off date/times but was hoping this was available somewhere already. Thanks, Mark
  1. Helpful
  1. Mark Jones
  2. Tuesday, 25 February 2020 14:38 PM UTC
One thought I had was to create a small table in the master and child databases that contains the current database time UTC offset in minutes. I would then have an event on the master database only that fires every hour (at 1 minute past the hour) to check if the value has changed and update the value. Because of the replication this data would then find its way down to the child database and give me the UTF offset for the master so I could make the adjustment.
  1. Helpful
  1. Michael Kramer
  2. Tuesday, 25 February 2020 18:07 PM UTC
Mark, one of SQL Anywhere's database options is to simulate a different time zone than the server is running.

So physical server or VM running in Australia remains configured for local time zone. Difference is that you can define database's "internal = simulated" time zone as CET (Berlin + Copenhagen + ...) . SQL Anywhere will internally do conversions in-flight. - - that's how I understand SQL Any's docs.
  1. Helpful
There are no comments made yet.
Mark Jones Accepted Answer Pending Moderation
  1. Tuesday, 25 February 2020 14:00 PM UTC
  2. PowerBuilder
  3. # 2

Thanks again ,  this is useful stuff and is of great help. I appreciate all your help with this!

 

Mark

Comment
There are no comments made yet.
Michael Kramer Accepted Answer Pending Moderation
  1. Tuesday, 25 February 2020 03:36 AM UTC
  2. PowerBuilder
  3. # 3

Whatever works "best" = most robust in your environment!
You own the support burden; I don't ;-)

Anyways, TEST your desired time zone to ensure it is viable setup.
Rather fail fast than delay testing and find out you went down the wrong rabbit whole.

And yes, audit trail requires you can somehow sort log entries in correct chronological order.
TEST: Hit some test button simultaneously on two PCs to compare timestamp
You may be surprised how much two PCs may differ.

// EVENT = cb_Test.Clicked
MessageBox( 'Local Clock', &
   String(Today(), 'YYYY.MM.DD') + '~r~n' + &
   String(Now(),   'HH:MM:SS.ffffff')


I often end up choosing DB server as "clock source" to have sufficiently trustworthy clock.
I just fired below SELECT at my local SQL Any 17 to gauge the different clock sources. 

SELECT Test.Data, Test.Value
FROM  (SELECT 1 "#", 'GetDate' "Data", CAST(GETDATE() AS VARCHAR) "Value"
 UNION SELECT 2, 'Now',                CAST(NOW() AS VARCHAR)
 UNION SELECT 3, 'Current Stamp',      CAST(CURRENT TIMESTAMP AS VARCHAR)
 UNION SELECT 4, 'UTC Stamp',          CAST(CURRENT UTC TIMESTAMP AS VARCHAR)
 UNION SELECT 5, 'TimeZoneAdjustment', CONNECTION_PROPERTY('TimeZoneAdjustment')) Test
ORDER BY Test."#";

Data looked like below (same value! and TimeZoneAdjust matches current time zone in Windows O/S config)

Enjoy, /Michael

Comment
There are no comments made yet.
Mark Jones Accepted Answer Pending Moderation
  1. Tuesday, 25 February 2020 00:42 AM UTC
  2. PowerBuilder
  3. # 4

Michael, thank you for the detailed reply.  I feared we might have to add code.

 My other thought is to stay with standard timestamps but correct the date/time using the timezone offset of the primary database server, rather than that of the remote server.  This way all the audit trail is relative to the primary server and makes some sense.  This is primarily for auditing data, created timestamp , last modified timestamp etc.. This is not quite as elegant as your solution but probably safer and quicker to implement.

MARK

Comment
There are no comments made yet.
Michael Kramer Accepted Answer Pending Moderation
  1. Tuesday, 25 February 2020 00:26 AM UTC
  2. PowerBuilder
  3. # 5

You're right. PowerScript (and the DataWindow engine) has neither concept of UTC nor time zones. So this requires extra code!

For SQL Anywhere pick DocCommentXchange and search for "TimeZoneAdjustment". When I read docs that connection property seems to be able to adjust datetime values to client's local time zone even though database internally uses different time zone (EX: UTC).

I advise to use UTC as much as possible when you do date/time calculations and comparisons. It is practically impossible to handle all daylight savings differences and all time zones unless you follow algorithm like:

  1. Convert each local zone INPUT value => UTC value
  2. Perform calculations, comparisons; etc.
  3. Convert each UTC OUTPUT value => local zone

It may even be multiple different INPUT zones and OUTPUT zones. Pure UTC calculation will be free from any daylight savings adjustments.

 

HTH /Michael

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.