|
I'm using SQL Anywhere 17 and have a timestamp (without time zone) which I know is UTC, and I know that my client's browser reports a timezone of "Europe/Berlin". Is there an easy way to convert the UTC timestamp using SQL? Let's assume that I have already created the timezone with CREATE TIME ZONE "Europe/Berlin" OFFSET '01:00' DST OFFSET '01:00' DST STARTING 'Mar/last Sun' AT '02:00' DST ENDING 'Oct/last Sun' AT '02:00'; I've not yet found a system function to do that. Doing it manually using SYSTIMEZONE is quite some work, with the risk of overlooking one of the special cases... |
Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- numbered list: 1. Foo 2. Bar
- to add a line break simply add two spaces to where you would like the new line to be.
- basic HTML tags are also supported
Question tags:
question asked: 23 Dec '21, 05:14
question was seen: 3,622 times
last updated: 05 Jan '22, 06:54
Have you considered DateAdd( minute, CONNECTION_PROPERTY( 'TimeZoneAdjustment' ), utctime )? The connection property TimeZoneAdjustment reflects the current adjstment between the client timezone and UTC.
Yes, it is the current adjustment. So it only helps me if I want to convert the current timestamp, but not any timestamp in the past or the future. Of course the problem is not adding the minutes, but finding out whether DST is active at the given UTC timestamp or not.
Have you tried TOLOCALTIME(utc_ts)? The most recent version of 17.0 have this function. https://help.sap.com/viewer/93079d4ba8e44920ae63ffb4def91f5b/17.0.01/en-US/4177e836841a40288a290d73953261b9.html
I should point out that TOLOCALTIME works outside of the virtual time zone feature. It's primary use is to convert UTC time to the server's local time (it's "real" local time). It can also convert TIMESTAMP WITH TIME ZONE values to local time. I'm not sure from your post how important the "CREATE TIME ZONE" aspect is to your problem but it doesn't apply to TOLOCALTIME.
Yes, this looks promising, though the inability to use other time zones than the server's is a bit of a drawback.
Does someone know in which version this function is implemented? We are using 17.0.10.6160 and it tells me that the procedure is not found...
It was added in 17.0 build 6171.
Do you realize that a virtual time zone, if selected using the time_zone option, is database wide? The time_zone option has only a PUBLIC setting. You cannot set it per connection. So if you have a user in Berlin and another user in New York, you won't be able to set virutal time zones for both. However, the setting can be different for other databases running under the same server.
Yes, at least I did assume so.
Therefor I would prefer a function which takes a time zone name (referring to SYSTEMTIMEZONE) and an UTC timestamp and returns the timestamp in the given time zone. Or at least the difference to UTC for the time zone at the given timestamp.