cancel
Showing results for 
Search instead for 
Did you mean: 

ASA12: Converting Time Zones & Support for Daylight Saving Time

3,954

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.

Accepted Solutions (1)

Accepted Solutions (1)

jack_schueler
Product and Topic Expert
Product and Topic Expert

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 ) );

Answers (1)

Answers (1)

Former Member

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.