Superare SQL 08 a livello globale fusi orari insensibile utilizzando. NET

Mi sembra di spendere un sacco di tempo in questione con le applicazioni web che finiscono per avere un sacco di diversità geografica. O si siedono in un server in un paese poi usati da persone o da qualche altra parte il più delle volte, si trovano ad affrontare il pubblico di carattere globale, distribuite attraverso i fusi orari variabili. E anche se lo fanno finire co-locati, è probabile che non sarà sempre rimanere così quindi c'è sempre il desiderio di aggiungere un po 'a prova di futuro.
Quando SQL 08 è arrivato sembrava che ci fosse qualche speranza nuova per rendere questo processo un po 'più facile attraverso l'introduzione di una data e ora alcuni nuovi tipi di dati relativi, in particolare il tipo datetimeoffset. Purtroppo tutto ciò che luccica non è oro in questo caso e il nuovo tipo di dati può essere un vero e proprio "Gotcha". Ecco come costruire in quella geo-consapevolezza da zero utilizzando il nuovo datetime2 tipo di dati e senza essere scoperti con i pantaloni giù metaforico.

Che cos'è un datetime2 e come è diverso da un datetime?

Il classico tipo di dati datetime era abbastanza rudimentale e aveva alcuni gravi limitazioni.Quello che regolarmente ha causato mal di testa per me era che la gamma è stata limitata al 1 ° gennaio 1753 e 31 dicembre 9999. Ora che potrebbe suonare come una gamma piuttosto liberali, ma iniziare a consumare i dati da un sistema basato Oracle che consente ad unvalore minimo data del 1 Gennaio 4712 aCe si può vedere la mancata corrispondenza. Ora, se si consumano da quel sistema a monte e qualcuno non ha fatto un lavoro particolarmente buono della loro convalida dei dati (o hanno una legittima necessità per le date di meno di 1753), poi qualcun altro la mancanza di diligenza è ora il tuo problema.
Un datetime2 tipo di dati in SQL 08 sarà scendiamo al 01/01/01 così il vecchio problema gamma datetime viene praticamente abolito (non ci sono casi d'uso molti là fuori per BC-based date). L'altra cosa che si ottiene sul top di gamma è la precisione così invece di essere limitata a 3,33 millisecondi è possibile andare fino in fondo a 100 nanosecondi. Che probabilmente non è importante per la vostra applicazione web media, ma ora è possibile anche definire la precisione nel tipo di dati, proprio come in genere si farebbe con un decimale in modo da poter salvare sempre un paio di byte per essere meno specifici.
Così che cosa ora con il tipo datetime umile che ci ha servito così bene per così tanti anni?Secondo la documentazione di TechNet:

Utilizzare l'ora, la data, datetime2 e datetimeoffset tipi di dati per un nuovo lavoro.

Così, in breve, datetime è morto.

Che cos'è un SQL 08 datetimeoffset?

Per tutti gli effetti, un datetimeoffset è un datetime2 con consapevolezza fuso orario nel range di più o meno 14 ore da GMT più un paio di stoccaggio pena di byte per tenere traccia esso. Ciò significa che possiamo memorizzare la data più il tempo e la differenza di fuso orario all'interno di un campo.
Facciamo vedere in pratica: dire che mi vuole memorizzare la data odierna e ora a mezzogiorno da due fonti diverse in cui uno di essi si riferisce a mezzogiorno a Sydney e l'altra si riferisce a mezzogiorno a New York. Nota la componente di offset delle dichiarazioni date seguenti:

DECLARE @ SydneyMidday DATETIMEOFFSET SET @ SydneyMidday = '2010-08-10 00:00:00 +10:00 '

 DECLARE @ NewYorkMidday DATETIMEOFFSET SET @ NewYorkMidday = '2010-08-10 00:00:00 -5:00 '

Se abbiamo memorizzato questi valori come datetime o datetime2 tipi di dati (senza la componente di offset, ovviamente), sarebbero considerati i tempi identici. Tuttavia, se ora eseguire il seguente:
SELEZIONARE DATEDIFF ( hh , SydneyMidday @ , @ NewYorkMidday )
Otteniamo un risultato di 15 così la funzione DateDiff è stato in grado di identificare con successo non c'è in realtà più di una mezza giornata tra queste date, anche se sono entrambi rigorosamente la stessa data e ora, hanno solo appartengono a diversi fusi orari.Ma il problema è che, indipendentemente da quante ore Sydney è actually ahead of New York (non è sempre la stessa), si sarà always ottengono 15. Questo è un problema.

L'unico neo l'ora legale

Ora che abbiamo stabilito che il datetimeoffset può fare, diamo un'occhiata a ciò che non si può fare, cioè soddisfare l'ora legale (DST). Corriamo scenario precedente, ma questa volta faremo avanzare due mesi al 10 ottobre. Ecco come tali date un'occhiata:

SET @ SydneyMidday = '2010-10-10 12:00:00 +10:00 '

SET @ NewYorkMidday = '2010-10-10 00:00:00 -5:00 '

Quando si corre la stessa data diff come prima, otteniamo 15 di nuovo ma il problema qui è che il 10 ottobre, Sydney è già andato in ora legale , mentre New York rimane in ora legale in modo che il risultato è sbagliato di un'ora. Ecco come i fusi orari aspetto:

Agosto	Settembre	Ottobre	Novembre	Dicembre
Nessun DST	Nessun DST	Dell'ora legale ha inizio 2 ottobre	DST	DST
DST	DST	DST	Ora legale terminante 9 novembre	Nessun DST

Perché abbiamo il vento i nostri orologi avanti di un'ora in Sydney il 2 ottobre abbiamo effettivamente diventare UTC +11 e il risultato corretto da quella dichiarazione datediff ultimi dovrebbero essere 16 . E 'stato spostato di un ora.
Per molte persone questo può sembrare un po 'di un bordo per caso, ma prendere la situazione in cui si vuole calcolare le ore a Sydney tra, diciamo, 20:00 il 1 ° ottobre e le 06:00 il 2 ottobre. L'approccio datetimeoffset ci darà 10 , ma la risposta corretta è 9 perché perdiamo un ora durante la notte.
Linea di fondo: non è possibile fidarsi del tipo di dati datetimeoffset per qualsiasi tipo di calcolo che potrebbe comportare l'ora legale in quanto non ha semplicemente la fedeltà di sapere quando si ferma e inizia a diverse posizioni , anche se sappiamo la differenza UTC. Il problema è descritto molto bene oltre a uno dei blog MSDN su Usando i dati di fuso orario in SQL Server 2008 in cui finiscono per importare i dati di fuso orario in tabelle su misura e calcolo manualmente le differenze.

Il problema con l'ora legale

Il problema è semplicemente questo: non sempre sapere quando sta per iniziare e non sempre sa quando finirà . Per esempio, il prossimo anno - e negli anni prevedibile - Sydney inizierà ora legale sulla prima Domenica di Ottobre. Questo è un algoritmo semplice ma non è sempre stato così. Fino al 2008 avrebbe inizio il scorso weekend di ottobre. Vogliamo rendere davvero difficile? Nel 2000 abbiamo iniziato il 27 agosto in tempo per le Olimpiadi.
Che cosa tutto questo significa:

1. Non esiste un algoritmo stabile che è possibile applicare a calcolare quando DST ha iniziato (o finito, è per questo).
2. Tu non sai cosa date dell'ora legale avrà in futuro oltre l'algoritmo attualmente accettata del giorno - e che potrebbe cambiare o una data arbitraria potrebbe essere introdotta.

In breve, il calcolo dell'ora legale sono irregolari, imprevedibile e pieno di eccezioni, quindi non è il tipo di dati che si vuole veramente prendersi la responsabilità per il mantenimento.

Il problema con fusi orari

I fusi orari rendere l'intero problema ancora peggiore, perché sono imprevedibili e instabili troppo! Date un'occhiata al seguente da tempo le mie impostazioni di Windows 7 zona:

L'immagine qui sopra mostra sei diversi fusi orari per l'Australia, metà dei quali cadono sbattere sulla stessa longitudine e anche hanno lo stesso UTC. Infatti molto chiaramente, i nostri quattro maggior parte degli stati orientali (e il nostro capitale senza sbocco sul mare), tutti in fila abbastanza ordinatamente sotto l'altro e sarebbe logicamente lo stesso fuso orario:

Quindi perché abbiamo tre fusi orari separati? Beh in primo luogo, Queensland non osserva l'ora legale quindi hanno bisogno di loro (è "Brisbane" nella lista precedente). Poi abbiamo Tasmania, che fino al 2008 aveva la propria data di inizio (anche se la data di fine stessa Nuovo Galles del Sud), in modo da ottenere il loro ingresso proprio sotto "Hobart" e allora abbiamo New South Wales, Victoria e Canberra tutti raggruppati insieme perché quelle ragazzi andare avanti abbastanza bene per concordare l'ora legale.
Ma non hanno il monopolio sul comportamento DST dispari Down Under, dare un'occhiata a questo:

Se - come me - Indiana sopra negli Stati Uniti di A è un po 'un luogo straniero a voi, ecco come appare:

Quindi qui abbiamo un caso in cui uno Stato - è suddivisa in fasce orarie distinte - ed una relativamente piccola a questo. Il cambiamento si intende la parte occidentale osserva il fuso orario conosciuta come "Central Time (USA e Canada)" mentre la parte orientale osserva "Eastern Time (USA e Canada)". Allora, dove è "Indiana (Est)"? E 'stato effettivamentedeprecato nel 2006 ma il suo ricordo rimane chiaramente in virtù del fuso orario ancora esistenti sul nostro PC.

Il ruolo del registro di Windows

Tutti i pezzi che abbiamo visto in precedenza nella sezione fuso orario sono guidati dal registro di Windows con il "HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Windows NT \ CurrentVersion \ Fusi orari" chiave. Infatti se si dà un'occhiata nell'Editor del Registro, le vedrete tutti seduti laggiù:

Ecco la cosa interessante: vedere come alcuni fusi orari hanno una piccola icona expando accanto a loro? Date un'occhiata all'interno del "Tempo AUS Eastern Standard" voce:

Qui potete vedere ci sono un paio di voci binari con nomi relativi ad esercizi. Questo è ciò che Microsoft si riferisce a come " dinamica Ora legale (DST dinamico) "in quanto è in grado di memorizzare le date effettive ora legale nel corso del tempo. Sulla base di ciò che hai appena letto sopra, questo è ovviamente molto importante in quanto l'unico modo siamo in grado di monitorare con precisione l'ora legale è di avere dati storici sui cambiamenti data.Tuttavia, il problema è che i dati va solo indietro di qualche anno (più su quello a breve).
Ecco il bit di veramente importante: questi dati sono tutti mantenuti tramite aggiornamenti cumulativi da Microsoft - basta dare un'occhiata al tasso di variazione che puntano a qualcosa che probabilmente pensava era abbastanza statico. Si noti inoltre che le date degli aggiornamenti cumulativi sono stati emessi solo tornare al 08 agosto il che significa che ci sono alcune lacune abbastanza grande storico. L'esempio Sydney precedente su dove abbiamo spostato l'ora legale da fine ottobre a inizio manca così ovviamente questo ha mancato il taglio.
Ma la linea di fondo è che finché si sta attivamente applicare la patch al server o prenderli tramite Windows Update per il tuo desktop, sarai tenuto aggiornato in cui i governi a prendere decisioni irregolare in futuro. La bellezza di questo è che tutto quello che dovete fare è continuare a fare quello che avete (si spera) sempre fatto la vostra macchina sarà semplicemente sapere cosa sta succedendo nel mondo delle zone ora legale e ora.

Lavorare con i fusi orari in formato. NET

Ora che avete capito le idiosincrasie intorno zone ora legale e ora, torniamo al codice.Tornato in. NET 3.5 abbiamo ottenuto le mani sul classe TimeZoneInfo che ci dà un sacco di caratteristiche pulito che sfruttano i dati di fuso orario memorizzati nel Registro di sistema. A causa di questo, possiamo scrivere ora legale codice a conoscenza, come quello che vedete qui sotto. Quello che stiamo andando a fare è creare due date nel fuso orario di Sydney - uno prima ora legale iniziata lo scorso anno e uno dopo - e poi riconvertirlo in UTC. Quello precedente dovrebbe mostrare ora e 10 offset UTC e quella dopo 11 ore di offset.

const string ausEst = "AUS Eastern Standard Time" ;
 var = estZone TimeZoneInfo FindSystemTimeZoneById (ausEst);.

 / / mezzogiorno del giorno prima dell'ora legale ha inizio
 var preDst = nuovo DateTime (2010, 10, 2, 12, 00, 00);
      
 / / mezzogiorno il DST giornata è già iniziato
 var postDst = nuovo DateTime (2010, 10, 3, 12, 00, 00);

 var = preDstUtc TimeZoneInfo . ConvertTimeToUtc (preDst, estZone);
 var = postDstUtc TimeZoneInfo . ConvertTimeToUtc (postDst, estZone);

 Console . WriteLine ( "La pre-DST tempo è {0} UTC. " , preDstUtc);
 Console . WriteLine ( "Il post-DST tempo è {0} UTC. " , postDstUtc);

E otteniamo la seguente (tenere a mente le mie impostazioni di localizzazione mostrerà gg-mm-aaaa):

Date un'occhiata a queste fasce orarie - esattamente quello che ci si aspetterebbe!Ricordate anche che non ci sono UTC DST quindi è un valore costante, indipendentemente dal periodo dell'anno. Ma cosa succede se si cerca il rollback di qualche anno al 2008?Ricorda, questo è stato quando l'ora legale è iniziata alla fine del mese di ottobre invece che alla partenza (il 26 era la data rollover), ma non è stato catturato negli aggiornamenti cumulativi:

Questo è chiaramente sbagliata, nel 2008, 26 ottobre è stato il primo giorno di ora legale. E possiamo capire perché è sbagliato se noi provare ad applicare il test per la prima Domenica del mese:

Vedere il problema? Il Registro di sistema non ha le informazioni su come legale era precedentemente calcolato in modo è venuta meno al meccanismo attuale è la prima Domenica di Ottobre.
Solo per dimostrare che il registro è a conoscenza di oggi le impostazioni DST defunto, torna a fine 06 e inizio 09, il nostro stato più occidentale (Western Australia), ha avuto un gioco con DST poi deciso di abbandonarla. Le date mischiato un po 'ma quando hanno preso il via in 08 è stata l'ultima Domenica del mese di ottobre (il 26). Ecco cosa si vede quando si esegue lo script precedente contro il "W. Australia, Standard "Ora fuso orario:

Western Australia è di +8 UTC così i tempi appaiono più tardi, ma la cosa importante è che il registro è a conoscenza del caso stravaganti erano indietro di qualche anno fa. Messaggio 2008, non riconosce alcun DST oltre a ovest.

Ruolo SQL 08 in tutto questo

Purtroppo SQL 08 semplicemente non ha la capacità di memorizzare nativamente sia un fuso orario (nota - non una statica offset - un fuso orario effettivo che è a conoscenza DST) o la conversione tra fusi orari diversi, tra cui UTC (ancora una volta - reali fusi orari consapevoli DST ). Allora cosa si fa?
C'è sempre l'angolo di pompaggio di tutti questi dati fuori in un paio di tavoli in cui può quindi rendere più facilmente accessibile in SQL Server. Naturalmente il problema è che hai per mantenere poi questa e come abbiamo visto dalla pagina di aggiornamenti cumulativi, i dati cambiano abbastanza di frequente. Sarebbe ragionevolmente gestibile con un piccolo numero di fusi orari, ma se si vuole essere "globalmente pronto", le cose stanno andando per iniziare a ricevere un po 'più complicato.
L'altro problema è che ancora non hanno alcun modo nativo di conversione tra fusi orari. Che cosa si finisce per fare quello che è stato fatto nel link precedente e manualmente unendo il backup dei dati e assumedly fare conversioni personalizzate ad un certo punto.
Ma si può sempre avere un po 'più di fantasia e sfruttare il NET CLR come era. mi ha suggerito oggi l'autore di SQL Server 2005 Sviluppo di esperti :

Questo commento, in particolare, mi dice c'è l'opportunità che vogliono andare in SQL Server al momento. Conversione di fusi orari sembra proprio come una funzione fondamentale per eseguire con i dati, anche nonostante le idiosincrasie della loro natura.

Applicando il comune denominatore UTC

Andiamo alla fine punta della discussione, tutto quanto sopra semplicemente ci porta a due problemi da risolvere quando abbiamo uno strato NET per fare un po 'di sollevamento di carichi pesanti:.

1. Abbiamo bisogno di un denominatore comune per memorizzare tutti i valori in datetime2 La conservazione in ora locale provoca una serie di altre sfide perché sono tutti su fusi orari diversi.
2. Abbiamo bisogno di un mezzo di conversione da e per un tempo locale, da e UTC. Ci sono momenti in cui abbiamo ancora bisogno di ricevere o visualizzare le date e ore in ora locale.

Il primo elemento è facile - negozio UTC date in un campo datetime2. UTC è l'unico denominatore comune su cui basare tutti i nostri altri fusi orari (cioè UTC -5, UTC +10, ecc) e prodotti come SQL Server. NET e sono ben consapevoli attraverso funzioni comeGETUTCDATE e DateTime . utcnow rispettivamente. . Ma una cosa che possiamo fare in NET che noi non possiamo fare in modo nativo in SQL Server è convertire i dati avanti e indietro tra l'ora locale in una posizione , non solo una differenza di fuso orario e ora abbiamo visto in azione:

var = preDstUtc TimeZoneInfo . ConvertTimeToUtc (preDst, estZone);

Naturalmente è altrettanto facile tornare indietro anche nel senso opposto:

var = preDstLocal TimeZoneInfo . ConvertTimeFromUtc (preDstUtc, estZone);

Così, dopo un preambolo molto lunga, il lavoro effettivo da fare è molto semplice, volevo solo per illustrare l'importanza della funzionalità native fuso orario. NET data la complessità di ciò che è effettivamente lavorando.

Casi d'uso

Ora che possiamo facilmente andare avanti e indietro tra l'ora UTC e fusi orari, ci sono un sacco di casi d'uso differenti in cui abbiamo bisogno per gestire le date. Ma non sempre è necessario applicare conversioni di fuso orario, qui sono gli scenari che mi vengono in mente e ricordare, l'idea è quella di persistere i dati in UTC e fare alcuna conversione di nuovo sullo strato NET:.

1. Data e ora: Pratiche come creare automaticamente uno stampaggio data o una data di modifica può semplicemente prendere immediatamente l'ora UTC o tramite NET o SQL e memorizzare direttamente nel DB..
2. Utente inserito le date: Le date sono solo che - una data - può andare direttamente in un campo di 08 data tipo SQL. Ad esempio, una data di nascita o di una giornata di ferie annuali non hanno alcun componente temporale quindi non c'è di solito un bisogno di fare alcuna conversione.
3. Utente inserito le date con i componenti di tempo: In uno scenario in cui, per esempio, un utente è la pianificazione di un evento accada in un momento particolare, andranno a lavorare in modo logico le loro fuso orario locale così conversione in UTC deve avvenire prima di inviare al livello dei dati.
4. La visualizzazione delle date con componenti di tempo: Se i dati dai punti precedenti deve essere recuperata e presentata di nuovo per l'utente, la conversione da UTC in ora locale deve accadere.

Naturalmente "locale" può anche essere un po 'variabile e, a seconda dell'applicazione, può essere a livello di sistema, vale solo per una sotto-componente del sistema (ovvero solo l'istanza di "Australia"), oppure essere specifici per l'utente (ad esempio la configurazione del fuso orario in un forum in modo da vedere tutto in ora locale).
In definitiva, la prova del nove è semplicemente questo: si può prendere l'applicazione e metterlo su un server diverso in una posizione diversa in esecuzione un fuso orario diverso e tutto ciò che ancora funziona in modo identico? Se no, hai perso una conversione da qualche parte e si sta andando ad essere bloccato per l'attuale modello sia in termini di hosting e pubblico.

Bordo casi

Ci sono alcuni problemi con l'approccio di cui sopra - non è certamente perfetto:

1. Incompleti dati del fuso orario: Come abbiamo visto con Sydney nel 08, il DST data è da tre settimane, quindi la conversione di una data UTC al locale in quel periodo (o viceversa se ​​ci fosse la necessità), porterà ad un orario non corretto fuori dal un'ora.
2. Sconosciuto cambiamenti futuri: se qualcuno entra in una data e l'ora locale per la metà di ottobre in cinque anni da ora poi convertirlo in UTC basato sulla logica attuale, potrebbe infine essere smentiti e non c'è niente da fare.
3. La necessità di ora locale del livello dei dati: Naturalmente nessuno di questo aiuta se hai veramente bisogno, ora locale, il livello dei dati. Forse c'è qualche avvenimento di reporting, forse i dati vengono spacciate per uno strato di BI, ma in entrambi i casi, si è bloccato con UTC.

I casi limite può essere mitigata da memorizzare ora locale utente nel database, ma poi hai tutti i problemi connessi con una grande quantità di dati sparsi in fusi orari e hai un veroproblema se qualcuno vuole cambiare davvero il loro tempo zona. Certamente ci sono casi in cui questo è un maggiore equilibrio, ma è anche un po 'di un futuro a prova di anti-modello.

Riassunto

La frustrazione di questo post è che non solo non è un pulito, prova così sciocco a memorizzare le date in un sistema globale senza il rischio di conflitto di fuso orario. Ma a conti fatti, maggior parte dei requisiti sarà lieto di accettare le idiosincrasie del Registro di non essere abbastanza aggiornati su alcuni dati storici. Dopo tutto, un errore di conversione uno ora contro una piccola finestra di tempo che capita di rado non è male.


Corso Visual Studio - Corsi Visual Studio
Corso .Net- Corso Dot.Net - Corso Vb.net
Corso C# - Corso PHP - Corso Joomla