|
I need to convert local time to UTC and back. |
|
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 ) ); |
|
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. |
