DATEデータ型の列を問合わせたときの予期しないソート順序 - Teradata Database - Teradata Vantage NewSQL Engine - DATEデータ型の列の予期しないソート順序、SELECT SQL文

Teradata Vantage™ SQLデータ操作言語
Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
2019年3月
Language
日本語
Last Update
2019-10-29
dita:mapPath
ja-JP/fbo1512081269404.ditamap
dita:ditavalPath
ja-JP/fbo1512081269404.ditaval
dita:id
B035-1146
Product Category
Software
Teradata Vantage

ORDER BY句を使用してDATEデータ型の列を問合わせると、返された値の順序が一見正しくない結果が生成される場合があります。

このタイプの予期しない結果が生成される要因は、以下のとおりです。
  • DATE時間値は、システムによってローカル時間ではなくUTC時間でディスク上に格納される。
  • ローカル時間値ではなくUTC時間値が列値のソートに使用される。
  • DATE時間値は、システムのタイムゾーン設定に影響される。システムのタイムゾーン設定は、TimeZoneStringまたはDBSControl一般フィールド16を使用して設定できます。
  • 日付境界を越えるDATE時間値は、システムのタイムゾーンのタイムゾーン オフセットに基づいて調整される(タイムゾーン オフセットはタイムゾーンによって異なります)。

予期しないソート順序の例

この例では、ORDER BY句で指定した順序でソートされてないように見えるTIMEデータ型列値の結果セットを示します。列に挿入されたローカル時間値の後に、問合わせで返された値があります。

この例のタイムゾーンは太平洋標準時であり、タイムゾーン オフセットは+8時間です。

CREATE TABLE table_1
(column_1 INTEGER, 
column_2 TIME);
INSERT INTO table_1 VALUEs ( 0, TIME'00:00:00.000000'); 
INSERT INTO table_1 VALUEs ( 1, TIME'01:00:00.000000'); 
INSERT INTO table_1 VALUEs ( 2, TIME'02:00:00.000000'); 
INSERT INTO table_1 VALUEs ( 3, TIME'03:00:00.000000'); 
INSERT INTO table_1 VALUEs ( 4, TIME'04:00:00.000000'); 
INSERT INTO table_1 VALUEs ( 5, TIME'05:00:00.000000'); 
INSERT INTO table_1 VALUEs ( 6, TIME'06:00:00.000000'); 
INSERT INTO table_1 VALUEs ( 7, TIME'07:00:00.000000'); 
INSERT INTO table_1 VALUEs ( 8, TIME'08:00:00.000000'); 
INSERT INTO table_1 VALUEs ( 9, TIME'09:00:00.000000'); 
INSERT INTO table_1 VALUEs (10, TIME'10:00:00.000000'); 
INSERT INTO table_1 VALUEs (11, TIME'11:00:00.000000'); 
INSERT INTO table_1 VALUEs (12, TIME'12:00:00.000000'); 
INSERT INTO table_1 VALUEs (13, TIME'13:00:00.000000'); 
INSERT INTO table_1 VALUEs (14, TIME'14:00:00.000000'); 
INSERT INTO table_1 VALUEs (15, TIME'15:00:00.000000'); 
INSERT INTO table_1 VALUEs (16, TIME'16:00:00.000000'); 
INSERT INTO table_1 VALUEs (17, TIME'17:00:00.000000'); 
INSERT INTO table_1 VALUEs (18, TIME'18:00:00.000000'); 
INSERT INTO table_1 VALUEs (19, TIME'19:00:00.000000'); 
INSERT INTO table_1 VALUEs (20, TIME'20:00:00.000000'); 
INSERT INTO table_1 VALUEs (21, TIME'21:00:00.000000'); 
INSERT INTO table_1 VALUEs (22, TIME'22:00:00.000000');
INSERT INTO table_1 VALUEs (23, TIME'23:00:00.000000');

次のSELECT文が、TIMEデータ型列(column_2)の時間値を問合わせるために使用されます。

SELECT * FROM table_1
ORDER BY column_2;

column_1  column_2
--------  ---------------
16        16:00:00.000000
17        17:00:00.000000
18        18:00:00.000000
19        19:00:00.000000
20        20:00:00.000000
21        21:00:00.000000
22        22:00:00.000000
23        23:00:00.000000

0         00:00:00.000000
1         01:00:00.000000
2         02:00:00.000000
3         03:00:00.000000
4         04:00:00.000000
5         05:00:00.000000
6         06:00:00.000000
7         07:00:00.000000
8         08:00:00.000000
9         09:00:00.000000
10        10:00:00.000000
11        11:00:00.000000
12        12:00:00.000000
13        13:00:00.000000
14        14:00:00.000000
15        15:00:00.000000

ソート順序はUTC時間(列には表示されていない)に基づくため、時間値は結果のソート順序と一致しません。行16～23は、UTC時間に従ってリストの上位にソートされるため、リストの上位に表示されています。

返された時間値と等価のUTCは、以下のとおりです。

返された値                                      等価のUTC(ローカル時間+8時間)

column_1  column_2
--------  -------------
16      16:00:00.000000           00:00:00.000000
17      17:00:00.000000           01:00:00.000000
18      18:00:00.000000           02:00:00.000000
19      19:00:00.000000           03:00:00.000000
20      20:00:00.000000           04:00:00.000000
21      21:00:00.000000           05:00:00.000000
22      22:00:00.000000           06:00:00.000000
23      23:00:00.000000           07:00:00.000000

0       00:00:00.000000           08:00:00.000000
1       01:00:00.000000           09:00:00.000000
2       02:00:00.000000           10:00:00.000000
3       03:00:00.000000           11:00:00.000000
4       04:00:00.000000           12:00:00.000000
5       05:00:00.000000           13:00:00.000000
6       06:00:00.000000           14:00:00.000000
7       07:00:00.000000           15:00:00.000000
8       08:00:00.000000           16:00:00.000000
9       09:00:00.000000           17:00:00.000000
10      10:00:00.000000           18:00:00.000000
11      11:00:00.000000           19:00:00.000000
12      12:00:00.000000           20:00:00.000000
13      13:00:00.000000           21:00:00.000000
14      14:00:00.000000           22:00:00.000000
15      15:00:00.000000           23:00:00.000000

DATEを問合わせたときの予期しないソート順序の回避策

以下の回避策を使用して、想定した結果を得ることができます。
  • INTERVALの使用
  • CHARまたはVARCHARへのCASTの使用
  • TIMEではなくTIMESTAMPの使用

INTERVALの使用

SELECT column_1, column_2, column_2 - interval '8' hour
FROM table_1
ORDER BY 3;

column_1     column_2            (column_2- 8)
--------     --------------      --------------
       0     00:00:00.000000     16:00:00.000000
       1     01:00:00.000000     17:00:00.000000
       2     02:00:00.000000     18:00:00.000000
       3     03:00:00.000000     19:00:00.000000
       4     04:00:00.000000     20:00:00.000000
       5     05:00:00.000000     21:00:00.000000
       6     06:00:00.000000     22:00:00.000000
       7     07:00:00.000000     23:00:00.000000
       8     08:00:00.000000     00:00:00.000000
       9     09:00:00.000000     01:00:00.000000
      10     10:00:00.000000     02:00:00.000000
      11     11:00:00.000000     03:00:00.000000
      12     12:00:00.000000     04:00:00.000000
      13     13:00:00.000000     05:00:00.000000
      14     14:00:00.000000     06:00:00.000000
      15     15:00:00.000000     07:00:00.000000
      16     16:00:00.000000     08:00:00.000000
      17     17:00:00.000000     09:00:00.000000
      18     18:00:00.000000     10:00:00.000000
      19     19:00:00.000000     11:00:00.000000
      20     20:00:00.000000     12:00:00.000000
      21     21:00:00.000000     13:00:00.000000
      22     22:00:00.000000     14:00:00.000000
      23     23:00:00.000000     15:00:00.000000

CHARまたはVARCHARへのCASTの使用

SELECT column_1, column_2, CAST(column_2 AS CHAR(15))
FROM table_1
ORDER BY 3;

column_1      column_2     column_2
--------  ------------     ---------------
       0  00:00:00.000000  00:00:00.000000
       1  01:00:00.000000  01:00:00.000000
       2  02:00:00.000000  02:00:00.000000
       3  03:00:00.000000  03:00:00.000000
       4  04:00:00.000000  04:00:00.000000
       5  05:00:00.000000  05:00:00.000000
       6  06:00:00.000000  06:00:00.000000
       7  07:00:00.000000  07:00:00.000000
       8  08:00:00.000000  08:00:00.000000
       9  09:00:00.000000  09:00:00.000000
      10  10:00:00.000000  10:00:00.000000
      11  11:00:00.000000  11:00:00.000000
      12  12:00:00.000000  12:00:00.000000
      13  13:00:00.000000  13:00:00.000000
      14  14:00:00.000000  14:00:00.000000
      15  15:00:00.000000  15:00:00.000000
      16  16:00:00.000000  16:00:00.000000
      17  17:00:00.000000  17:00:00.000000
      18  18:00:00.000000  18:00:00.000000
      19  19:00:00.000000  19:00:00.000000
      20  20:00:00.000000  20:00:00.000000
      21  21:00:00.000000  21:00:00.000000
      22  22:00:00.000000  22:00:00.000000
      23  23:00:00.000000  23:00:00.000000

TIMEではなくTIMESTAMPの使用

CREATE TABLE table_2
(column_1 INTEGER,
column_2 TIMESTAMP);

INSERT INTO table_2 SELECT * FROM table_1;

SELECT * FROM table_2
ORDER BY column_2;

column_1   column_2
--------   --------------------------
       0   2011-11-07 00:00:00.000000
       1   2011-11-07 01:00:00.000000
       2   2011-11-07 02:00:00.000000
       3   2011-11-07 03:00:00.000000
       4   2011-11-07 04:00:00.000000
       5   2011-11-07 05:00:00.000000
       6   2011-11-07 06:00:00.000000
       7   2011-11-07 07:00:00.000000
       8   2011-11-07 08:00:00.000000
       9   2011-11-07 09:00:00.000000
      10   2011-11-07 10:00:00.000000
      11   2011-11-07 11:00:00.000000
      12   2011-11-07 12:00:00.000000
      13   2011-11-07 13:00:00.000000
      14   2011-11-07 14:00:00.000000
      15   2011-11-07 15:00:00.000000
      16   2011-11-07 16:00:00.000000
      17   2011-11-07 17:00:00.000000
      18   2011-11-07 18:00:00.000000
      19   2011-11-07 19:00:00.000000
      20   2011-11-07 20:00:00.000000
      21   2011-11-07 21:00:00.000000
      22   2011-11-07 22:00:00.000000
      23   2011-11-07 23:00:00.000000