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...

asked 23 Dec '21, 05:14

tedfroehlich's gravatar image

tedfroehlich
115129
accept rate: 0%

edited 23 Dec '21, 05:18

1

Have you considered DateAdd( minute, CONNECTION_PROPERTY( 'TimeZoneAdjustment' ), utctime )? The connection property TimeZoneAdjustment reflects the current adjstment between the client timezone and UTC.

(23 Dec '21, 09:46) Chris Keating
Replies hidden

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.

(23 Dec '21, 10:25) tedfroehlich

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

(03 Jan, 09:59) JBSchueler
Replies hidden
1

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.

(03 Jan, 10:17) JBSchueler

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...

(03 Jan, 11:03) tedfroehlich
1

It was added in 17.0 build 6171.

(03 Jan, 12:49) Chris Keating

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.

(03 Jan, 13:49) JBSchueler

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.

(05 Jan, 06:54) tedfroehlich
showing 3 of 8 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • 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:

×111
×21
×6

question asked: 23 Dec '21, 05:14

question was seen: 2,609 times

last updated: 05 Jan, 06:54