日付データ演算の達人技を伝授する 【第3話】：SQLクリニック（4）（1/3 ページ）

[山下正，株式会社インサイトテクノロジー]

本連載はSQLの応用力を身に付けたいエンジニア向けに、さまざまなテクニックを紹介する。SQLの基本構文は平易なものだが、実務で活用するには教科書的な記述を理解するだけでは不十分だ。本連載は、著名なメールマガジン「おら！オラ！ Oracle - どっぷり検証生活」を発行するインサイトテクノロジーのコンサルタントを執筆陣に迎え、SQLのセンス向上に役立つ大技小技を紹介していく。（編集局）

　日付データに関する話題を集めた「日付データ演算の達人技を伝授する」は今回で3回目です。最後は、タイムゾーンに関する話題で締めくくりましょう。

データベースにおけるタイムゾーン

主な内容

--Page 1--

▼データベースにおけるタイムゾーン

--Page 2--

▼タイムゾーンの確認

▼タイムゾーンの変更

--Page 3--

▼タイムゾーンデータのエクスポート／インポート

▼日本のタイムゾーン（LMT、JST、CJTの話）

　タイムゾーンという言葉自体については、おそらく何らかのイメージがあると思います。ここではデータベースで利用される簡単な例をお話しします。あまりにもシンプルすぎて現実に即していない面があるかもしれませんが、ご容赦ください。次のようなケースを考えます。

　アメリカから発送した部品と、シンガポールから発送した部品を日本で組み立てて、その後完成品としてイギリスへ届けるというストーリーです。

図1　複数の国にまたがったビジネスフロー

　では、これらに時刻を当てはめて、最終的にイギリスにはいつ届くのかを見てみましょう。

前提条件
　 アメリカから日本への輸送に12時間
　 シンガポールから日本への輸送に8時間
　 日本での組み立てに24時間
　 日本からイギリスへの輸送に20時間

　部品の発送状況を確認してみましょう。データベース自体は日本にあり、すべての情報はこのデータベースにあるとします。このデータベースを検索すると部品が発送された日時が検索できます。

アメリカからの部品発送→現地時刻の1月10日の午前11時

シンガポールからの部品発送→現地時刻の1月11日の午後4時

　日本は現在、1月11日の午後4時です。さて、日本にはいつ部品がそろうのでしょうか？ イギリスのユーザーの手元には何時ごろに完成品が到着するのでしょうか？

図2　タイムゾーンを考慮しないデータベース設計（各ローカルタイムをデータとして保持する）

　アメリカとシンガポールから部品が発送された時刻が登録されています。日本での組み立て作業は、後に到着した部品を待って開始されるはずです。どちらの部品が後で到着するのでしょうか？

一見しただけでは分かりづらいですね。

　日付データが各地の時刻で登録されているために、それぞれの時刻の関連が分かりにくくなっています。ではこれらをすべて日本時刻に変換して考えてみましょう。

アメリカからの部品発送→日本時刻の1月11日の午前4時

シンガポールからの部品発送→日本時刻の1月11日の午後5時

　ということは、アメリカから部品が到着するのは、12時間後の1月11日の午後4時。シンガポールから部品が到着するのは、8時間後の1月12日の午前1時（アメリカからの部品はすでに到着しているはずですね）。

　つまりシンガポールからの部品が後で到着するはずです。それから組み立てを開始して、完成するのが24時間後の1月13日の午前1時。ということはイギリスに到着するのがそれから20時間後の、1月13日の午後9時となります。現地時刻で1月13日の正午ですね。

　上記のように時刻計算を単一のタイムゾーンで行い、検索結果の表示についてはユーザーのタイムゾーンに応じた表示ができると非常に分かりやすく便利です。

　前置きが長くなりましたが、これを実現してくれる機能がタイムゾーンです。

　上記の場合、データベース・タイムゾーンが日本時刻に相当し、各ユーザーが確認する時刻はそれぞれのセッション・タイムゾーンで確認しています。

図3　タイムゾーンを考慮したデータベース設計（各タイムゾーンを考慮してデータを保持する）

　上記のようにタイムゾーンを使用する場合の具体な設定について例を示します。注意すべき点は、以下の3点です。

1. データベース・タイムゾーン（注1）："Japan"
2. セッション・タイムゾーン（注2）：各クライアントの地域のタイムゾーン
3. 時刻データをすべてTIMESTAMP WITH LOCAL TIME ZONE型の列に格納

　もう少しいうと、上記のデータベース・タイムゾーンについてオラクル社の推奨は「UTC」のようですが。

タイムゾーンの種類

注1 　データベース・タイムゾーン

CREATE DATABASEの際に決定する、文字どおりデータベースのタイムゾーンです。このタイムゾーンは、デフォルトではOSのタイムゾーンに設定されます。このタイムゾーンが影響するのはTIMESTAMP WITH LOCAL TIME ZONE型の列に格納されているデータです。

注2　セッション・タイムゾーン

データベースに接続するセッションごとにタイムゾーンの設定が可能です。セッション・タイムゾーンはクライアント側の環境変数ORA_SDTZで設定されますが、この環境変数が定義されていない場合は、データベース・タイムゾーンがデフォルトとして使用されます。ユーザーの地域によって時刻を現地時刻に合わせて表示させるような場合は、このタイムゾーンが影響します。

なお、Webサーバやアプリケーションサーバを介すような構成の場合は、セッション・タイムゾーンがWebサーバやアプリケーションサーバのセッションに対して有効になるため、エンドユーザーの環境とは関係なくなってしまい、効果がありません。

　INSERTするときも、SELECTするときも、アプリケーション側では特にタイムゾーンを意識する必要はありません。もちろんサーバのタイムゾーンを含めてタイムゾーンに関する設定が正しくされていることが大前提です。

図4　セッション・タイムゾーンとデータベース・タイムゾーン

　TIMESTAMP WITH LOCAL TIME ZONE型の列には、ユーザー環境とは関係なくすべてデータベース・タイムゾーンに変換された日時情報が格納されます。なので、世界中の日付時刻のデータをTIMESTAMP WITH LOCAL TIME ZONE型の列に格納すると、必然的に単一のタイムゾーンでデータを扱うことになるのです。

　さらに、TIMESTAMP WITH LOCAL TIME ZONE型の列に格納されたデータは、ユーザーセッションのタイムゾーンに従って変換されてクライアント側に表示されます。

　That's all.

　上記のほかにも、タイムゾーンの情報を格納できるデータ型があります。TIMESTAMP WITH TIME ZONE型です。こちらは、データを投入したアプリケーション（セッション）の時刻と、そのタイムゾーン情報が格納されます。このデータを検索すると、自分のタイムゾーン（検索したセッションのタイムゾーン）とは関係なく、データベースに格納されている日時とタイムゾーンがそのまま検索できます。必要に応じて使い分けてください。（次ページへ続く）