SQL – Etienne ZINZINDOHOUE

Un article de zinzineti    Pas de commentaires

Sous SQL SERVER 2008 R2, il n’existe pas nativement de fonction qui permet de connaître le décalage horaire (offset) d’une zone passée en paramètre. Sous ORACLE il suffit de faire SELECT TZ_OFFSET('Europe/Paris') FROM V$TIMEZONE_NAMES;. Son équivalent SQL Server n’existe pas à ce jour. Toujours à propos des fuseaux horaires ORACLE permet nativement de lister les fuseaux horaires installés sur le serveur. Il suffit de faire SELECT TZNAME FROM V$TIMEZONE_NAMES; son équivalent SQL SERVER n’existe pas nativement…. mais la bonne nouvelle c’est que SQL SERVER donne la possibilité d’écrire des fonctions CLRs pour ces besoins spécifiques.

Voici les deux exemples cités ci-dessus

SOUS ORACLE (10g)
===========
=> 1er exemple : SELECT TZNAME FROM V$TIMEZONE_NAMES;

TZNAME
—————————————————————-
Africa/Ceuta
Africa/Windhoek
America/Cayenne
…….
…….
Europe/Amsterdam
Europe/Copenhagen
Europe/Helsinki
Europe/Istanbul
Europe/Kiev
Europe/Lisbon
Europe/London
Europe/Paris
…….
…….

=> 2ème exemple :

SELECT TZ_OFFSET('Europe/Paris') FROM V$TIMEZONE_NAMES;

TZ_OFFSET(‘EUROPE/PARIS’)
————————-
+02:00

SOUS SQL SERVER 2008R2
===========
les fonctions SYSDATETIMEOFFSET(), SYSUTCDATETIME(), DATEDIFF (), DATEPART () ne suffisent pas à répondre à ces besoins. Mais avec les CLRs on peut obtenir les résultats excomptés.

=> Code C#

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Globalization;

using System.Collections;

using System.Collections.ObjectModel;

public partial class TZ_Rules

{

[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "GetRow"

, TableDefinition = "TZName nvarchar(256),TZ_Offset nvarchar(10),StartDate nvarchar(256),EndDate nvarchar(256),StartDSTRule nvarchar(256),EndDSTRule nvarchar(256)")]

public static IEnumerable F_TzRules()

{

ArrayList rowsArray = new ArrayList();

GetTZ_Rules(rowsArray);

return rowsArray;

}

private enum WeekOfMonth

{

First = 1,

Second = 2,

Third = 3,

Fourth = 4,

Last = 5,

}

public static void GetTZ_Rules(ArrayList rowsArray)

{

int tzinfomax = TimeZoneInfo.GetSystemTimeZones().Count;

// Pour chaque fuseau horaire

for (int i = 0; i < tzinfomax; i++)

{

string tzid = TimeZoneInfo.GetSystemTimeZones()[i].Id;

TimeZoneInfo tzinfo = TimeZoneInfo.FindSystemTimeZoneById(tzid);

TimeZoneInfo.AdjustmentRule[] adjustmentRules = tzinfo.GetAdjustmentRules();

DateTimeFormatInfo dateInfo = CultureInfo.CurrentCulture.DateTimeFormat;

//Pour les zone ayant des règles de changement d'heure

if (adjustmentRules.Length > 0)

{

int jmax = adjustmentRules.Length;

for (int j = 0; j < jmax; j++)

{

TimeZoneInfo.TransitionTime daylightStart = adjustmentRules[j].DaylightTransitionStart;

TimeZoneInfo.TransitionTime daylightEnd = adjustmentRules[j].DaylightTransitionEnd;

if (!daylightStart.IsFixedDateRule && !daylightEnd.IsFixedDateRule)

{

object[] column = new object[6];

column[0] = tzid.ToString();

column[1] = tzinfo.GetUtcOffset(DateTime.Now); //offset (décalage horaire) par rapport à UTC

column[2] = adjustmentRules[j].DateStart.ToString();

column[3] = adjustmentRules[j].DateEnd.ToString();

column[4] = ((WeekOfMonth)daylightStart.Week).ToString() + " " + daylightStart.DayOfWeek.ToString() + " of " + dateInfo.GetMonthName(daylightStart.Month);

column[5] = ((WeekOfMonth)daylightEnd.Week).ToString() + " " + daylightEnd.DayOfWeek.ToString() + " of " + dateInfo.GetMonthName(daylightEnd.Month);

rowsArray.Add(column);

}

}

}

//Pour les zones n'ayant pas de changement horaire

else

{

object[] column = new object[6];

column[0] = tzid.ToString();

column[1] = tzinfo.GetUtcOffset(DateTime.Now);

column[2] = " ";

column[3] = " ";

column[4] = "No rule ";

column[5] = "No rule ";

rowsArray.Add(column);

}

}

}

private static void GetRow(Object obj, out SqlString TZName, out SqlString TZ_Offset, out SqlString StartDSTRule, out SqlString EndDSTRule, out SqlString StartDate, out SqlString EndDate)

{

object[] row = (object[])obj;

TZName = row[0].ToString();

TZ_Offset = row[1].ToString();

StartDSTRule = row[2].ToString();

EndDSTRule = row[3].ToString();

StartDate = row[4].ToString();

EndDate = row[5].ToString();

}

};

=>Prequis pour le déployement
FrameWork .NET : 3.5
Clique droit sur le projet > propriétés >
Dans le menu application, positionner la valeur du Framework cible à 3.5

=> Résultat : Test1

=> Résultat : Test2

Remarquer que mon OS est en français ce qui donne des StartDSTRule du genre « Last Sunday of mars » au de lieu de « Last Sunday of march » bref les mois sont en français.

L’idée ici est :
–> de lister les fuseaux horaires présents sur le serveur de base de données
–> de lister les règles de décalage horaire correspondant à chaque fuseau horaire
–> de lister les dates de validité (StartDate et EndDate) de ces règles
–> d’obtenir le décalage horaire (offset) correspondant à chaque fuseau horaire par rapport à UTC

—————————
Etienne ZINZINDOHOUE
—————————