I am trying to make it so that all of the datetimes in SQL are stored as UTC time, but displayed as local time in the smart/webclient. So far the two implementations of this I have been able to think of are:
1: Storing: Set the application server time to UTC.
Displaying: Set dataprovider on fields that should show datetimes to a calculation that adds the current client’s offset to the UTC time before displaying it.
2: Storing: Use method converter to remove offset from each datetime before it is stored in the table.
Displaying: Use method converter to add offset back to each datetime as they are pulled from the table to be displayed.
I don’t really like either option, but I am leaning more towards option 2. I would like to hear some of your solutions to this problem and hopefully gain some valuable insight.
Thank you,
Peter
Check the “servoy.use.client.timezone” property.
I think with that option, if you set the app server to be UTC, the clients will adapt automatically.
Hmm… okay. I can see how that would be a better option than converting many times displaying fields.
So another question. Is there a way to change the Servoy Application Server timezone without updating the time zone of the server that it is running on?
I’ve had some experience with making my Servoy apps work for users in multiple timezones. In addition to what you are talking about, I have succesfully got Servoy to show users in one timezone timestamps that are local to another timezone (e.g. someone in England looking at timestamps of events that took place in New York, displayed in New York time) independent of what timezone the Server is in.
My first question is why do you want to store the times in UTC? By that I assume you mean you want them to be stored with UTC offset = 0. Is your app server on the same machine as your db? If so, I have found that when reading in timestamps from the db, as long as the timezone of the server machine is the same as it was when those timestamps were written, it doesn’t matter what that offset is, Servoy will correctly take it into account. So I am wondering why you want the timestamps to be in UTC-0. Is it because there are non-Servoy apps hitting the db that insist on the times being in UTC-0?
If all you want is for timestamps to get converted to the user’s timezone, I have found (and I know this is counter-intuitive) that you want to set ‘use.client.timezone’ to false. If you set it to true, I have found that Servoy doesn’t convert the time to the user’s timezone, it just blindly imposes the users’ UTC offset on any timestamp it reads in from the db. So if the db holds a timestamp of 10:00 UTC and the servoy client machine is in UTC-7, the Servoy client will display that timestamp as 10:00 UTC-7. I didn’t see how that could be useful but that is what I observed it doing.
When I set ‘use.client.timezone’ to false then Servoy did the correct conversion on the time and displayed it as 3:00 UTC-7 for the user in the UTC-7 timezone.
If you still decide you want your timestamps stored in the db in UTC-0 time, then I think all you’ll need to do is set the server machine’s timezone to UTC-0 timezone BEFORE you start writing timestamps, and with ‘use.client.timezone’ set to false everything should work without you needing to use any calcs or method converters.
I don’t know of a way to set the Servoy Server’s timezone separately from the timezone setting of the OS it’s running on. Java gets its timezone from the OS.
I should mention that this is all based on behavior in Servoy 5.x and Sybase SQL Anywhere running on Mac OSX. I have not tested it in 6 or 7 or other databases. I hope this is helpful