TSQL: Comment faire pour convertir l'heure locale à l'UTC? (SQLServer2008)

Question

Nous avons affaire à une application qui doit gérer l'heure de différents fuseaux horaires et l'heure d'paramètres. L'idée est de stocker le tout dans format UTC interne et de convertir dans les deux sens pour l'utilisateur localisée interfaces. Le SQL Server offre des mécanismes pour gérer les traductions de temps donné, un pays et un fuseau horaire?

Ce doit être un problème commun, donc je suis surpris que google ne serait pas quelque chose d'utilisable.

Les pointeurs?

Answer 1

Cela fonctionne pour les dates qui ont actuellement le même décalage UTC comme SQL Serveur hôte; il ne tient pas compte de modifications de l'heure d'été. Remplacer YOUR_DATE avec la date locale à convertir.

SELECT DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), YOUR_DATE);

Answer 2

Tandis que quelques-uns de ces réponses, vous obtiendrez dans le stade, vous ne pouvez pas faire ce que vous essayez de faire avec l'arbitraire des dates pour sql server 2005 et versions antérieures raison de l'heure avancée. À l'aide de la différence entre le courant locale et UTC actuelle va me donner le décalage qui existe aujourd'hui. Je n'ai pas trouvé un moyen de déterminer le décalage aurait été pour la date en question.

Cela dit, je sais que sql server 2008 fournit quelques nouvelles fonctions de date susceptible de répondre à cette question, mais les gens à l'aide d'une version antérieure doivent être conscients des limites.

Notre approche est de persister UTC et effectuer la conversion sur le côté client où nous avons plus de contrôle sur la conversion de l'exactitude.

Answer 3

SQL 2008 a un type appelé datetimeoffset. C'est vraiment utile pour ce genre de choses.

http://msdn.microsoft.com/en-us/library/bb630289.aspx

Ensuite, vous pouvez utiliser la fonction SWITCHOFFSET pour le déplacer d'un fuseau horaire à un autre, mais toujours en gardant la même valeur UTC.

http://msdn.microsoft.com/en-us/library/bb677244.aspx

Rob

Answer 4

J'ai tendance à pencher vers l'aide DateTimeOffset pour tous date-heure de stockage qui n'est pas liée à un événement local (ie: rencontre/partie, etc, 12h-3h du musée).

Pour récupérer les DTO que l'UTC:

DECLARE @utcNow DATETIMEOFFSET = CONVERT(DATETIMEOFFSET, SYSUTCDATETIME())
DECLARE @utcToday DATE = CONVERT(DATE, @utcNow);
DECLARE @utcTomorrow DATE = DATEADD(D, 1, @utcNow);
SELECT  @utcToday [today]
        ,@utcTomorrow [tomorrow]
        ,@utcNow [utcNow]

NOTE: j'utilise toujours UTC lors de l'envoi sur le fil... côté client JS pouvez facilement obtenir/de la locale de l'UTC. Voir: new Date().toJSON() ...

La suite de JS traitera de l'analyse d'une UTC/GMT date au format ISO8601 à un local de type datetime.

if (typeof Date.fromISOString != 'function') {
  //method to handle conversion from an ISO-8601 style string to a Date object
  //  Date.fromISOString("2009-07-03T16:09:45Z")
  //    Fri Jul 03 2009 09:09:45 GMT-0700
  Date.fromISOString = function(input) {
    var date = new Date(input); //EcmaScript5 includes ISO-8601 style parsing
    if (!isNaN(date)) return date;

    //early shorting of invalid input
    if (typeof input !== "string" || input.length < 10 || input.length > 40) return null;

    var iso8601Format = /^(\d{4})-(\d{2})-(\d{2})((([T ](\d{2}):(\d{2})(:(\d{2})(\.(\d{1,12}))?)?)?)?)?([Zz]|([-+])(\d{2})\:?(\d{2}))?$/;

    //normalize input
    var input = input.toString().replace(/^\s+/,'').replace(/\s+$/,'');

    if (!iso8601Format.test(input))
      return null; //invalid format

    var d = input.match(iso8601Format);
    var offset = 0;

    date = new Date(+d[1], +d[2]-1, +d[3], +d[7] || 0, +d[8] || 0, +d[10] || 0, Math.round(+("0." + (d[12] || 0)) * 1000));

    //use specified offset
    if (d[13] == 'Z') offset = 0-date.getTimezoneOffset();
    else if (d[13]) offset = ((parseInt(d[15],10) * 60) + (parseInt(d[16],10)) * ((d[14] == '-') ? 1 : -1)) - date.getTimezoneOffset();

    date.setTime(date.getTime() + (offset * 60000));

    if (date.getTime() <= new Date(-62135571600000).getTime()) // CLR DateTime.MinValue
      return null;

    return date;
  };
}

Answer 5

Oui, à un certain degré, comme détaillé ici.
L'approche que j'ai utilisée (avant 2008) est de faire la conversion dans le .NET de la logique métier avant de l'insérer dans la DB.