octobre
2011
Un article de zinzineti
Pour une même application utilisée dans différentes times zones, il est pratique de stocker dans la base les données datetime en UTC/GMT et de proposer en INPUT les différentes times zones afin d’afficher correctement les données selon les réalités de la zone géographique de l’utilisateur.
Voici 2 pocédures P_localDateTimeToUTC et P_UTCTolocalDateTime qui permettent de jouer à ce jeu.
–=========================================================================================
–> Description : Retourne l'UTC à partir de la dateheure local
–> Exemple d'utilisation :
– DECLARE @LocalDateTime DATETIME,@LocalDateTimeToUTC DATETIME
– SET @LocalDateTime= GETDATE()
– EXEC P_localDateTimeToUTC @LocalDateTime, @LocalDateTimeToUTC OUTPUT
– SELECT @LocalDateTimeToUTC AS [LocalDateTimeToUTC],@LocalDateTime AS [LocalDateTime],GETUTCDATE()AS [UTC]
–> Auteur : Etienne ZINZINDOHOUE
–=========================================================================================
CREATE PROCEDURE P_localDateTimeToUTC
@LocalDateTime DATETIME,
@LocalDateTimeToUTC DATETIME OUTPUT
AS
BEGIN TRY
SET NOCOUNT ON
DECLARE @UTC DATETIME
SET @UTC = GETUTCDATE()
– Conversion du LocalDateTime en UTC
SET @LocalDateTimeToUTC = DATEADD(Hour, DATEDIFF(Hour, GETDATE(), GETUTCDATE()), @LocalDateTime)
END TRY
BEGIN CATCH
RETURN 'Erreur dans la procedure stockee P_localDateTimeToUTC'
END CATCH
Et pour faire l’inverse
–=========================================================================================
–> Description : Retourne l'UTC partir de la dateheure local
–> Exemple d'utilisation :
– DECLARE @UTCToLocalDateTime DATETIME
– EXEC P_UTCTolocalDateTime @UTCToLocalDateTime OUTPUT
– SELECT @UTCToLocalDateTime AS [UTCToLocalDateTime],GETDATE()AS [HeureLocal]
–> Auteur : Etienne ZINZINDOHOUE
–=========================================================================================
CREATE PROCEDURE P_UTCTolocalDateTime
@LocalDateTime DATETIME OUTPUT
AS
BEGIN TRY
SET NOCOUNT ON
DECLARE @UTC DATETIME
—DECLARE @LocalDateTimeToUTC DATETIME
SET @UTC = GETUTCDATE()
– Conversion UTC en dateheure local
SET @LocalDateTime = DATEADD(Hour, DATEDIFF(Hour, GETUTCDATE(),GETDATE()), @UTC)
END TRY
BEGIN CATCH
RETURN 'Erreur dans la procedure stockee P_UTCTolocalDateTime'
END CATCH
Pour proposer à l’utilisateur de choisir son time zone on peut charger une table TimeZone (TZ) avec les données ci-dessous noter que le séparateur est ;
TZName;StandardTime
Antarctica/Vostok;UTC+00
Africa/Ouagadougou;UTC+00
Africa/Abidjan;UTC+00
Africa/El_Aaiun;UTC+00
Atlantic/Canary;UTC+00
Atlantic/Faroe;UTC+00
Europe/London;UTC+00
Europe/Guernsey;UTC+00
Africa/Accra;UTC+00
America/Danmarkshavn;UTC+00
Africa/Banjul;UTC+00
Africa/Conakry;UTC+00
Africa/Bissau;UTC+00
Europe/Dublin;UTC+00
Europe/Isle_of_Man;UTC+00
Atlantic/Reykjavik;UTC+00
Europe/Jersey;UTC+00
Africa/Monrovia;UTC+00
Africa/Casablanca;UTC+00
Africa/Bamako;UTC+00
Africa/Nouakchott;UTC+00
Europe/Lisbon;UTC+00
Atlantic/Madeira;UTC+00
Atlantic/St_Helena;UTC+00
Africa/Freetown;UTC+00
Africa/Dakar;UTC+00
Africa/Sao_Tome;UTC+00
Africa/Lome;UTC+00
Europe/Andorra;UTC+01
Europe/Tirane;UTC+01
Africa/Luanda;UTC+01
Europe/Vienna;UTC+01
Europe/Sarajevo;UTC+01
Europe/Brussels;UTC+01
Africa/Porto-Novo;UTC+01
Africa/Kinshasa;UTC+01
Africa/Bangui;UTC+01
Africa/Brazzaville;UTC+01
Europe/Zurich;UTC+01
Africa/Douala;UTC+01
Europe/Prague;UTC+01
Europe/Berlin;UTC+01
Europe/Copenhagen;UTC+01
Africa/Algiers;UTC+01
Europe/Madrid;UTC+01
Africa/Ceuta;UTC+01
Europe/Paris;UTC+01
Africa/Libreville;UTC+01
Europe/Gibraltar;UTC+01
Africa/Malabo;UTC+01
Europe/Zagreb;UTC+01
Europe/Budapest;UTC+01
Europe/Rome;UTC+01
Europe/Vaduz;UTC+01
Europe/Luxembourg;UTC+01
Europe/Monaco;UTC+01
Europe/Podgorica;UTC+01
Europe/Skopje;UTC+01
Europe/Malta;UTC+01
Africa/Windhoek;UTC+01
Africa/Niamey;UTC+01
Africa/Lagos;UTC+01
Europe/Amsterdam;UTC+01
Europe/Oslo;UTC+01
Europe/Warsaw;UTC+01
Europe/Belgrade;UTC+01
Europe/Stockholm;UTC+01
Europe/Ljubljana;UTC+01
Arctic/Longyearbyen;UTC+01
Europe/Bratislava;UTC+01
Europe/San_Marino;UTC+01
Africa/Ndjamena;UTC+01
Africa/Tunis;UTC+01
Europe/Vatican;UTC+01
Europe/Mariehamn;UTC+02
Europe/Sofia;UTC+02
Africa/Bujumbura;UTC+02
Africa/Gaborone;UTC+02
Europe/Minsk;UTC+02
Africa/Lubumbashi;UTC+02
Asia/Nicosia;UTC+02
Europe/Tallinn;UTC+02
Africa/Cairo;UTC+02
Europe/Helsinki;UTC+02
Europe/Athens;UTC+02
Asia/Jerusalem;UTC+02
Asia/Amman;UTC+02
Asia/Beirut;UTC+02
Africa/Maseru;UTC+02
Europe/Vilnius;UTC+02
Europe/Riga;UTC+02
Africa/Tripoli;UTC+02
Europe/Chisinau;UTC+02
Africa/Blantyre;UTC+02
Africa/Maputo;UTC+02
Asia/Gaza;UTC+02
Europe/Bucharest;UTC+02
Africa/Kigali;UTC+02
Asia/Damascus;UTC+02
Africa/Mbabane;UTC+02
Europe/Istanbul;UTC+02
Europe/Kiev;UTC+02
Europe/Uzhgorod;UTC+02
Europe/Zaporozhye;UTC+02
Europe/Simferopol;UTC+02
Africa/Johannesburg;UTC+02
Africa/Lusaka;UTC+02
Africa/Harare;UTC+02
Antarctica/Syowa;UTC+03
Asia/Bahrain;UTC+03
Africa/Djibouti;UTC+03
Africa/Asmara;UTC+03
Africa/Addis_Ababa;UTC+03
Asia/Baghdad;UTC+03
Africa/Nairobi;UTC+03
Indian/Comoro;UTC+03
Asia/Kuwait;UTC+03
Indian/Antananarivo;UTC+03
Asia/Qatar;UTC+03
Europe/Kaliningrad;UTC+03
Asia/Riyadh;UTC+03
Africa/Khartoum;UTC+03
Africa/Mogadishu;UTC+03
Africa/Dar_es_Salaam;UTC+03
Africa/Kampala;UTC+03
Asia/Aden;UTC+03
Indian/Mayotte;UTC+03
Asia/Tehran;UTC+03:30
Asia/Dubai;UTC+04
Asia/Yerevan;UTC+04
Asia/Baku;UTC+04
Asia/Tbilisi;UTC+04
Indian/Mauritius;UTC+04
Asia/Muscat;UTC+04
Indian/Reunion;UTC+04
Europe/Moscow;UTC+04
Europe/Volgograd;UTC+04
Europe/Samara;UTC+04
Indian/Mahe;UTC+04
Asia/Kabul;UTC+04:30
Asia/Aqtobe;UTC+05
Asia/Aqtau;UTC+05
Asia/Oral;UTC+05
Indian/Maldives;UTC+05
Indian/Kerguelen;UTC+05
Asia/Dushanbe;UTC+05
Asia/Ashgabat;UTC+05
Asia/Samarkand;UTC+05
Asia/Tashkent;UTC+05
Asia/Kolkata;UTC+05:30
Asia/Colombo;UTC+05:30
Asia/Kathmandu;UTC+05:45
Antarctica/Mawson;UTC+06
Asia/Dhaka;UTC+06
Asia/Thimphu;UTC+06
Indian/Chagos;UTC+06
Asia/Bishkek;UTC+06
Asia/Almaty;UTC+06
Asia/Qyzylorda;UTC+06
Asia/Karachi;UTC+06
Asia/Yekaterinburg;UTC+06
Indian/Cocos;UTC+06:30
Asia/Rangoon;UTC+06:30
Antarctica/Davis;UTC+07
Indian/Christmas;UTC+07
Asia/Jakarta;UTC+07
Asia/Pontianak;UTC+07
Asia/Phnom_Penh;UTC+07
Asia/Vientiane;UTC+07
Asia/Hovd;UTC+07
Asia/Omsk;UTC+07
Asia/Novosibirsk;UTC+07
Asia/Novokuznetsk;UTC+07
Asia/Bangkok;UTC+07
Asia/Ho_Chi_Minh;UTC+07
Antarctica/Casey;UTC+08
Australia/Perth;UTC+08
Asia/Brunei;UTC+08
Asia/Shanghai;UTC+08
Asia/Harbin;UTC+08
Asia/Chongqing;UTC+08
Asia/Urumqi;UTC+08
Asia/Kashgar;UTC+08
Asia/Hong_Kong;UTC+08
Asia/Makassar;UTC+08
Asia/Ulaanbaatar;UTC+08
Asia/Choibalsan;UTC+08
Asia/Macau;UTC+08
Asia/Kuala_Lumpur;UTC+08
Asia/Kuching;UTC+08
Asia/Manila;UTC+08
Asia/Krasnoyarsk;UTC+08
Asia/Singapore;UTC+08
Asia/Taipei;UTC+08
Australia/Eucla;UTC+08:45
Asia/Jayapura;UTC+09
Asia/Tokyo;UTC+09
Asia/Pyongyang;UTC+09
Asia/Seoul;UTC+09
Pacific/Palau;UTC+09
Asia/Irkutsk;UTC+09
Asia/Dili;UTC+09
Australia/Broken_Hill;UTC+09:30
Australia/Adelaide;UTC+09:30
Australia/Darwin;UTC+09:30
Antarctica/DumontDUrville;UTC+10
Australia/Hobart;UTC+10
Australia/Currie;UTC+10
Australia/Melbourne;UTC+10
Australia/Sydney;UTC+10
Australia/Brisbane;UTC+10
Australia/Lindeman;UTC+10
Pacific/Truk;UTC+10
Pacific/Guam;UTC+10
Pacific/Saipan;UTC+10
Pacific/Port_Moresby;UTC+10
Asia/Yakutsk;UTC+10
Australia/Lord_Howe;UTC+10:30
Pacific/Ponape;UTC+11
Pacific/Kosrae;UTC+11
Pacific/Noumea;UTC+11
Asia/Vladivostok;UTC+11
Asia/Sakhalin;UTC+11
Pacific/Guadalcanal;UTC+11
Pacific/Efate;UTC+11
Pacific/Norfolk;UTC+11:30
Antarctica/McMurdo;UTC+12
Antarctica/South_Pole;UTC+12
Pacific/Fiji;UTC+12
Pacific/Tarawa;UTC+12
Pacific/Majuro;UTC+12
Pacific/Kwajalein;UTC+12
Pacific/Nauru;UTC+12
Pacific/Auckland;UTC+12
Asia/Magadan;UTC+12
Asia/Kamchatka;UTC+12
Asia/Anadyr;UTC+12
Pacific/Funafuti;UTC+12
Pacific/Wake;UTC+12
Pacific/Wallis;UTC+12
Pacific/Chatham;UTC+12:45
Pacific/Enderbury;UTC+13
Pacific/Tongatapu;UTC+13
Pacific/Kiritimati;UTC+14
Atlantic/Cape_Verde;UTC-01
America/Scoresbysund;UTC-01
Atlantic/Azores;UTC-01
America/Noronha;UTC-02
Atlantic/South_Georgia;UTC-02
Antarctica/Rothera;UTC-03
America/Argentina/Buenos_Aires;UTC-03
America/Argentina/Cordoba;UTC-03
America/Argentina/Salta;UTC-03
America/Argentina/Jujuy;UTC-03
America/Argentina/Tucuman;UTC-03
America/Argentina/Catamarca;UTC-03
America/Argentina/La_Rioja;UTC-03
America/Argentina/San_Juan;UTC-03
America/Argentina/Mendoza;UTC-03
America/Argentina/Rio_Gallegos;UTC-03
America/Argentina/Ushuaia;UTC-03
America/Belem;UTC-03
America/Fortaleza;UTC-03
America/Recife;UTC-03
America/Araguaina;UTC-03
America/Maceio;UTC-03
America/Bahia;UTC-03
America/Sao_Paulo;UTC-03
America/Santarem;UTC-03
America/Cayenne;UTC-03
America/Godthab;UTC-03
America/Miquelon;UTC-03
America/Paramaribo;UTC-03
America/Montevideo;UTC-03
America/St_Johns;UTC-03:30
America/Antigua;UTC-04
America/Anguilla;UTC-04
America/Curacao;UTC-04
Antarctica/Palmer;UTC-04
America/Argentina/San_Luis;UTC-04
America/Aruba;UTC-04
America/Barbados;UTC-04
America/St_Barthelemy;UTC-04
Atlantic/Bermuda;UTC-04
America/La_Paz;UTC-04
America/Campo_Grande;UTC-04
America/Cuiaba;UTC-04
America/Porto_Velho;UTC-04
America/Boa_Vista;UTC-04
America/Manaus;UTC-04
America/Eirunepe;UTC-04
America/Rio_Branco;UTC-04
America/Halifax;UTC-04
America/Glace_Bay;UTC-04
America/Moncton;UTC-04
America/Goose_Bay;UTC-04
America/Blanc-Sablon;UTC-04
America/Santiago;UTC-04
America/Dominica;UTC-04
America/Santo_Domingo;UTC-04
Atlantic/Stanley;UTC-04
America/Grenada;UTC-04
America/Thule;UTC-04
America/Guadeloupe;UTC-04
America/Guyana;UTC-04
America/St_Kitts;UTC-04
America/St_Lucia;UTC-04
America/Marigot;UTC-04
America/Martinique;UTC-04
America/Montserrat;UTC-04
America/Puerto_Rico;UTC-04
America/Asuncion;UTC-04
America/Port_of_Spain;UTC-04
America/St_Vincent;UTC-04
America/Tortola;UTC-04
America/St_Thomas;UTC-04
America/Caracas;UTC-04:30
America/Nassau;UTC-05
America/Montreal;UTC-05
America/Toronto;UTC-05
America/Nipigon;UTC-05
America/Thunder_Bay;UTC-05
America/Iqaluit;UTC-05
America/Pangnirtung;UTC-05
America/Resolute;UTC-05
America/Atikokan;UTC-05
America/Bogota;UTC-05
America/Havana;UTC-05
America/Guayaquil;UTC-05
America/Port-au-Prince;UTC-05
America/Jamaica;UTC-05
America/Cayman;UTC-05
America/Panama;UTC-05
America/Lima;UTC-05
America/Grand_Turk;UTC-05
America/New_York;UTC-05
America/Detroit;UTC-05
America/Kentucky/Louisville;UTC-05
America/Kentucky/Monticello;UTC-05
America/Indiana/Indianapolis;UTC-05
America/Indiana/Vincennes;UTC-05
America/Indiana/Winamac;UTC-05
America/Indiana/Marengo;UTC-05
America/Indiana/Petersburg;UTC-05
America/Indiana/Vevay;UTC-05
America/Belize;UTC-06
America/Rankin_Inlet;UTC-06
America/Winnipeg;UTC-06
America/Rainy_River;UTC-06
America/Regina;UTC-06
America/Swift_Current;UTC-06
Pacific/Easter;UTC-06
America/Costa_Rica;UTC-06
Pacific/Galapagos;UTC-06
America/Guatemala;UTC-06
America/Tegucigalpa;UTC-06
America/Mexico_City;UTC-06
America/Cancun;UTC-06
America/Merida;UTC-06
America/Monterrey;UTC-06
America/Matamoros;UTC-06
America/Managua;UTC-06
America/El_Salvador;UTC-06
America/Chicago;UTC-06
America/Indiana/Tell_City;UTC-06
America/Indiana/Knox;UTC-06
America/Menominee;UTC-06
America/North_Dakota/Center;UTC-06
America/North_Dakota/New_Salem;UTC-06
America/Edmonton;UTC-07
America/Cambridge_Bay;UTC-07
America/Yellowknife;UTC-07
America/Inuvik;UTC-07
America/Dawson_Creek;UTC-07
America/Mazatlan;UTC-07
America/Chihuahua;UTC-07
America/Ojinaga;UTC-07
America/Hermosillo;UTC-07
America/Denver;UTC-07
America/Boise;UTC-07
America/Shiprock;UTC-07
America/Phoenix;UTC-07
America/Vancouver;UTC-08
America/Whitehorse;UTC-08
America/Dawson;UTC-08
America/Tijuana;UTC-08
America/Santa_Isabel;UTC-08
Pacific/Pitcairn;UTC-08
America/Los_Angeles;UTC-08
Pacific/Gambier;UTC-09
America/Anchorage;UTC-09
America/Juneau;UTC-09
America/Yakutat;UTC-09
America/Nome;UTC-09
Pacific/Marquesas;UTC-09:30
Pacific/Rarotonga;UTC-10
Pacific/Tahiti;UTC-10
Pacific/Fakaofo;UTC-10
Pacific/Johnston;UTC-10
America/Adak;UTC-10
Pacific/Honolulu;UTC-10
Pacific/Pago_Pago;UTC-11
Pacific/Niue;UTC-11
Pacific/Midway;UTC-11
Pacific/Apia;UTC-11
Une façon simple d’obetnir l’heure locale actuelle et le décalage avec UTC
SELECT SYSDATETIMEOFFSET() AS 'Heure locale + décalage par rapport à UTC'
,SYSUTCDATETIME() AS 'UTC'
——————————
Etienne ZINZINDOHOUE
——————————