非標準のSQL構文機能のいくつかの例。 パート2：日付形式

はじめに

この記事では、Oracleの日付形式とその処理のいくつかの機能について説明します。 この記事では、いくつかの標準的な日付書式マスク、文字列から日付への明示的および暗黙的な変換、およびこのプロセスに影響する追加のパラメーターの概要を説明します。 記事の最初の部分と同様に、この資料の説明は、非標準のフォーマット機能を示す例に基づいています。 暗黙的な変換プロセスに関係するOracleメカニズムについて詳しく調べます。 ほとんどの機能は、ドキュメントの関連セクションへのリンクで説明されています。



最初は、日付に関する記事を書くつもりはありませんでしたが、このトピックに関する1つの問題だけを検討することに専念するつもりでした。 ただし、作業中にOracleのさまざまな追加機能をカバーする必要があり、新しい例が登場し始めました。 そのため、1つの問題の検討が小さな記事になりました。 最も興味深いトピックではないにもかかわらず、退屈なものになったことを願っています。



演算子による順序の機能、not inの操作、および暗黙的な型変換の例に関する記事の最初の部分はこちらです。

To_date関数と日付形式

トピックの書式設定が好きなプログラマーはほとんどいません。 たとえば、一部のコースでは、日付の書式設定と地域標準のトピックは、特にトレーニングの最終日の最後の時間に配置されます。 リスナーは退屈です。 その理由は、標準タスクで比較的まれに使用される多数の既存のフォーマットです。 ほとんどの場合、マスクはto_number 、 to_char 、およびto_dateの 3つの関数で使用されます。 3つの場合すべてで、マスクは2番目のオプションのパラメーターです。 そして、 数値をフォーマットするための正気のマスクが多かれ少なかれある場合 、 日付をわいせつにフォーマットするためのマスクに加えて、 サフィックスと修飾子があります。



もちろん、多数のマスクを使用できることは肯定的なことです。たとえば、プログラマーの日が2011年9月13日であるかどうかを確認するために、年の日数を返すマスク「DDD」を使用できます。

-- №1 select to_char(to_date('13.09.2011'),'DDD') "Programmers day" from dual;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

書式設定の明らかな利点にもかかわらず、この記事の2番目のパートでは、日付書式の概要とエキゾチックなマスクの使用例を含めるつもりはありませんでした。 第一に、誰もが興味を持つことはまずありません。第二に、著者は複雑なフォーマットの大ファンではありません。 このセクションが表示される唯一の理由は、RR形式の使用に関する読者からの質問です。



セクションのメイントピックに直接移動する前に、日付の操作の非標準的な例をいくつか見てみましょう。



例1 切り捨てられたパターンを使用する

標準のフォーマットから始めましょう。 今日の日付は2011年9月16日ですが、次のリクエストは満たされますか？

 -- №2 select to_char(sysdate, 'YYYY') from dual; -- №3 select to_date('03', 'DD') from dual;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

リクエスト番号2は、日付を文字列に変換し、希望する形式に変換する典型的な例です。 唯一の違いは、「DD.MM.YY」または「DD-MON-YYYY」タイプのより一般的なマスクの代わりに、年のみを設定するマスクを使用したことです。 リクエスト番号2は正常に実行され、現在の年が4桁の形式で返されます。 「2011」。



クエリ3はもう少しおもしろいです。これは、文字列を切り捨てられた書式マスクを使用した日付への明示的な変換の典型的な例です。したがって、構文の観点から、クエリは正しく、正常に実行されます。 より重要な問題は、その実装の結果です。 1日しか与えられない場合、彼は何日目を返しますか？ この質問に答える前に、明示的に設定されていない場合にOracleがどのように時間を設定するか思い出してみましょう。

 -- №4 select to_char(to_date('03.02.2011','DD.MM.YYYY'),'DD.MM.YYYY HH24:MI:SS') from dual; -- №5 select to_char(to_date('03.02.2011 30', 'DD.MM.YYYY MI'),'DD.MM.YYYY HH24:MI:SS') from dual;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

リクエストNo. 4では時間は示されず、リクエストNo. 5では分数のみが示され、時間と秒は省略されます。 Oracleにはルールがあり、日付に時間コンポーネントがない場合、時間要素の一部のみが指定されている場合（クエリNo.5のように）、時間は自動的に00:00:00（つまり真夜中）に設定され、要素は00に設定されます。したがって、リクエスト番号4は文字列「03 .02.2011 00:00:00」を返し、リクエスト番号5は「03.03.02.2011 00:30:00」を返します。



クエリ3に戻りましょう。このルールは日付に当てはまりますか。 変換中に欠落した日付要素は00または01に置き換えられますか？ はい、それらは置き換えられますが、sysdate（現在の年の現在の月の最初の日）からの値がすべて欠落している日付要素に使用されるわけではありません。 したがって、リクエストNo. 3は月を09、2011を年として使用するため、リクエストの結果は2011年3月9日となります。



例2 フォーマット順

次のクエリが実行されますか？実行された場合、何日目が返されますか？

 -- №6 select to_date('20092011', 'YYYYDDMM') from dual;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

一見、日付文字列に区切り文字がないことは、クエリの実行と互換性のない重要な要素のように見えますが、日付マスクも区切り文字なしで指定され、変換する文字列は指定されたパターンに対応します。 したがって、リクエストNo. 6は正常に実行され、2009年11月20日に返送されます（結果の形式は、セッション設定によってわずかに異なる場合があります）。 次の例で、セパレータに関連する問題をより詳細に検討します。



例3 暗黙的な変換

デフォルトの日付形式をDD.MON.RR、日付言語をロシア語とすると、次のクエリが機能します。

 -- №7 select months_between('1\-9','01$/08') from dual;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このクエリは、暗黙的な変換を使用して日付に変換する必要がある2つの文字列パラメーターを指定します。 ドキュメントによると、既定の形式を使用する場合、日付への暗黙的な変換の文字列は、パターン[D | DD] separator1 [MM | MON | MONTH] separator2 [R | RR | YY | YYYY]を満たす必要があります。 separator1およびseparator2として、スペース、タブ、および「二重引用符」を含むほとんどの区切り文字と特殊文字を使用できます。さらに、行に少なくとも2桁の日、月、年を指定する場合、次に、セパレータを完全に省略することができます。例：

 -- №8 select to_date('0109') from dual; -- №9 select to_date('0109') from dual; -- №10          DD.MM.RR select to_date('010909') from dual;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

クエリNo. 7で指定された両方の行が指定されたパターンに対応するため、クエリは正常に実行され、番号11が返されます。



例番号4。 To_date関数のパラメーター

デフォルトの日付形式をDD.MON.RR、日付言語をロシア語とすると、次のクエリが機能します。

 -- №11 select to_date(sysdate,'mm/dd/yyyy hh24:mi:ss') from dual;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

同様のクエリが、Ask Tomページの議論の1つに登場しました。 要求トラップは、日付（sysdate）を日付に変換しようとしていることです。 リクエストが次のようになる場合：

 -- №12 select to_char(sysdate,'mm/dd/yyyy hh24:mi:ss') from dual;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

その実行は成功し、文字列'09 / 15/2011 23:00:11 'を返しました。 ただし、to_date関数は最初のパラメーターとして文字列を想定しているため、最初は日付が文字列に暗黙的に変換されます（デフォルトのマスクでto_char（sysdate）を呼び出すのと同じです）。 この変換の結果はストリング'15 .09.11 'であり、to_date呼び出しが行われます。 したがって、クエリ＃11は次のクエリと同等です。

 -- №13 select to_date('15.09.11','mm/dd/yyyy hh24:mi:ss') from dual;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

行'15 .09.11 'はインストールされているマスクに対応していないため、リクエストNo. 13を実行できないことを確認することは難しくありません。したがって、リクエストNo. 11も実行できません。



デフォルトの日付形式を設定する

デフォルトの日付形式は、NLS_DATE_FORMAT（形式自体に責任）とNLS_DATE_LANGUAGE（日、月などの名前を記述するときに使用される言語に責任）の2つのパラメーターによって設定されます。 これらのパラメーターが明示的に指定されていない場合、それらの値はNLS_LANGパラメーターに基づいて設定されます 。



日付形式を設定できるレベルは3つあります。

1. DBレベル：

    select * from nls_database_parameters where parameter in ('NLS_DATE_FORMAT','NLS_DATE_LANGUAGE');
         
         
   
           
           
           
         
   
       
           
           
           
         
         
   
           
           
           
         
   
       
        

   このレベルのパラメーターは、データベースの作成時に設定され、init.oraファイルに書き込まれます。
2. インスタンスレベル：

    select * from nls_instance_parameters where parameter in ('NLS_DATE_FORMAT','NLS_DATE_LANGUAGE');
         
         
   
           
           
           
         
   
       
           
           
           
         
         
   
           
           
           
         
   
       
        

   このレベルのパラメーターは、インスタンスの開始時に設定され 、ALTER SYSTEMコマンドを使用して変更できます。
3. セッションレベル：

    select * from nls_session_parameters where parameter in ('NLS_DATE_FORMAT','NLS_DATE_LANGUAGE');
         
         
   
           
           
           
         
   
       
           
           
           
         
         
   
           
           
           
         
   
       
        

   このレベルのパラメーターは、ALTER SESSIONコマンドで変更できます 。 また、これらのパラメーターの値は、クエリを使用して確認できます。

     select SYS_CONTEXT ('USERENV', 'NLS_DATE_FORMAT'), SYS_CONTEXT ('USERENV', 'NLS_DATE_LANGUAGE') from dual;
         
         
   
           
           
           
         
   
       
           
           
           
         
         
   
           
           
           
         
   
       
        

次の各レベルのパラメーターは、前のレベルのパラメーターを「上書き」します。 セッションレベルのパラメーターを設定している場合、他のレベルのパラメーターはセッションに影響しません。 すべてのセッションに単一の日付形式を設定するには、Tomは自分の列で ON-LOGONトリガーを使用することをお勧めします 。

 create or replace trigger data_logon_trigger after logon ON DATABASE begin execute immediate 'alter session set nls_date_format = ''dd/mm/yyyy'''; end;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

例5 DD.MM.RRとDD-MON-RRの形式

デフォルトで日付のフォーマットに注意を払うために、文字列を日付に暗黙的に変換する際のある奇妙なことに触発されました。 次の例を見てみましょう。

 -- №14 --     alter session set NLS_DATE_FORMAT='DD.MM.RR'; --     alter session set NLS_DATE_LANGUAGE='AMERICAN'; --    select * from nls_session_parameters where parameter in ('NLS_DATE_FORMAT','NLS_DATE_LANGUAGE'); --    select to_date('11.09.11') from dual; select to_date('11.SEP.11') from dual;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

文字列'11 .09.11 'の日付への変換は成功するが、文字列'11 .SEP.11'は成功しないと仮定するのは論理的です。 ただし、そうではないため、両方の変換が正常に完了します。 最初に、文字列をセッションマスクに変換できない場合、Oracleは他のレベルのマスクを使用しようとすると仮定しました（DBレベルマスクは「DD-MON-RR」に設定されます）。 ドキュメントを読むと、そうではないことがわかりました。Oracleは、前の段落で説明した原則に従っています。



別の例を試してみましょう。

 -- №15 --     alter session set NLS_DATE_FORMAT='DD.MON.RR'; --     alter session set NLS_DATE_LANGUAGE='AMERICAN'; --    select * from nls_session_parameters where parameter in ('NLS_DATE_FORMAT','NLS_DATE_LANGUAGE'); --    select to_date('11.09.11') from dual; select to_date('11.SEP.11') from dual;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

結果が前のクエリと同じだと思う場合、あなたは間違っています。 変換の1つが失敗します。 この場合、文字列'11 .09.11 'はパターンと一致しません。 おそらくこれは神秘主義でしょうか？



ああ、いや。 ドキュメントを読むと、日付書式要素の自動置換のルールがあることがわかりました 。 以下は交換表です。

元のフォーマット要素	オリジナルの代わりに試す追加のフォーマット要素
「MM」	「月」と「月」
「月	「月」
「月」	「月」
「Yy」	「YYYY」
「RR」	「RRRR」

この表の内容を見ると、形式「DD.MM.RR」には形式「DD.MON.RR」（および「DD.MONTH.RR」など）が暗黙的に含まれているが、形式は「DD」であることが明らかになります。 .MON.RR 'format' DD.MM.RR 'は存在せず、リクエストNo. 14およびNo. 15の動作を説明しています。



例6 フォーマットRR vs YY

ほとんどのユーザーにとって、RRマスクとYYマスクの違いはよく知られていますが、この情報が役立つと感じる人もいます。 例の検討にすぐに進みます。 次のクエリを返すデータ：

 -- №16 select to_date('11','RR') "RR", to_date('11','YY') "YY" from dual; -- №17 select to_date('99','RR') "RR", to_date('99','YY') "YY" from dual;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

上記の両方のリクエストは成功し、リクエストNo. 3の例No. 1に記載されているルールに従って日付を返します。 したがって、受信したすべての日付の日の値は01になり、月の値は09になります（9月にリクエストを実行する場合）。 主な質問は、今年の重要性は何ですか？



クエリNo. 16 by '11'では2011年を意味し、両方のマスクがそれを私に返したと推測するのは簡単です。 クエリ＃16の結果は、2011年9月1日と2011年9月1日です。



リクエストNo. 17では、'99 'で1999を意味しましたが、ここではマスクの意見が分かれていました。RRマスクは予想される1999を返し、YYマスクは2099を返しました。 クエリ＃17の結果は1999年9月1日と2099年9月1日です。



これらのフォーマット要素がどのように機能するかを詳しく見てみましょう。

 -- №18 select to_date('00','RR') "00", to_date('49','RR') "49", to_date('50','RR') "50", to_date('99','RR') "99" from dual union all select to_date('00','YY') "00", to_date('49','YY') "49", to_date('50','YY') "50", to_date('99','YY') "99" from dual;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

クエリ番号18からわかるように、マスクの動作の違いは50年代から始まります。 YY形式は常に現在の世紀の年を返し、RRは、現在の世紀の年として50年未満、および前世紀の年として50年以上の年を認識します。 実際、値to_date（'99 '、' RR '）= 1999およびto_date（' 00 '、' RR '）= 2000は、現在の日付が2051未満で、その後にto_date（'99'、 'RR' ）= 2099およびto_date（'00 '、' RR '）=2100。実験する場合、サーバー時間を2051に変更し、クエリ＃18の結果がどのように変化するかを確認できます。実行中のシステムでは実行しないでください。 サーバー時間が変更されると、sysdateが変更されるため、すべてのレポート、ログテーブルなどで発生する頭痛の種についても考えない方がよいでしょう。 RR形式を使用して2桁の年を4桁の年に変換するための一般的な規則は次のようになります 。

指定された2桁の年が00〜49の場合、

• 現在の年の下2桁が00から49の場合、返される年の最初の2桁は現在の年と同じになります。
• 現在の年の下2桁が50〜99の場合、返される年の上2桁は、現在の年の上2桁よりも1大きくなります。

指定された2桁の年が50〜99の場合、

• 現在の年の下2桁が00から49の場合、返される年の最初の2桁は、現在の年の上2桁よりも1少なくなります。
• 現在の年の下2桁が50から99の場合、返される年の最初の2桁は現在の年と同じになります。

年を2桁で設定する場合についてのみ説明していることに注意してください。年を4桁で設定すると、YYマスクは自動的にYYYYマスクに置き換えられ、RRマスクはRRRRになります。

 -- №19 select to_date('1950','RR') "50RR", to_date('1950','YY') "50YY" from dual;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

したがって、リクエスト19は、どちらの場合も1950を返します。



例7 他のいくつかの例

レビューを終了するには、少し異国情緒を考慮してください。 次のクエリの結果としてエラーが発生しますか？

 -- №20 select DATE '1928-12-25' from dual;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これが無意味なレコードであると判断した場合は、間違えられます。これはANSI標準に準拠した完全に正しい日付設定であり、クエリNo. 20は成功し、1928年12月25日に返されます。



どのリクエストが失敗しますか？

 -- №21 select to_date('1998--25 17:30','YYYY-MON-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN') from dual; -- №21 select to_date('1998--25 17:30','YYYY-MON-DD HH24:MI','NLS_DATE_LANGUAGE=RUSSIAN') from dual;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この例は、to_date関数の3番目のパラメーターの存在を示すことを目的としています。 このパラメーターを使用すると、このto_date関数呼び出しに対してのみ、NLS（National Language Support）パラメーターのいずれかの値を設定できます。 例5で検討したセッションのNLSパラメーターを設定します。 リクエストNo. 20とNo. 21の主な違いは、月の名前ではなく（例5で説明されているように、MONマスクは自動的にMONTHマスクに置き換えられます）、異なる日付言語を示しています。 リクエスト番号21は月の名前を英語で期待しているため、実行されません;リクエスト番号22は月の名前をロシア語で期待し、正常に実行されます。



次のテーブル宣言は、どのような場合にデータを挿入するときにエラーにつながる可能性がありますか？

 -- №23 create table for_test (a number, b date default to_date('11.09.2011'));
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

デフォルトの日付形式「DD.MON.RR」のセッションでは、最初の列の値のみが挿入されるたびに

 insert into for_test(a) values(1);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

エラーが発生します。

さまざまなアプリケーションで日付を表示する機能

日付表示に影響するもの

このセクションは、コメントに示された推奨事項により、記事の発行後に追加されました。 以下は、日付の表示と数値の表示の両方に当てはまります。 おそらく、上記の記事に記載されているいくつかの例を実行すると、結果に示されている形式以外の形式で日付を受け取りました。 セッションの設定が例に示されているとおりであれば、これは少なくとも奇妙に思えます。



真実は、リクエストを実行するとき

 -- №24 select sysdate from dual;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

日付を取得しますが、画面に結果を表示するには、データベースにアクセスする特定のユーティリティが日付を文字列に変換する必要があります。 したがって、日付（および数値）を表示するには、to_charが暗黙的に呼び出されます。 暗黙的な変換の古典的なケースがあります（この変換は画面に表示するためだけであり、その結果は計算に関与せず、データの表示以外には影響しません）。 暗黙的な変換がある場合、それを実行するマスクもあります。 古典的なケースでは、セッションに設定されたマスク、つまり nls_session_parametersテーブルのNLS_DATE_FORMATパラメーターで指定されたマスク。これは記事全体で積極的に作業しました。



いくつかのアプリケーションの動作を確認しましょう。 次のスクリプトを使用して確認します。

 -- №25 --     select * from nls_session_parameters where parameter in ('NLS_DATE_FORMAT','NLS_DATE_LANGUAGE'); --  select sysdate from dual; --     alter session set NLS_DATE_FORMAT='DD.MON.RR hh24:mi:ss'; --  select sysdate from dual;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

sqlplusが日付の表示に使用するパラメーターを確認しましょう。



図 1. sqlplusのクエリ＃25の結果。



図1からわかるように、日付表示形式はセッション設定、つまり sqlplusはセッション設定を使用します。 これにより、変換と表示の両方に同じマスクが使用されるため、日付を文字列に、またはその逆に変換するプロセスを理解しやすくなります。



一部の高度な開発ツールは、Oracle設定に関連しない独自のNLS設定を使用します。 例として、日付を表示するためにPL / SQL Developerが使用するオプションを確認しましょう。 これを行うには、リクエスト番号25を実行します。



図 2. PL / SQL Developerのクエリ＃25の結果。



図2からわかるように、セッション設定を変更しても日付表示形式は変わりません。 さらに、注意深く見ると、画面に日付を表​​示した最初と2番目の結果がセッションパラメーターと一致しなかったことがわかります（最初の場合、表示された日付は4桁形式の年で、マスクは2桁形式の年を示していました）。 これは、ユーティリティが独自のNLS設定を使用することを意味します。PL/ SQL Developerの場合、その場所は図に示されています。 3。



図 3. PL / SQL DeveloperでNLS日付表示オプションを設定します。



NLSユーティリティの設定が悪いことがある方法

セッション形式とは異なる形式で日付を表示すると、1つの理由で有害です-ユーザーを誤解させ、エラーを引き起こす可能性があります。 sqlplusおよびPL / SQL Developerで次のクエリを実行します。

 -- №26 --     alter session set NLS_DATE_FORMAT='DD.MON.RR hh24:mi:ss'; --  select sysdate from dual; --         select to_date() from dual;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

の代わりに、前の行から受信したデータをクエリの最後の行に挿入します。



クエリの結果は、以下の図に示されています。



図 4. sqlplusのクエリ＃26の結果。





図 5. PL / SQL Developerのクエリ＃26の結果。



sqlplusで表示されたデータが正常に日付に変換され、PL / SQL Developerによって表示されたデータを変換できなかったのはなぜですか？ Oracleはセッションで指定されたデータ形式を変換に使用し、PL / SQL Developerによるデータ出力はセッション形式とは異なる独自の形式で表示されるためです。

おわりに

結論として、日付の操作に専念するほぼすべての投稿で、Tom Kiteは明示的な変換を使用する必要性とマスクの必須使用について話していることを思い出してください。 「文字列を日付に変換するときは、デフォルトの日付形式に依存せず、常に明示的にマスクを設定してください」-これが彼の言葉の発音です。 ask Tomページを使用して、日付変換を操作するときに、追加の例と考えられるエラーを見つけることができます。



日付を扱うことは記事全体を取り上げたので、多くの興味深い質問が「オーバーボード」のままであり、それを検討したいと思います。 ほとんどの場合、記事の3番目の部分は、空き時間があるとすぐに表示されます。