この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
昨日(9/30)、私の"心の映画"とも言えるべき『マッドマックス 怒りのデスロード』が"極上爆音上映"を売りにしているシネマシティ(立川市)にてリバイバル上映を終了致しました。最終上映という事で映画開始前の挨拶を観客の中から選んだという事らしいのですが、その中から選ばれたのはV140(当映画を映画館で140回鑑賞)且つV50(シネマシティにて50回鑑賞)の女性の方だったようです。途方も無い数字過ぎてびっくりしたのと共に、通算V17(うちシネマシティではV11)までしか達成出来ていない私はまだまだ修行とV8への崇拝が足りないなぁと思う次第でした。
という訳で本題です。
Amazon Redshiftでは、タイムゾーンについては永らく『UTC(世界協定時刻)』のみ使用可となっており、利用可能なデータ型についても『timestamp without time zone』(タイムゾーン無しのタイムスタンプ型)のみとなっておりました。
でしたが、この度ようやく新しく『timestamp with time zone』(タイムゾーン付きタイムスタンプ型)が利用可能となった事がアナウンスされました！当エントリではその内容について紹介してみたいと思います。
Amazon Redshift introduces new data type to support time zones in time stamps! https://t.co/rvu7qEmZxU pic.twitter.com/6WMCp8om2h
— Amazon Web Services (@awscloud) 2016年9月30日
利用可能となる条件
毎度の事ながら、Redshiftに於いては新機能リリースは所定のパッチが当たり、条件を満たすバージョンにアップグレードされるか、所定のバージョンでクラスタが作成出来るタイミングでクラスタを新規作成する必要があります。下記フォーラムでのアナウンス内容によると、これから1〜2週間の間に(所定の時間に設定した)メンテナンスウインドウでのパッチで更新される予定、とあります。
We will be patching your Amazon Redshift clusters during your system maintenance window in the coming 1-2 weeks. The timing of the patch will depend on your region and maintenance window settings. You can view or change your maintenance window settings from the AWS Management Console. After your cluster has been patched, the new cluster version will be Build 1.0.1103.
バージョンについては1.0.1103になっていれば使えるようです。現行クラスタのバージョンが幾つになっているかを確かめるには管理コンソールで内容を確認するか、AWS CLIで以下のコマンドを実行する事で内容を見ることが出来ます。
$ aws redshift describe-clusters --cluster-identifier (クラスタ名) \
| jq '.Clusters[] | .ClusterVersion, .ClusterRevisionNumber'
"1.0"
"1101"新しいデータ型：TIMESTAMPTZ
TIMESTAMPTZはTIMESTAMP WITH TIME ZONE(タイムゾーン付きタイムスタンプ)のエイリアスです。
日付、時刻、そしてタイムスタンプの情報を持つ"完全な"タイムスタンプ値を入力する際に、このデータ型:TIMESTAMPTZを使います。入力値にタイムゾーンが含まれていると、Amazon Redshiftはその値を世界協定時刻(UTC)に変換し、UTCとして値を格納します。
サポートされているタイムゾーンのリストを観る場合はpg_timezone_names()を使います。 (少し長いですが実行内容も添付しておきます)
# select pg_timezone_names();
pg_timezone_names
----------------------------------------------------
(Africa/Banjul,GMT,00:00:00,f)
(Africa/Dar_es_Salaam,EAT,03:00:00,f)
(Africa/Blantyre,CAT,02:00:00,f)
(Africa/Gaborone,CAT,02:00:00,f)
(Africa/Maputo,CAT,02:00:00,f)
(Africa/Monrovia,GMT,00:00:00,f)
(Africa/Khartoum,EAT,03:00:00,f)
(Africa/Djibouti,EAT,03:00:00,f)
(Africa/Johannesburg,SAST,02:00:00,f)
(Africa/Freetown,GMT,00:00:00,f)
(Africa/Juba,EAT,03:00:00,f)
(Africa/Cairo,EET,02:00:00,f)
(Africa/Maseru,SAST,02:00:00,f)
(Africa/Malabo,WAT,01:00:00,f)
(Africa/Sao_Tome,GMT,00:00:00,f)
(Africa/Lome,GMT,00:00:00,f)
(Africa/Lagos,WAT,01:00:00,f)
(Africa/Addis_Ababa,EAT,03:00:00,f)
(Africa/Accra,GMT,00:00:00,f)
(Africa/Nairobi,EAT,03:00:00,f)
(Africa/Algiers,CET,01:00:00,f)
(Africa/Asmera,EAT,03:00:00,f)
(Africa/Porto-Novo,WAT,01:00:00,f)
(Africa/Nouakchott,GMT,00:00:00,f)
(Africa/Asmara,EAT,03:00:00,f)
(Africa/Abidjan,GMT,00:00:00,f)
(Africa/Douala,WAT,01:00:00,f)
(Africa/Lubumbashi,CAT,02:00:00,f)
(Africa/Bangui,WAT,01:00:00,f)
(Africa/Ouagadougou,GMT,00:00:00,f)
(Africa/Windhoek,WAST,02:00:00,t)
(Africa/Conakry,GMT,00:00:00,f)
(Africa/Ceuta,CEST,02:00:00,t)
(Africa/Luanda,WAT,01:00:00,f)
(Africa/Dakar,GMT,00:00:00,f)
(Africa/Lusaka,CAT,02:00:00,f)
(Africa/Mogadishu,EAT,03:00:00,f)
(Africa/Tunis,CET,01:00:00,f)
(Africa/Kinshasa,WAT,01:00:00,f)
(Africa/Tripoli,CEST,02:00:00,t)
(Africa/Niamey,WAT,01:00:00,f)
(Africa/Bujumbura,CAT,02:00:00,f)
(Africa/Bamako,GMT,00:00:00,f)
(Africa/Kampala,EAT,03:00:00,f)
(Africa/Casablanca,WET,00:00:00,f)
(Africa/Kigali,CAT,02:00:00,f)
(Africa/Ndjamena,WAT,01:00:00,f)
(Africa/Bissau,GMT,00:00:00,f)
(Africa/Timbuktu,GMT,00:00:00,f)
(Africa/Mbabane,SAST,02:00:00,f)
(Africa/El_Aaiun,WET,00:00:00,f)
(Africa/Libreville,WAT,01:00:00,f)
(Africa/Harare,CAT,02:00:00,f)
(Africa/Brazzaville,WAT,01:00:00,f)
(EST5EDT,EDT,-04:00:00,t)
(CST6CDT,CDT,-05:00:00,t)
(PST8PDT,PDT,-07:00:00,t)
(America/Boa_Vista,AMT,-04:00:00,f)
(America/Edmonton,MDT,-06:00:00,t)
(America/Louisville,EDT,-04:00:00,t)
(America/Mendoza,ART,-03:00:00,f)
(America/Menominee,CDT,-05:00:00,t)
(America/Cordoba,ART,-03:00:00,f)
(America/Rio_Branco,AMT,-04:00:00,f)
(America/Costa_Rica,CST,-06:00:00,f)
(America/Moncton,ADT,-03:00:00,t)
(America/Thule,ADT,-03:00:00,t)
(America/Managua,CST,-06:00:00,f)
(America/Barbados,AST,-04:00:00,f)
(America/Santarem,BRT,-03:00:00,f)
(America/Martinique,AST,-04:00:00,f)
(America/Kentucky/Louisville,EDT,-04:00:00,t)
(America/Kentucky/Monticello,EDT,-04:00:00,t)
(America/Eirunepe,AMT,-04:00:00,f)
(America/Belize,CST,-06:00:00,f)
(America/Resolute,CDT,-05:00:00,t)
(America/Lower_Princes,AST,-04:00:00,f)
(America/Adak,HADT,-09:00:00,t)
(America/Santiago,CLST,-03:00:00,t)
(America/Catamarca,ART,-03:00:00,f)
(America/Sao_Paulo,BRT,-03:00:00,f)
(America/Swift_Current,CST,-06:00:00,f)
(America/Shiprock,MDT,-06:00:00,t)
(America/Nassau,EDT,-04:00:00,t)
(America/Anchorage,AKDT,-08:00:00,t)
(America/Porto_Acre,AMT,-04:00:00,f)
(America/Halifax,ADT,-03:00:00,t)
(America/Dominica,AST,-04:00:00,f)
(America/St_Vincent,AST,-04:00:00,f)
(America/Dawson,PDT,-07:00:00,t)
(America/Montserrat,AST,-04:00:00,f)
(America/Noronha,FNT,-02:00:00,f)
(America/Mexico_City,CDT,-05:00:00,t)
(America/Indiana/Petersburg,EDT,-04:00:00,t)
(America/Indiana/Winamac,EDT,-04:00:00,t)
(America/Indiana/Vincennes,EDT,-04:00:00,t)
(America/Indiana/Vevay,EDT,-04:00:00,t)
(America/Indiana/Indianapolis,EDT,-04:00:00,t)
(America/Indiana/Tell_City,CDT,-05:00:00,t)
(America/Indiana/Marengo,EDT,-04:00:00,t)
(America/Indiana/Knox,CDT,-05:00:00,t)
(America/Glace_Bay,ADT,-03:00:00,t)
(America/Vancouver,PDT,-07:00:00,t)
(America/Guayaquil,ECT,-05:00:00,f)
(America/Virgin,AST,-04:00:00,f)
(America/Iqaluit,EDT,-04:00:00,t)
(America/Asuncion,PYT,-04:00:00,f)
(America/Danmarkshavn,GMT,00:00:00,f)
(America/Metlakatla,MeST,-08:00:00,f)
(America/Kralendijk,AST,-04:00:00,f)
(America/Guatemala,CST,-06:00:00,f)
(America/Thunder_Bay,EDT,-04:00:00,t)
(America/Fortaleza,BRT,-03:00:00,f)
(America/Port-au-Prince,EDT,-04:00:00,t)
(America/Ensenada,PDT,-07:00:00,t)
(America/Scoresbysund,EGST,00:00:00,t)
(America/Merida,CDT,-05:00:00,t)
(America/Nome,AKDT,-08:00:00,t)
(America/Godthab,WGST,-02:00:00,t)
(America/Yellowknife,MDT,-06:00:00,t)
(America/Coral_Harbour,EST,-05:00:00,f)
(America/Havana,CDT,-04:00:00,t)
(America/Manaus,AMT,-04:00:00,f)
(America/Denver,MDT,-06:00:00,t)
(America/Marigot,AST,-04:00:00,f)
(America/Yakutat,AKDT,-08:00:00,t)
(America/New_York,EDT,-04:00:00,t)
(America/Miquelon,PMDT,-02:00:00,t)
(America/Monterrey,CDT,-05:00:00,t)
(America/Jujuy,ART,-03:00:00,f)
(America/Belem,BRT,-03:00:00,f)
(America/Antigua,AST,-04:00:00,f)
(America/Ojinaga,MDT,-06:00:00,t)
(America/Cancun,CDT,-05:00:00,t)
(America/North_Dakota/Beulah,CDT,-05:00:00,t)
(America/North_Dakota/Center,CDT,-05:00:00,t)
(America/North_Dakota/New_Salem,CDT,-05:00:00,t)
(America/Grenada,AST,-04:00:00,f)
(America/Rainy_River,CDT,-05:00:00,t)
(America/Indianapolis,EDT,-04:00:00,t)
(America/Cambridge_Bay,MDT,-06:00:00,t)
(America/Santa_Isabel,PDT,-07:00:00,t)
(America/Toronto,EDT,-04:00:00,t)
(America/Paramaribo,SRT,-03:00:00,f)
(America/Montreal,EDT,-04:00:00,t)
(America/Buenos_Aires,ART,-03:00:00,f)
(America/Regina,CST,-06:00:00,f)
(America/Porto_Velho,AMT,-04:00:00,f)
(America/Atka,HADT,-09:00:00,t)
(America/Port_of_Spain,AST,-04:00:00,f)
(America/Juneau,AKDT,-08:00:00,t)
(America/St_Johns,NDT,-02:30:00,t)
(America/Inuvik,MDT,-06:00:00,t)
(America/Grand_Turk,EDT,-04:00:00,t)
(America/Goose_Bay,ADT,-03:00:00,t)
(America/Caracas,VET,-04:30:00,f)
(America/Los_Angeles,PDT,-07:00:00,t)
(America/Campo_Grande,AMT,-04:00:00,f)
(America/Dawson_Creek,MST,-07:00:00,f)
(America/Recife,BRT,-03:00:00,f)
(America/Santo_Domingo,AST,-04:00:00,f)
(America/Cayman,EST,-05:00:00,f)
(America/Winnipeg,CDT,-05:00:00,t)
(America/St_Lucia,AST,-04:00:00,f)
(America/Guadeloupe,AST,-04:00:00,f)
(America/La_Paz,BOT,-04:00:00,f)
(America/Rosario,ART,-03:00:00,f)
(America/Aruba,AST,-04:00:00,f)
(America/Matamoros,CDT,-05:00:00,t)
(America/Tegucigalpa,CST,-06:00:00,f)
(America/Whitehorse,PDT,-07:00:00,t)
(America/Cayenne,GFT,-03:00:00,f)
(America/Sitka,AKDT,-08:00:00,t)
(America/Atikokan,EST,-05:00:00,f)
(America/Anguilla,AST,-04:00:00,f)
(America/Bogota,COT,-05:00:00,f)
(America/Curacao,AST,-04:00:00,f)
(America/Chicago,CDT,-05:00:00,t)
(America/Puerto_Rico,AST,-04:00:00,f)
(America/Araguaina,BRT,-03:00:00,f)
(America/St_Thomas,AST,-04:00:00,f)
(America/Phoenix,MST,-07:00:00,f)
(America/Nipigon,EDT,-04:00:00,t)
(America/Chihuahua,MDT,-06:00:00,t)
(America/Detroit,EDT,-04:00:00,t)
(America/Fort_Wayne,EDT,-04:00:00,t)
(America/Maceio,BRT,-03:00:00,f)
(America/Tijuana,PDT,-07:00:00,t)
(America/El_Salvador,CST,-06:00:00,f)
(America/Blanc-Sablon,AST,-04:00:00,f)
(America/St_Barthelemy,AST,-04:00:00,f)
(America/Knox_IN,CDT,-05:00:00,t)
(America/Creston,MST,-07:00:00,f)
(America/Bahia_Banderas,CDT,-05:00:00,t)
(America/Boise,MDT,-06:00:00,t)
(America/Tortola,AST,-04:00:00,f)
(America/Pangnirtung,EDT,-04:00:00,t)
(America/Rankin_Inlet,CDT,-05:00:00,t)
(America/Jamaica,EST,-05:00:00,f)
(America/St_Kitts,AST,-04:00:00,f)
(America/Guyana,GYT,-04:00:00,f)
(America/Lima,PET,-05:00:00,f)
(America/Argentina/Mendoza,ART,-03:00:00,f)
(America/Argentina/Cordoba,ART,-03:00:00,f)
(America/Argentina/La_Rioja,ART,-03:00:00,f)
(America/Argentina/Salta,ART,-03:00:00,f)
(America/Argentina/San_Luis,WARST,-03:00:00,t)
(America/Argentina/Catamarca,ART,-03:00:00,f)
(America/Argentina/San_Juan,ART,-03:00:00,f)
(America/Argentina/ComodRivadavia,ART,-03:00:00,f)
(America/Argentina/Jujuy,ART,-03:00:00,f)
(America/Argentina/Buenos_Aires,ART,-03:00:00,f)
(America/Argentina/Tucuman,ART,-03:00:00,f)
(America/Argentina/Rio_Gallegos,ART,-03:00:00,f)
(America/Argentina/Ushuaia,ART,-03:00:00,f)
(America/Mazatlan,MDT,-06:00:00,t)
(America/Montevideo,UYT,-03:00:00,f)
(America/Hermosillo,MST,-07:00:00,f)
(America/Cuiaba,AMT,-04:00:00,f)
(America/Bahia,BRT,-03:00:00,f)
(America/Panama,EST,-05:00:00,f)
(GB-Eire,BST,01:00:00,t)
(Antarctica/Mawson,MAWT,05:00:00,f)
(Antarctica/Rothera,ROTT,-03:00:00,f)
(Antarctica/Davis,DAVT,07:00:00,f)
(Antarctica/McMurdo,NZDT,13:00:00,t)
(Antarctica/South_Pole,NZDT,13:00:00,t)
(Antarctica/Syowa,SYOT,03:00:00,f)
(Antarctica/Palmer,CLST,-03:00:00,t)
(Antarctica/Casey,WST,08:00:00,f)
(Antarctica/Macquarie,MIST,11:00:00,f)
(Antarctica/Vostok,VOST,06:00:00,f)
(Antarctica/DumontDUrville,DDUT,10:00:00,f)
(Portugal,WEST,01:00:00,t)
(Pacific/Fakaofo,TKT,13:00:00,f)
(Pacific/Chatham,CHADT,13:45:00,t)
(Pacific/Funafuti,TVT,12:00:00,f)
(Pacific/Johnston,HST,-10:00:00,f)
(Pacific/Samoa,SST,-11:00:00,f)
(Pacific/Galapagos,GALT,-06:00:00,f)
(Pacific/Noumea,NCT,11:00:00,f)
(Pacific/Palau,PWT,09:00:00,f)
(Pacific/Honolulu,HST,-10:00:00,f)
(Pacific/Wallis,WFT,12:00:00,f)
(Pacific/Port_Moresby,PGT,10:00:00,f)
(Pacific/Chuuk,CHUT,10:00:00,f)
(Pacific/Nauru,NRT,12:00:00,f)
(Pacific/Easter,EASST,-05:00:00,t)
(Pacific/Majuro,MHT,12:00:00,f)
(Pacific/Guam,ChST,10:00:00,f)
(Pacific/Pitcairn,PST,-08:00:00,f)
(Pacific/Tahiti,TAHT,-10:00:00,f)
(Pacific/Kiritimati,LINT,14:00:00,f)
(Pacific/Wake,WAKT,12:00:00,f)
(Pacific/Rarotonga,CKT,-10:00:00,f)
(Pacific/Truk,CHUT,10:00:00,f)
(Pacific/Enderbury,PHOT,13:00:00,f)
(Pacific/Guadalcanal,SBT,11:00:00,f)
(Pacific/Saipan,ChST,10:00:00,f)
(Pacific/Fiji,FJT,12:00:00,f)
(Pacific/Efate,VUT,11:00:00,f)
(Pacific/Marquesas,MART,-09:30:00,f)
(Pacific/Tarawa,GILT,12:00:00,f)
(Pacific/Pago_Pago,SST,-11:00:00,f)
(Pacific/Tongatapu,TOT,13:00:00,f)
(Pacific/Niue,NUT,-11:00:00,f)
(Pacific/Yap,CHUT,10:00:00,f)
(Pacific/Kwajalein,MHT,12:00:00,f)
(Pacific/Ponape,PONT,11:00:00,f)
(Pacific/Apia,WSDT,14:00:00,t)
(Pacific/Norfolk,NFT,11:30:00,f)
(Pacific/Auckland,NZDT,13:00:00,t)
(Pacific/Pohnpei,PONT,11:00:00,f)
(Pacific/Gambier,GAMT,-09:00:00,f)
(Pacific/Midway,SST,-11:00:00,f)
(Pacific/Kosrae,KOST,11:00:00,f)
(Chile/EasterIsland,EASST,-05:00:00,t)
(Chile/Continental,CLST,-03:00:00,t)
(GMT-0,GMT,00:00:00,f)
(Libya,CEST,02:00:00,t)
(MST,MST,-07:00:00,f)
(UCT,UCT,00:00:00,f)
(MET,MEST,02:00:00,t)
(Etc/GMT-9,GMT-9,09:00:00,f)
(Etc/GMT-3,GMT-3,03:00:00,f)
(Etc/GMT-8,GMT-8,08:00:00,f)
(Etc/GMT-0,GMT,00:00:00,f)
(Etc/GMT+11,GMT+11,-11:00:00,f)
(Etc/UCT,UCT,00:00:00,f)
(Etc/Greenwich,GMT,00:00:00,f)
(Etc/GMT-5,GMT-5,05:00:00,f)
(Etc/GMT-1,GMT-1,01:00:00,f)
(Etc/Universal,UTC,00:00:00,f)
(Etc/GMT+8,GMT+8,-08:00:00,f)
(Etc/GMT-12,GMT-12,12:00:00,f)
(Etc/Zulu,UTC,00:00:00,f)
(Etc/GMT+9,GMT+9,-09:00:00,f)
(Etc/UTC,UTC,00:00:00,f)
(Etc/GMT+4,GMT+4,-04:00:00,f)
(Etc/GMT-10,GMT-10,10:00:00,f)
(Etc/GMT-4,GMT-4,04:00:00,f)
(Etc/GMT-11,GMT-11,11:00:00,f)
(Etc/GMT,GMT,00:00:00,f)
(Etc/GMT+3,GMT+3,-03:00:00,f)
(Etc/GMT+0,GMT,00:00:00,f)
(Etc/GMT+10,GMT+10,-10:00:00,f)
(Etc/GMT-6,GMT-6,06:00:00,f)
(Etc/GMT+7,GMT+7,-07:00:00,f)
(Etc/GMT+6,GMT+6,-06:00:00,f)
(Etc/GMT-13,GMT-13,13:00:00,f)
(Etc/GMT0,GMT,00:00:00,f)
(Etc/GMT+12,GMT+12,-12:00:00,f)
(Etc/GMT-7,GMT-7,07:00:00,f)
(Etc/GMT+2,GMT+2,-02:00:00,f)
(Etc/GMT-14,GMT-14,14:00:00,f)
(Etc/GMT+5,GMT+5,-05:00:00,f)
(Etc/GMT-2,GMT-2,02:00:00,f)
(Etc/GMT+1,GMT+1,-01:00:00,f)
(Indian/Mahe,SCT,04:00:00,f)
(Indian/Maldives,MVT,05:00:00,f)
(Indian/Comoro,EAT,03:00:00,f)
(Indian/Chagos,IOT,06:00:00,f)
(Indian/Antananarivo,EAT,03:00:00,f)
(Indian/Mayotte,EAT,03:00:00,f)
(Indian/Cocos,CCT,06:30:00,f)
(Indian/Mauritius,MUT,04:00:00,f)
(Indian/Kerguelen,TFT,05:00:00,f)
(Indian/Reunion,RET,04:00:00,f)
(Indian/Christmas,CXT,07:00:00,f)
(MST7MDT,MDT,-06:00:00,t)
(W-SU,MSK,04:00:00,f)
(EET,EEST,03:00:00,t)
(Greenwich,GMT,00:00:00,f)
(Canada/Pacific,PDT,-07:00:00,t)
(Canada/Central,CDT,-05:00:00,t)
(Canada/Mountain,MDT,-06:00:00,t)
(Canada/Saskatchewan,CST,-06:00:00,f)
(Canada/Eastern,EDT,-04:00:00,t)
(Canada/Yukon,PDT,-07:00:00,t)
(Canada/Atlantic,ADT,-03:00:00,t)
(Canada/Newfoundland,NDT,-02:30:00,t)
(Canada/East-Saskatchewan,CST,-06:00:00,f)
(Iran,IRST,03:30:00,f)
(Arctic/Longyearbyen,CEST,02:00:00,t)
(Eire,IST,01:00:00,t)
(EST,EST,-05:00:00,f)
(GB,BST,01:00:00,t)
(PRC,CST,08:00:00,f)
(Universal,UTC,00:00:00,f)
(Mexico/BajaNorte,PDT,-07:00:00,t)
(Mexico/BajaSur,MDT,-06:00:00,t)
(Mexico/General,CDT,-05:00:00,t)
(Navajo,MDT,-06:00:00,t)
(Japan,JST,09:00:00,f)
(Iceland,GMT,00:00:00,f)
(Zulu,UTC,00:00:00,f)
(Israel,IDT,03:00:00,t)
(Singapore,SGT,08:00:00,f)
(Atlantic/Azores,AZOST,00:00:00,t)
(Atlantic/Jan_Mayen,CEST,02:00:00,t)
(Atlantic/Reykjavik,GMT,00:00:00,f)
(Atlantic/Madeira,WEST,01:00:00,t)
(Atlantic/South_Georgia,GST,-02:00:00,f)
(Atlantic/St_Helena,GMT,00:00:00,f)
(Atlantic/Cape_Verde,CVT,-01:00:00,f)
(Atlantic/Faroe,WEST,01:00:00,t)
(Atlantic/Bermuda,ADT,-03:00:00,t)
(Atlantic/Stanley,FKST,-03:00:00,f)
(Atlantic/Faeroe,WEST,01:00:00,t)
(Atlantic/Canary,WEST,01:00:00,t)
(UTC,UTC,00:00:00,f)
(NZ-CHAT,CHADT,13:45:00,t)
(ROC,CST,08:00:00,f)
(HST,HST,-10:00:00,f)
(Egypt,EET,02:00:00,f)
(GMT,GMT,00:00:00,f)
(Poland,CEST,02:00:00,t)
(NZ,NZDT,13:00:00,t)
(Hongkong,HKT,08:00:00,f)
(WET,WEST,01:00:00,t)
(GMT+0,GMT,00:00:00,f)
(Turkey,EEST,03:00:00,t)
(Kwajalein,MHT,12:00:00,f)
(CET,CEST,02:00:00,t)
(US/Samoa,SST,-11:00:00,f)
(US/Pacific,PDT,-07:00:00,t)
(US/East-Indiana,EDT,-04:00:00,t)
(US/Michigan,EDT,-04:00:00,t)
(US/Arizona,MST,-07:00:00,f)
(US/Hawaii,HST,-10:00:00,f)
(US/Central,CDT,-05:00:00,t)
(US/Mountain,MDT,-06:00:00,t)
(US/Eastern,EDT,-04:00:00,t)
(US/Indiana-Starke,CDT,-05:00:00,t)
(US/Pacific-New,PDT,-07:00:00,t)
(US/Aleutian,HADT,-09:00:00,t)
(US/Alaska,AKDT,-08:00:00,t)
(Brazil/DeNoronha,FNT,-02:00:00,f)
(Brazil/Acre,AMT,-04:00:00,f)
(Brazil/East,BRT,-03:00:00,f)
(Brazil/West,AMT,-04:00:00,f)
(Asia/Dubai,GST,04:00:00,f)
(Asia/Beirut,EEST,03:00:00,t)
(Asia/Macao,CST,08:00:00,f)
(Asia/Jayapura,EIT,09:00:00,f)
(Asia/Colombo,IST,05:30:00,f)
(Asia/Vladivostok,VLAT,11:00:00,f)
(Asia/Istanbul,EEST,03:00:00,t)
(Asia/Riyadh88,zzz,03:07:04,f)
(Asia/Novosibirsk,NOVT,07:00:00,f)
(Asia/Aqtau,AQTT,05:00:00,f)
(Asia/Tokyo,JST,09:00:00,f)
(Asia/Dacca,BDT,06:00:00,f)
(Asia/Ulaanbaatar,ULAT,08:00:00,f)
(Asia/Tbilisi,GET,04:00:00,f)
(Asia/Tehran,IRST,03:30:00,f)
(Asia/Ujung_Pandang,CIT,08:00:00,f)
(Asia/Pyongyang,KST,09:00:00,f)
(Asia/Karachi,PKT,05:00:00,f)
(Asia/Ashgabat,TMT,05:00:00,f)
(Asia/Bishkek,KGT,06:00:00,f)
(Asia/Bahrain,AST,03:00:00,f)
(Asia/Kuching,MYT,08:00:00,f)
(Asia/Seoul,KST,09:00:00,f)
(Asia/Sakhalin,SAKT,11:00:00,f)
(Asia/Hovd,HOVT,07:00:00,f)
(Asia/Riyadh,AST,03:00:00,f)
(Asia/Baghdad,AST,03:00:00,f)
(Asia/Manila,PHT,08:00:00,f)
(Asia/Calcutta,IST,05:30:00,f)
(Asia/Choibalsan,CHOT,08:00:00,f)
(Asia/Shanghai,CST,08:00:00,f)
(Asia/Dhaka,BDT,06:00:00,f)
(Asia/Khandyga,YAKT,10:00:00,f)
(Asia/Nicosia,EEST,03:00:00,t)
(Asia/Yerevan,AMT,04:00:00,f)
(Asia/Vientiane,ICT,07:00:00,f)
(Asia/Yekaterinburg,YEKT,06:00:00,f)
(Asia/Katmandu,NPT,05:45:00,f)
(Asia/Chongqing,CST,08:00:00,f)
(Asia/Riyadh89,zzz,03:07:04,f)
(Asia/Ust-Nera,VLAT,11:00:00,f)
(Asia/Aqtobe,AQTT,05:00:00,f)
(Asia/Almaty,ALMT,06:00:00,f)
(Asia/Dushanbe,TJT,05:00:00,f)
(Asia/Kashgar,CST,08:00:00,f)
(Asia/Singapore,SGT,08:00:00,f)
(Asia/Tel_Aviv,IDT,03:00:00,t)
(Asia/Irkutsk,IRKT,09:00:00,f)
(Asia/Phnom_Penh,ICT,07:00:00,f)
(Asia/Novokuznetsk,NOVT,07:00:00,f)
(Asia/Dili,TLT,09:00:00,f)
(Asia/Ulan_Bator,ULAT,08:00:00,f)
(Asia/Harbin,CST,08:00:00,f)
(Asia/Damascus,EEST,03:00:00,t)
(Asia/Yakutsk,YAKT,10:00:00,f)
(Asia/Jerusalem,IDT,03:00:00,t)
(Asia/Kolkata,IST,05:30:00,f)
(Asia/Anadyr,ANAT,12:00:00,f)
(Asia/Samarkand,UZT,05:00:00,f)
(Asia/Qatar,AST,03:00:00,f)
(Asia/Ho_Chi_Minh,ICT,07:00:00,f)
(Asia/Thimphu,BTT,06:00:00,f)
(Asia/Chungking,CST,08:00:00,f)
(Asia/Gaza,EET,02:00:00,f)
(Asia/Kathmandu,NPT,05:45:00,f)
(Asia/Ashkhabad,TMT,05:00:00,f)
(Asia/Qyzylorda,QYZT,06:00:00,f)
(Asia/Aden,AST,03:00:00,f)
(Asia/Baku,AZST,05:00:00,t)
(Asia/Magadan,MAGT,12:00:00,f)
(Asia/Pontianak,WIT,07:00:00,f)
(Asia/Macau,CST,08:00:00,f)
(Asia/Hong_Kong,HKT,08:00:00,f)
(Asia/Kuwait,AST,03:00:00,f)
(Asia/Brunei,BNT,08:00:00,f)
(Asia/Amman,EEST,03:00:00,t)
(Asia/Makassar,CIT,08:00:00,f)
(Asia/Urumqi,CST,08:00:00,f)
(Asia/Oral,ORAT,05:00:00,f)
(Asia/Kuala_Lumpur,MYT,08:00:00,f)
(Asia/Jakarta,WIT,07:00:00,f)
(Asia/Taipei,CST,08:00:00,f)
(Asia/Bangkok,ICT,07:00:00,f)
(Asia/Omsk,OMST,07:00:00,f)
(Asia/Saigon,ICT,07:00:00,f)
(Asia/Muscat,GST,04:00:00,f)
(Asia/Krasnoyarsk,KRAT,08:00:00,f)
(Asia/Tashkent,UZT,05:00:00,f)
(Asia/Riyadh87,zzz,03:07:04,f)
(Asia/Hebron,EET,02:00:00,f)
(Asia/Rangoon,MMT,06:30:00,f)
(Asia/Thimbu,BTT,06:00:00,f)
(Asia/Kabul,AFT,04:30:00,f)
(Asia/Kamchatka,PETT,12:00:00,f)
(GMT0,GMT,00:00:00,f)
(Mideast/Riyadh88,zzz,03:07:04,f)
(Mideast/Riyadh89,zzz,03:07:04,f)
(Mideast/Riyadh87,zzz,03:07:04,f)
(Australia/Yancowinna,CST,09:30:00,f)
(Australia/Brisbane,EST,10:00:00,f)
(Australia/South,CST,09:30:00,f)
(Australia/Eucla,CWST,08:45:00,f)
(Australia/Queensland,EST,10:00:00,f)
(Australia/Adelaide,CST,09:30:00,f)
(Australia/Darwin,CST,09:30:00,f)
(Australia/ACT,EST,10:00:00,f)
(Australia/Tasmania,EST,10:00:00,f)
(Australia/Currie,EST,10:00:00,f)
(Australia/Lindeman,EST,10:00:00,f)
(Australia/LHI,LHST,10:30:00,f)
(Australia/Canberra,EST,10:00:00,f)
(Australia/Perth,WST,08:00:00,f)
(Australia/Lord_Howe,LHST,10:30:00,f)
(Australia/West,WST,08:00:00,f)
(Australia/Melbourne,EST,10:00:00,f)
(Australia/NSW,EST,10:00:00,f)
(Australia/Victoria,EST,10:00:00,f)
(Australia/North,CST,09:30:00,f)
(Australia/Hobart,EST,10:00:00,f)
(Australia/Sydney,EST,10:00:00,f)
(Australia/Broken_Hill,CST,09:30:00,f)
(ROK,KST,09:00:00,f)
(Jamaica,EST,-05:00:00,f)
(Europe/Samara,SAMT,04:00:00,f)
(Europe/Oslo,CEST,02:00:00,t)
(Europe/Istanbul,EEST,03:00:00,t)
(Europe/Malta,CEST,02:00:00,t)
(Europe/Amsterdam,CEST,02:00:00,t)
(Europe/Simferopol,EEST,03:00:00,t)
(Europe/Brussels,CEST,02:00:00,t)
(Europe/Bratislava,CEST,02:00:00,t)
(Europe/Uzhgorod,EEST,03:00:00,t)
(Europe/Busingen,CEST,02:00:00,t)
(Europe/Mariehamn,EEST,03:00:00,t)
(Europe/Rome,CEST,02:00:00,t)
(Europe/Monaco,CEST,02:00:00,t)
(Europe/Warsaw,CEST,02:00:00,t)
(Europe/Stockholm,CEST,02:00:00,t)
(Europe/Copenhagen,CEST,02:00:00,t)
(Europe/Vaduz,CEST,02:00:00,t)
(Europe/Podgorica,CEST,02:00:00,t)
(Europe/Moscow,MSK,04:00:00,f)
(Europe/Madrid,CEST,02:00:00,t)
(Europe/Berlin,CEST,02:00:00,t)
(Europe/Tiraspol,EEST,03:00:00,t)
(Europe/Belgrade,CEST,02:00:00,t)
(Europe/Zaporozhye,EEST,03:00:00,t)
(Europe/Isle_of_Man,BST,01:00:00,t)
(Europe/Nicosia,EEST,03:00:00,t)
(Europe/Vienna,CEST,02:00:00,t)
(Europe/Zurich,CEST,02:00:00,t)
(Europe/Tallinn,EEST,03:00:00,t)
(Europe/Gibraltar,CEST,02:00:00,t)
(Europe/Skopje,CEST,02:00:00,t)
(Europe/Andorra,CEST,02:00:00,t)
(Europe/Sofia,EEST,03:00:00,t)
(Europe/Ljubljana,CEST,02:00:00,t)
(Europe/Chisinau,EEST,03:00:00,t)
(Europe/Tirane,CEST,02:00:00,t)
(Europe/London,BST,01:00:00,t)
(Europe/Prague,CEST,02:00:00,t)
(Europe/Dublin,IST,01:00:00,t)
(Europe/Lisbon,WEST,01:00:00,t)
(Europe/Guernsey,BST,01:00:00,t)
(Europe/Vilnius,EEST,03:00:00,t)
(Europe/Volgograd,VOLT,04:00:00,f)
(Europe/Luxembourg,CEST,02:00:00,t)
(Europe/Belfast,BST,01:00:00,t)
(Europe/Kiev,EEST,03:00:00,t)
(Europe/Minsk,FET,03:00:00,f)
(Europe/Vatican,CEST,02:00:00,t)
(Europe/Kaliningrad,FET,03:00:00,f)
(Europe/Sarajevo,CEST,02:00:00,t)
(Europe/Zagreb,CEST,02:00:00,t)
(Europe/Bucharest,EEST,03:00:00,t)
(Europe/Paris,CEST,02:00:00,t)
(Europe/Budapest,CEST,02:00:00,t)
(Europe/Jersey,BST,01:00:00,t)
(Europe/Athens,EEST,03:00:00,t)
(Europe/San_Marino,CEST,02:00:00,t)
(Europe/Helsinki,EEST,03:00:00,t)
(Europe/Riga,EEST,03:00:00,t)
(Cuba,CDT,-04:00:00,t)
(584 rows)また、サポートされているタイムゾーンの省略形の一覧を表示するにはpg_timezone_abbrevs()を使います。
# SELECT pg_timezone_abbrevs();
pg_timezone_abbrevs
---------------------
(ACSST,10:30:00,t)
(ACST,-04:00:00,t)
(ACT,-05:00:00,f)
(ADT,-03:00:00,t)
(AESST,11:00:00,t)
(AEST,10:00:00,f)
(AFT,04:30:00,f)
(AKDT,-08:00:00,t)
(AKST,-09:00:00,f)
(ALMST,07:00:00,t)
(ALMT,06:00:00,f)
(AMST,05:00:00,t)
(AMT,04:00:00,f)
(ANAST,13:00:00,t)
(ANAT,12:00:00,f)
(ARST,-02:00:00,t)
(ART,-03:00:00,f)
(AST,-04:00:00,f)
(AWSST,09:00:00,t)
(AWST,08:00:00,f)
(AZOST,00:00:00,t)
(AZOT,-01:00:00,f)
(AZST,05:00:00,t)
(AZT,04:00:00,f)
(BDST,02:00:00,t)
(BDT,06:00:00,f)
(BNT,08:00:00,f)
(BORT,08:00:00,f)
(BOT,-04:00:00,f)
(BRA,-03:00:00,f)
(BRST,-02:00:00,t)
(BRT,-03:00:00,f)
(BST,01:00:00,t)
(BTT,06:00:00,f)
(CADT,10:30:00,t)
(CAST,09:30:00,f)
(CCT,08:00:00,f)
(CDT,-05:00:00,t)
(CEST,02:00:00,t)
(CET,01:00:00,f)
(CETDST,02:00:00,t)
(CHADT,13:45:00,t)
(CHAST,12:45:00,f)
(CHUT,10:00:00,f)
(CKT,12:00:00,f)
(CLST,-03:00:00,t)
(CLT,-04:00:00,f)
(COT,-05:00:00,f)
(CST,-06:00:00,f)
(CXT,07:00:00,f)
(DAVT,07:00:00,f)
(DDUT,10:00:00,f)
(EASST,-05:00:00,t)
(EAST,-06:00:00,f)
(EAT,03:00:00,f)
(EDT,-04:00:00,t)
(EEST,03:00:00,t)
(EET,02:00:00,f)
(EETDST,03:00:00,t)
(EGST,00:00:00,t)
(EGT,-01:00:00,f)
(EST,-05:00:00,f)
(FET,03:00:00,f)
(FJST,-13:00:00,t)
(FJT,-12:00:00,f)
(FKST,-03:00:00,t)
(FKT,-04:00:00,f)
(FNST,-01:00:00,t)
(FNT,-02:00:00,f)
(GALT,-06:00:00,f)
(GAMT,-09:00:00,f)
(GEST,04:00:00,t)
(GET,04:00:00,f)
(GFT,-03:00:00,f)
(GILT,12:00:00,f)
(GMT,00:00:00,f)
(GYT,-04:00:00,f)
(HKT,08:00:00,f)
(HST,-10:00:00,f)
(ICT,07:00:00,f)
(IDT,03:00:00,t)
(IOT,06:00:00,f)
(IRKST,09:00:00,t)
(IRKT,09:00:00,f)
(IRT,03:30:00,f)
(IST,02:00:00,f)
(JAYT,09:00:00,f)
(JST,09:00:00,f)
(KDT,10:00:00,t)
(KGST,06:00:00,t)
(KGT,06:00:00,f)
(KOST,11:00:00,f)
(KRAST,08:00:00,t)
(KRAT,08:00:00,f)
(KST,09:00:00,f)
(LHDT,11:00:00,t)
(LHST,10:30:00,f)
(LIGT,10:00:00,f)
(LINT,14:00:00,f)
(LKT,06:00:00,f)
(MAGST,12:00:00,t)
(MAGT,12:00:00,f)
(MART,-09:30:00,f)
(MAWT,05:00:00,f)
(MDT,-06:00:00,t)
(MEST,02:00:00,t)
(MET,01:00:00,f)
(METDST,02:00:00,t)
(MEZ,01:00:00,f)
(MHT,12:00:00,f)
(MMT,06:30:00,f)
(MPT,10:00:00,f)
(MSD,04:00:00,t)
(MSK,04:00:00,f)
(MST,-07:00:00,f)
(MUST,05:00:00,t)
(MUT,04:00:00,f)
(MVT,05:00:00,f)
(MYT,08:00:00,f)
(NDT,-02:30:00,t)
(NFT,-03:30:00,f)
(NOVST,07:00:00,t)
(NOVT,07:00:00,f)
(NPT,05:45:00,f)
(NST,-03:30:00,f)
(NUT,-11:00:00,f)
(NZDT,13:00:00,t)
(NZST,12:00:00,f)
(NZT,12:00:00,f)
(OMSST,07:00:00,t)
(OMST,07:00:00,f)
(PDT,-07:00:00,t)
(PET,-05:00:00,f)
(PETST,13:00:00,t)
(PETT,12:00:00,f)
(PGT,10:00:00,f)
(PHOT,13:00:00,f)
(PHT,08:00:00,f)
(PKST,06:00:00,t)
(PKT,05:00:00,f)
(PMDT,-02:00:00,t)
(PMST,-03:00:00,f)
(PONT,11:00:00,f)
(PST,-08:00:00,f)
(PWT,09:00:00,f)
(PYST,-03:00:00,t)
(PYT,-04:00:00,f)
(RET,04:00:00,f)
(SADT,10:30:00,t)
(SAST,09:30:00,f)
(SCT,04:00:00,f)
(SGT,08:00:00,f)
(TAHT,-10:00:00,f)
(TFT,05:00:00,f)
(TJT,05:00:00,f)
(TKT,13:00:00,f)
(TMT,05:00:00,f)
(TOT,13:00:00,f)
(TRUT,10:00:00,f)
(TVT,12:00:00,f)
(UCT,00:00:00,f)
(ULAST,09:00:00,t)
(ULAT,08:00:00,f)
(UT,00:00:00,f)
(UTC,00:00:00,f)
(UYST,-02:00:00,t)
(UYT,-03:00:00,f)
(UZST,06:00:00,t)
(UZT,05:00:00,f)
(VET,-04:30:00,f)
(VLAST,11:00:00,t)
(VLAT,11:00:00,f)
(VOLT,04:00:00,f)
(VUT,11:00:00,f)
(WADT,08:00:00,t)
(WAKT,12:00:00,f)
(WAST,07:00:00,f)
(WAT,01:00:00,f)
(WDT,09:00:00,t)
(WET,00:00:00,f)
(WETDST,01:00:00,t)
(WFT,12:00:00,f)
(WGST,-02:00:00,t)
(WGT,-03:00:00,f)
(YAKST,10:00:00,t)
(YAKT,10:00:00,f)
(YAPT,10:00:00,f)
(YEKST,06:00:00,t)
(YEKT,06:00:00,f)
(Z,00:00:00,f)
(ZULU,00:00:00,f)
(191 rows)IANAにはタイムゾーンに関する情報も公開されていますので必要に応じてこちらの内容もご確認ください。
タイムゾーンのフォーマットとしては以下のような形がサポートされているようです。
| フォーマット | 例 |
|---|---|
| day mon hh:mm:ss yyyy tz | 17 Dec 07:37:16 1997 PST |
| mm/dd/yyyy hh:mm:ss.ss tz | 12/17/1997 07:37:16.00 PST |
| mm/dd/yyyy hh:mm:ss.ss tz | 12/17/1997 07:37:16.00 US/Pacific |
| yyyy-mm-dd hh:mm:ss+/-tz | 1997-12-17 07:37:16-08 |
| dd.mm.yyyy hh:mm:ss tz | 12.17.1997 07:37:16.00 PST |
データ型: TIMESTAMPTZに関するその他の詳細については以下のような挙動、内容があります。
- 『秒』の部分の小数部は最大桁数6桁までを格納します。
- TIMESTAMPTZ値はテーブル内ではUTCとして格納されます。
- 日付型データや部分的なタイムスタンプ情報を持つ日付型データをTIMESTAMPTZ型の列に投入すると、値は不足部分を補う形でタイムスタンプの値に暗黙的に変換されます。(下記はTIMESTAMP型で試した形ですが、挙動としてはTIMESTAMPTZ型も同じ内容かと)
# CREATE TABLE public.tztest ( tz TIMESTAMP );
CREATE TABLE
# INSERT INTO public.tztest VALUES('2016/10/01');
INSERT
# INSERT INTO public.tztest VALUES('2016/10/01 12:00');
INSERT
# INSERT INTO public.tztest VALUES('2016/10/01 12:34');
INSERT
# INSERT INTO public.tztest VALUES('2016/10/01 12:34:56');
INSERT
# SELECT * FROM public.tztest ORDER BY tz;
tz
---------------------
2016-10-01 00:00:00
2016-10-01 12:00:00
2016-10-01 12:34:00
2016-10-01 12:34:56
(4 rows)TIMESTAMPTZを元にした比較用の関数も新たに利用可能となるようです。(DATE_CMP_TIMESTAMPTZとTIMESTAMP_CMP_TIMESTAMPTZ)
- DATE_CMP_TIMESTAMPTZ Function - Amazon Redshift
- TIMESTAMP_CMP_TIMESTAMPTZ Function - Amazon Redshift
まとめ
新しいデータ型『TIMESTAMPTZ』が利用可能となる事で、異なるタイムゾーンのデータをより便利な形で一度に扱う事が可能となりました。複数のタイムゾーンに跨るようなデータ管理を行う場合、このデータ型を用いる事に拠って効果的・効率的な管理が出来そうですね。こちらからは以上です。
27
