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.

asked 02 Sep '13, 07:56

Ilia63's gravatar image

Ilia63
801283452
accept rate: 16%


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

answered 03 Sep '13, 13:12

JBSchueler's gravatar image

JBSchueler
2.1k2837
accept rate: 15%

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.

permanent link

answered 03 Sep '13, 11:05

TonyV's gravatar image

TonyV
1.2k333967
accept rate: 75%

Your answer
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:

×413

question asked: 02 Sep '13, 07:56

question was seen: 1,854 times

last updated: 03 Sep '13, 13:12