on 2013 Sep 02 8:56 AM
I need to convert local time to UTC and back.
In my opinion, converting for the current date can be done with the DATEDIFF-functions and CONNECTION_PROPERTY('TimeZoneAdjustment'). See, please, CURRENT UTC TIMESTAMP & TimeZoneAdjustment for ASA12 .
Can this be done for an arbitrary date (in keeping with summer/winter-time)?
For example, as Converting Time Zones and Support for Daylight Saving Time in Oracle.
Request clarification before answering.
SQL Anywhere does not have any built-in functions for determining whether daylight savings was in effect for a historical date. This is where the use of external calls comes in handy. If you are on a Windows platform, you can create the following interfaces to CLR functions that determine whether a historical date was in daylight savings.
CREATE OR REPLACE FUNCTION isDaylight( IN p1 TIMESTAMP ) RETURNS BIT EXTERNAL NAME 'c:\\\\bin\\\\TimeFunctions.dll::TimeFunctions.isDaylight( DateTime ) bool' LANGUAGE CLR; CREATE OR REPLACE FUNCTION isDaylightFromString( IN p1 LONG VARCHAR ) RETURNS BIT EXTERNAL NAME 'c:\\\\bin\\\\TimeFunctions.dll::TimeFunctions.isDaylightFromString( string ) bool' LANGUAGE CLR;
The C# CLR project for this would build a TimeFunctions DLL from the following.
using System; using System.Collections.Generic; using System.Linq; using System.Text; public class TimeFunctions { public static bool isDaylight(DateTime dt) { bool isDaylight = TimeZoneInfo.Local.IsDaylightSavingTime(dt); return isDaylight; } public static bool isDaylightFromString(String datetime) { DateTime dt = DateTime.Parse(datetime); bool isDaylight = TimeZoneInfo.Local.IsDaylightSavingTime(dt); return isDaylight; } }
The interesting bit about IsDaylightSavingTime is that it appears to use the rules that were in effect on the date/time in question. Of course this assumes that the date/time is from the locale of the server. If it isn't, there are other CLR functions that can be used.
Here are some examples:
SELECT isDaylight( CAST( '2006-04-02 02:59' AS TIMESTAMP ) ); SELECT isDaylight( CAST( '2006-04-02 03:00' AS TIMESTAMP ) ); SELECT isDaylight( CAST( '2008-03-09 02:59' AS TIMESTAMP ) ); SELECT isDaylight( CAST( '2008-03-09 03:00' AS TIMESTAMP ) );
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
The DATETIMEOFFSET data type, AKA "timestamp with timezone", automatically record date / times in UTC, as well as keep the timezone offset. That is, if you are in a time zone that is 5 hours behind UTC, such as Eastern Standard Time, then 09/03/2013 12:00:00 EST is recorded as 09/03/2013 17:00:00 UTC with a time zone offset of -05:00 hours. The data type takes care of converting from one time zone to another automatically.
This is a lot easier than doing the time zone conversions yourself. Nothing to debug.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
50 | |
9 | |
8 | |
6 | |
5 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.