タイムゾーン de 自動調整 Tweet
Python Challengeは気が向いた時にやるとして、今日は、タイムゾーンで日時の自動調整をして楽をしようというお話。
Oracleには、timestamp with local time zone型（マニュアルの説明はわかりにくいかも)という便利な日時データ型があります。この日時データ型で日時を保存すると、クライアント側ではクライアントのタイムゾーンに調整された日時を参照することができるようになります。
timestamp with local time zone型はデータベースの作成時に指定されたタイムゾーンで日時データを保持（クライアント側とタイムゾーンが異なる場合はデータベース側のタイムゾーンへ自動調整されます）します。
言葉で言っても分かり憎いでしょうから、早速、例をお見せしましょう。 といっても少々変った例なので（Oracleの）SQL初心者向きではないですが。。
●●●この例の環境及び条件の説明●●●
データベース（この例の場合はOracle10g R1）のタイムゾーン(DBTIMEZONE)は、UTC。
クライアント（この例の場合はSQL*Plus）のタイムゾーン(SESSIONTIMEZONE)は、+9（つまりJST)とUTCをalter sessionにて変更する。
ts_test表には、TIMESTAMP WITH LOCAL TIME ZONE型（今回の場合はUTCで）で２／２８から３／２７までのあるアクティビティのログ（日時データのみ）が記録されている。（尚、アクティビティの無い日のログは記録されていない。）
というデータ、環境で、実行当日を含む直近２７日分のアクティビティ数を日毎に集計して日付順に取得する。アクティビティが無い日はアクティビティ数を０として日付を表示する。
（但し、ts_test以外の表は使わずに実現する。必須じゃないけど、できればSQL１文で！）
では、早速。
データベースタイムゾーン(DBTIMEZONE)は、UTCであることを確認。
（ちなみに、データベースに既にTIMESTAMP WITH LOCAL TIME ZONE型を持つ表が存在しなければ、alter database文で変更可能。)
Last login: Fri Mar 28 05:01:29 on console
Welcome to Darwin!
G5Server:˜ discus$ su - oracle
Password:
G5Server:˜ oracle$
G5Server:˜ oracle$ sqlplus /nolog
SQL*Plus: Release 10.1.0.3.0 - Production on 金 3月 28 07:52:49 2008
Copyright (c) 1982, 2004, Oracle. All rights reserved.
>
> conn scott/tiger@catfish
接続されました。
SCOTT> select dbtimezone from dual;
DBT
---
UTC
次にクライアントのタイムゾーン(SESSIONTIMEZONE)を確認。(JSTになっている）。
SCOTT> select sessiontimezone from dual;
SESSIONTIMEZONE
-----------------------------------------------------------------
+09:00
ts_test表の登録データを確認する。（クライアントのタイムゾーン(JST)へ自動調整されて表示される。）
TIMESTAMP WITH LOCAL TIME ZONE型として登録されている日時データはクライアントのタイムゾーンに自動調整されて表示される。
簡単に確認する方法としてCURRENT_TIMESTAMP疑似列を問い合わせて現状を確認することがもできる。
SCOTT> select current_timestamp from dual;
CURRENT_TIMESTAMP
-----------------------------------------------------------------
08-03-28 08:07:33.082296 +09:00
SCOTT> desc ts_test
名前 NULL? 型
-------------------------- -------- ----------------------------
TS_LTZ TIMESTAMP(6) WITH LOCAL TIME
ZONE
SCOTT> select * from ts_test order by ts_ltz;
TS_LTZ
-----------------------------------------------------------------
08-02-29 00:00:00.000000
08-02-29 23:59:59.999999
08-03-01 00:00:00.000000
08-03-01 23:59:59.999999
08-03-02 00:00:00.000000
08-03-02 23:59:59.999999
08-03-03 00:00:00.000000
08-03-03 23:59:59.999999
08-03-04 00:00:00.000000
08-03-04 23:59:59.999999
08-03-05 00:00:00.000000
08-03-05 23:59:59.999999
08-03-06 00:00:00.000000
08-03-06 23:59:59.999999
08-03-07 00:00:00.000000
08-03-07 23:59:59.999999
08-03-08 00:00:00.000000
08-03-08 23:59:59.999999
08-03-09 00:00:00.000000
08-03-09 23:59:59.999999
08-03-10 00:00:00.000000
08-03-10 23:59:59.999999
08-03-11 00:00:00.000000
08-03-11 23:59:59.999999
08-03-12 00:00:00.000000
08-03-12 23:59:59.999999
08-03-13 00:00:00.000000
08-03-13 23:59:59.999999
08-03-14 00:00:00.000000
08-03-14 23:59:59.999999
08-03-15 00:00:00.000000
08-03-15 23:59:59.999999
08-03-16 00:00:00.000000
08-03-16 23:59:59.999999
08-03-17 00:00:00.000000
08-03-17 23:59:59.999999
08-03-18 00:00:00.000000
08-03-18 23:59:59.999999
08-03-19 00:00:00.000000
08-03-19 23:59:59.999999
08-03-20 00:00:00.000000
08-03-20 23:59:59.999999
08-03-21 00:00:00.000000
08-03-21 23:59:59.999999
08-03-22 00:00:00.000000
08-03-22 23:59:59.999999
08-03-23 00:00:00.000000
08-03-23 23:59:59.999999
08-03-25 00:00:00.000000
08-03-25 23:59:59.999999
08-03-26 00:00:00.000000
08-03-26 23:59:59.999999
08-03-27 08:59:59.999999
08-03-27 09:00:00.000000
08-03-27 10:12:11.928490
55行が選択されました。
SCOTT>
では、上記のデータを直近２７日分のアクティビティ数を日毎に集計。アクティビティの無い日はアクティビティ数を０として問い合わせるSQL文。
以下の例では、Oracle独自の階層問合せのlevel疑似列を利用しSYSDATEから２７日分のカレンダーを生成しログと外部結合することで、ログデータが存在していない日の
アクティビティ数を０として取得している。
SCOTT> l
1 select
2 cal."date",
3 nvl(logs."activity",0) as "activity"
4 from
5 (
6 select
7 "date",
8 count(ts_ltz) as "activity"
9 from
10 (
11 select
12 to_char(ts_ltz, 'YYYY/MM/DD') as "date",
13 ts_ltz
14 from
15 ts_test
16 )
17 group by
18 "date"
19 ) logs
20 right outer join
21 (
22 select
23 to_char(trunc(sysdate) - (level - 1),
24 'YYYY/MM/DD') as "date"
25 from
26 dual
27 where
28 to_char(trunc(sysdate) - (level - 1),
29 'YYYY/MM/DD') <= trunc(sysdate)
30 connect by
31 level <= 27
32 ) cal
33 on logs."date" = cal."date"
34 order by
35* cal."date"
SCOTT> /
date activity
---------- ----------
2008/03/02 2
2008/03/03 2
2008/03/04 2
2008/03/05 2
2008/03/06 2
2008/03/07 2
2008/03/08 2
2008/03/09 2
2008/03/10 2
2008/03/11 2
2008/03/12 2
2008/03/13 2
2008/03/14 2
2008/03/15 2
2008/03/16 2
2008/03/17 2
2008/03/18 2
2008/03/19 2
2008/03/20 2
2008/03/21 2
2008/03/22 2
2008/03/23 2
2008/03/24 0
2008/03/25 2
2008/03/26 2
2008/03/27 3
2008/03/28 0
27行が選択されました。
SCOTT>
では、クライアントのタイムゾーンをJSTからUTCへ変更してみましょう。(表示しているデータは前述の例と同じts_test表の内容ですがUTCに自動調整されて表示されています。)
SCOTT> alter session set time_zone='UTC';
セッションが変更されました。
SCOTT> select * from ts_test order by ts_ltz;
TS_LTZ
-------------------------------------------------------
08-02-28 15:00:00.000000
08-02-29 14:59:59.999999
08-02-29 15:00:00.000000
08-03-01 14:59:59.999999
08-03-01 15:00:00.000000
08-03-02 14:59:59.999999
08-03-02 15:00:00.000000
08-03-03 14:59:59.999999
08-03-03 15:00:00.000000
08-03-04 14:59:59.999999
08-03-04 15:00:00.000000
08-03-05 14:59:59.999999
08-03-05 15:00:00.000000
08-03-06 14:59:59.999999
08-03-06 15:00:00.000000
08-03-07 14:59:59.999999
08-03-07 15:00:00.000000
08-03-08 14:59:59.999999
08-03-08 15:00:00.000000
08-03-09 14:59:59.999999
08-03-09 15:00:00.000000
08-03-10 14:59:59.999999
08-03-10 15:00:00.000000
08-03-11 14:59:59.999999
08-03-11 15:00:00.000000
08-03-12 14:59:59.999999
08-03-12 15:00:00.000000
08-03-13 14:59:59.999999
08-03-13 15:00:00.000000
08-03-14 14:59:59.999999
08-03-14 15:00:00.000000
08-03-15 14:59:59.999999
08-03-15 15:00:00.000000
08-03-16 14:59:59.999999
08-03-16 15:00:00.000000
08-03-17 14:59:59.999999
08-03-17 15:00:00.000000
08-03-18 14:59:59.999999
08-03-18 15:00:00.000000
08-03-19 14:59:59.999999
08-03-19 15:00:00.000000
08-03-20 14:59:59.999999
08-03-20 15:00:00.000000
08-03-21 14:59:59.999999
08-03-21 15:00:00.000000
08-03-22 14:59:59.999999
08-03-22 15:00:00.000000
08-03-23 14:59:59.999999
08-03-24 15:00:00.000000
08-03-25 14:59:59.999999
08-03-25 15:00:00.000000
08-03-26 14:59:59.999999
08-03-26 23:59:59.999999
08-03-27 00:00:00.000000
08-03-27 01:12:11.928490
55行が選択されました。
SCOTT>
SCOTT> l
1 select
2 cal."date",
3 nvl(logs."activity",0) as "activity"
4 from
5 (
6 select
7 "date",
8 count(ts_ltz) as "activity"
9 from
10 (
11 select
12 to_char(ts_ltz, 'YYYY/MM/DD') as "date",
13 ts_ltz
14 from
15 ts_test
16 )
17 group by
18 "date"
19 ) logs
20 right outer join
21 (
22 select
23 to_char(trunc(sysdate) - (level - 1),
24 'YYYY/MM/DD') as "date"
25 from
26 dual
27 where
28 to_char(trunc(sysdate) - (level - 1),
29 'YYYY/MM/DD') <= trunc(sysdate)
30 connect by
31 level <= 27
32 ) cal
33 on logs."date" = cal."date"
34 order by
35* cal."date"
SCOTT> /
date activity
---------- ----------
2008/03/02 2
2008/03/03 2
2008/03/04 2
2008/03/05 2
2008/03/06 2
2008/03/07 2
2008/03/08 2
2008/03/09 2
2008/03/10 2
2008/03/11 2
2008/03/12 2
2008/03/13 2
2008/03/14 2
2008/03/15 2
2008/03/16 2
2008/03/17 2
2008/03/18 2
2008/03/19 2
2008/03/20 2
2008/03/21 2
2008/03/22 2
2008/03/23 1
2008/03/24 1
2008/03/25 2
2008/03/26 2
2008/03/27 2
2008/03/28 0
27行が選択されました。
SCOTT>
はい、データベース側でクライアントのタイムゾーンに日時を自動調整してくれるお陰でタイムゾーンが変っても同一SQL文で処理できます。（楽でしょ。）
次回は、同じことを集計関数cubeを使って実現するSQL文の例をお見せします。(Oracle de Fizzbuzz #2を見れば想像できてしまうとは思いますが。。）
ところで、MySQLやPostgreSQLなど他のデータベースではどうするんだろう。。余裕があったら調べてみるか。。。。。
2008/3/29追記
当日から必要な日数分の過去日付を求める階層問合せ部分はもっと簡単にできることに今気付いた。帰宅したら改善したクエリも追加する予定！
ということで以下のように、階層問合せのwhere句の部分は無くてもOKということだったのです。はい。
SCOTT> l
1 select
2 cal."date",
3 nvl(logs."activity",0) as "activity"
4 from
5 (
6 select
7 "date",
8 count(ts_ltz) as "activity"
9 from
10 (
11 select
12 to_char(ts_ltz, 'YYYY/MM/DD') as "date",
13 ts_ltz
14 from
15 ts_test
16 )
17 group by
18 "date"
19 ) logs
20 right outer join
21 (
22 select
23 to_char(
24 trunc(sysdate) - (level - 1),
25 'YYYY/MM/DD'
26 ) as "date"
27 from
28 dual
29 connect by
30 level <= 27
31 ) cal
32 on logs."date" = cal."date"
33 order by
34* cal."date"
SCOTT> /
date activity
---------- ----------
2008/03/03 2
2008/03/04 2
2008/03/05 2
2008/03/06 2
2008/03/07 2
2008/03/08 2
2008/03/09 2
2008/03/10 2
2008/03/11 2
2008/03/12 2
2008/03/13 2
2008/03/14 2
2008/03/15 2
2008/03/16 2
2008/03/17 2
2008/03/18 2
2008/03/19 2
2008/03/20 2
2008/03/21 2
2008/03/22 2
2008/03/23 2
2008/03/24 0
2008/03/25 2
2008/03/26 2
2008/03/27 3
2008/03/28 0
2008/03/29 0
27行が選択されました。
SCOTT>
| 固定リンク | 0
コメント