Copy link to clipboard
Copied
Hey Folks,
We store timezone IDs from java.util.TimeZone, and now we want to use them with SQL Server (2019)'s AT TIMEZONE function. The problem is, the Java name for the timezone is not the same as the name SQL server wants.
For example, we store the timezone ID from Java 'Australia/Adelaide'. We can get the full name via java.util.TimeZone.getTimeZone('Australia/Adelaide').getDisplayName(), which returns 'Australian Central Standard Time (South Australia)'.
But.... SQL Server wants 'Australian Central Standard Time' and throws an error when using the string returned from Java.
--- this works
select datetime AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE 'Australian Central Standard' AS datetime_TimeZone
-- this fails
select datetime AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE 'Australian Central Standard Time (South Australia)' AS datetime_TimeZone
Note, our dates are stored in PST and there is no way to easily change that now. We just need to be able to convert Java's name of the timezone to the one SQL server expects which I believe is based on the window registry HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones
Has anyone had to deal with this or have any idea?
1 Correct answer
I found a XML file that has all the conversions! Still testing it but so far looks like like it is perfect. Link is here https://raw.githubusercontent.com/unicode-org/cldr/main/common/supplemental/windowsZones.xml
Copy link to clipboard
Copied
This might be a dumb idea, but can you just manipulate the string you're getting from Java before using it in your SQL? For example, if the part before the parentheses is always a match, just clip off everything starting with the space before the opening parenthesis.
Dave Watts, Eidolon LLC
Copy link to clipboard
Copied
And if Dave's idea doesn't pan out, I will say that storing timezone strings is just fraught with peril. That's why the recommendation is nearly always to store things in UTC. Heck, even what may be "standard" time one day would be off by an hour half the year (in most timezones), right? But perhaps you are somehow already dealing with that.
And I know you said, Brook, that "our dates are stored in PST and there is no way to easily change that now." I realize that may be your "final answer" (to quote WWTBAM). Just wanted to put this out there. 🙂
/Charlie (troubleshooter, carehart. org)
Copy link to clipboard
Copied
Aww, Charlie, you are right, and I wish the original designer had used UTC. But alas, all the dates are stored in PST. So we run everthing through time zone conversion code which is slow, and painful. SQL Servers' AT TIMEZONE function is helpful in translating the dates though, and is one of the workarounds we are currently reviewing.
Thanks for the replies guys! You two are veterans!! Allaire day vets! There's no way your profile pics are recent guys, come on this isn't a dating site 😉 jk
Copy link to clipboard
Copied
"come on this isn't a dating site"
soo disappointed!
Dave Watts, Eidolon LLC
Copy link to clipboard
Copied
Well at least matches on this site wouldn't get a glazed over look in their eyes when you talk about timezones and offsets. 😉
Copy link to clipboard
Copied
Thanks for the updates and the kind regards, Brook.
But about our profile pics, what are you saying? That you want mine to show still less hair, and still MORE gray for what little remains? As if my pic doesn't already cause enough agism? 🙂
/Charlie (troubleshooter, carehart. org)
Copy link to clipboard
Copied
Not a dump idea, that is my back up plan 😉
If it is that easy, that would be great. I don't expect it to be that easy for ALL the timezones, although I admittedly have not dumped them all out and made a side-by-side comparison.
I feel this approach could be error-prone. I was hoping for a more systematic approach or some interoperability I wasn't aware of that would help translate these values.
Copy link to clipboard
Copied
I found a XML file that has all the conversions! Still testing it but so far looks like like it is perfect. Link is here https://raw.githubusercontent.com/unicode-org/cldr/main/common/supplemental/windowsZones.xml
Copy link to clipboard
Copied
There is likely a better way to do this, but in case anyone cares, this function will read the xml file from my previous post and create a struct with java2win, and win2java keys for translating the timezones back and forth..
<cffunction name="getJavatoSQLTimeZoneMap" access="public" output="false" returntype="any">
<!--- get the windowsZones.xml file (https://raw.githubusercontent.com/unicode-org/cldr/main/common/supplemental/windowsZones.xml) --->
<cftry>
<cfsavecontent variable="local.xml">
<cfinclude template="/config/windowsZones.xml">
</cfsavecontent>
<cfset local.xml = xmlParse(trim(local.xml))>
<cfcatch>
<cfthrow message="Unable to locate windowsZones.xml or error parsing XML. #cfcatch.message#">
</cfcatch>
</cftry>
<cfset local.tz_java2winmap={
win2java:{},
java2win:{}
}>
<cfset local.childnodes = local.xml.xmlroot.xmlchildren[2].xmlchildren[1].xmlchildren>
<cfloop array="#local.childnodes#" item="local.item">
<cfset local.tz_java2winmap.win2java[local.item.XmlAttributes.type] = local.item.XmlAttributes.other>
<cfset local.tz_java2winmap.java2win[local.item.XmlAttributes.other] = local.item.XmlAttributes.type>
</cfloop>
<cfreturn local.tz_java2winmap>
</cffunction>
Copy link to clipboard
Copied
That index was backwards
<cffunction name="getJavatoSQLTimeZoneMap" access="public" output="false" returntype="any">
<!--- get the windowsZones.xml file (https://raw.githubusercontent.com/unicode-org/cldr/main/common/supplemental/windowsZones.xml) --->
<cftry>
<cfsavecontent variable="local.xml">
<cfinclude template="/config/windowsZones.xml">
</cfsavecontent>
<cfset local.xml = xmlParse(trim(local.xml))>
<cfcatch>
<cfthrow message="Unable to locate windowsZones.xml or error parsing XML. #cfcatch.message#">
</cfcatch>
</cftry>
<cfset local.tz_java2winmap={
win2java:{},
java2win:{}
}>
<cfset local.childnodes = local.xml.xmlroot.xmlchildren[2].xmlchildren[1].xmlchildren>
<cfloop array="#local.childnodes#" item="local.item">
<cfset local.tz_java2winmap.win2java[local.item.XmlAttributes.other] = local.item.XmlAttributes.type>
<cfset local.tz_java2winmap.java2win[local.item.XmlAttributes.type] = local.item.XmlAttributes.other>
</cfloop>
<cfreturn local.tz_java2winmap>
</cffunction>
<cffunction name="getTimeZoneForSQL" access="public" output="false" returntype="string">
<cfargument name="timezone" type="string" required="true">
<cfif structKeyExists(this.java2wintimezonemap.java2win,arguments.timezone)>
<cfreturn this.java2wintimezonemap.java2win[arguments.timezone]>
<cfelse>
<cfreturn ''>
</cfif>
</cffunction>

