2022.08.24
SQLチューニング（第34回）「日データ関連関数紹介」（3／3回）
今回は、「日データ関連関数紹介」の３回目・最終回です。では、早速始めましょう。
８.３ 日付と関連する便利な関数
８.３.１ TO_DATE & TO_TIMESTAMP & TO_CHAR(datetime)
・TO_DATE
Syntax : TO_DATE (CHAR [, FORMAT] [, nlsparam])
TO_DATEはCHAR,VARCHAR2,NCHAR,NVARCHAR2タイプをDATEタイプに変換する関数です。 もし、Formatを省略して実行する場合、CHARはDefault Date Formatと同一でなければなりません。 Default Date FormatはNLS_DATE_FORMATパラメーターを設定しなければNLS_TERRITORYで、NLS_DATE_FORMATパラメーターを設定すればNLS_DATE_FORMATとなります。
SQL> SELECT TO_DATE('20110101') AS "Date" FROM DUAL ;
ERROR at line 1:
ORA-01861: literal does not match format string
SQL> SHOW PARAMETER NLS
NAME TYPE VALUE
------------------------------------ ----------- ----------
nls_calendar string
nls_comp string
nls_currency string
nls_date_format string
nls_date_language string
nls_DUAL_currency string
nls_iso_currency string
nls_language string AMERICAN
nls_length_semantics string BYTE
nls_nchar_conv_excp string FALSE
nls_numeric_characters string
nls_sort string
nls_territory string AMERICA
nls_time_format string
nls_time_tz_format string
nls_timestamp_format string
nls_timestamp_tz_format string
TO_DATEを使う時Formatを省略すればCHARがDefault Date Formatと同一でなければなりません。 SHOW PARAMETER NLS命令で確認してみれば、NLS_DATE_FORMATが設定されていないのでNLS_TERRITORY(AMERICA)と同一でなければならないのです。 したがって下記のように使うことで正常実行されます。
SQL> SELECT TO_DATE('01/JAN/2011') AS "Date" FROM DUAL ;
Date
------------
01-JAN-11
SQL実行時の前と同じFormatで問い合わしたくない場合、下記のようにセッション レベルでNLS_DATE_FORMATを設定してSQLを実行すれば良いのです。
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'yyyymmdd' ;
Session altered.
SQL> SELECT TO_DATE('20110101') AS "Date" FROM DUAL ;
Date
--------
20110101
[使用例 : 基本方式TO_DATE問い合わせ]
SELECT TO_DATE('20110101') AS "Date" FROM DUAL
UNION ALL
SELECT TO_DATE('01/JAN/2011','dd/mon/yyyy') FROM DUAL
UNION ALL
SELECT TO_DATE('2011-01-01','yyyy-mm-dd') FROM DUAL
UNION ALL
SELECT TO_DATE('20110101','yyyymmdd') FROM DUAL
UNION ALL
SELECT TO_DATE('01012011','ddmmyyyy') FROM DUAL
UNION ALL
SELECT TO_DATE('01012011','mmddyyyy') FROM DUAL
UNION ALL
SELECT TO_DATE('01012011','mmddrrrr') FROM DUAL
UNION ALL
SELECT TO_DATE('20110101','yyyymmdd hh24:mi:ss') FROM DUAL ;
Date
--------
20110101
20110101
20110101
20110101
20110101
20110101
20110101
20110101
次に、RRからYyyyフォームで示される文字（「01-Jan-59」）を変更すると、年間形式が間違ったデータを取得する場合を見てみましょう。
通常、2059年1月1日の入力値でデータを検索しようとすると、to_date関数を使用します。 ただし、TO_DATEの引数として「DD-Mon-RR」を使用する場合、キャラクター「01-Jan-59」値の59が2059ではなく1959年に抽出されます。 2059年に書くことを意図している場合、これは間違ったデータを抽出します。 以下のさまざまな例を見てみましょう。
[使用例 : RR(RRRR) Datetime Format問い合わせ]
01 ~ 49 = 2001 ~ 2049
50 ~ 99 = 1950 ~ 1999
SELECT TO_CHAR(TO_DATE('01-JAN-01', 'DD-MON-RR') ,'YYYY') AS "YEAR" FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('01-JAN-49', 'DD-MON-RR') ,'YYYY') AS "YEAR" FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('01-JAN-50', 'DD-MON-RR') ,'YYYY') AS "YEAR" FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('01-JAN-99', 'DD-MON-RR') ,'YYYY') AS "YEAR" FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('01-JAN-01', 'DD-MON-RRRR') ,'YYYY') AS "YEAR" FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('01-JAN-49', 'DD-MON-RRRR') ,'YYYY') AS "YEAR" FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('01-JAN-50', 'DD-MON-RRRR') ,'YYYY') AS "YEAR" FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('01-JAN-99', 'DD-MON-RRRR') ,'YYYY') AS "YEAR" FROM DUAL ;
YEAR
-----
2001
2049
1950
1999
2001
2049
1950
1999
1945年を2045年で間違って抽出することを防ぐためには下記のように指定しなければなりません。
ELECT TO_CHAR(TO_DATE('01-JAN-1945', 'DD-MON-RRRR') ,'YYYY') AS "YEAR" FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('01-JAN-2099', 'DD-MON-RRRR') ,'YYYY') AS "YEAR" FROM DUAL ;
YEAR
-----
1945
2099
・TO_TIMESTAMP
Syntax : TO_TIMESTAMP (CHAR [, FORMAT] [, nlsparam])
TO_TIMESTAMPはCHAR,VARCHAR2,NCHAR,NVARCHAR2タイプをTIMESTAMPタイプに変換する関数です。 もし、Formatを省略すればCHARはNLS_TIMESTAMP_FORMATと同一でなければなりません。
[使用例]
SELECT TO_TIMESTAMP('20110101','yyyymmdd') AS "TO_TIMESTAMP" FROM DUAL
UNION ALL
SELECT TO_TIMESTAMP('2011-01-01 00:00:00:000000000','yyyy-mm-dd hh24:mi:ss:ff') FROM DUAL
UNION ALL
SELECT TO_TIMESTAMP('2011-01-01 23:59:59:999999999','yyyy-mm-dd hh24:mi:ss:ff') FROM DUAL ;
TO_TIMESTAMP
----------------------------
11/01/01 00:00:00.000000000 --->一日の開始
11/01/01 00:00:00.000000000 --->一日の開始
11/01/01 23:59:59.999999999 --->一日の終わり
・TO_CHAR(datetime)
Syntax : TO_CHAR ( DATE TIME [,FORMAT] [,nlsparam] )
TO_CHAR(DATETIME)はDATE,TIMESTAMP,TIMESTAMP WITH TIMEZONE,TIMESTAMP WITH LOCAL TIME ZONEタイプをVARCHAR2タイプに変換する関数です。
もし、Formatを省略すれば次の通りカラムのデータ タイプにより変換されます。
・DATEデータはDefault Date Formatに変換
・TIMESTAMP,TIMESTAMP WITH LOCAL TIME ZONEはDefault Timestamp Formatに変換
・TIMESTAMP WITH TIME ZONEはDefault Timestamp with time zone Formatに変換
[使用例]
SELECT TO_CHAR(TO_DATE('20110101','yyyymmdd')) AS "Date" FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('20110101','yyyymmdd'),'yyyy-mm-dd') FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('20110101','yyyymmdd'),'yyyy/mm/dd') FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('20110101','yyyymmdd'),'yyyymmdd hh24:mi:ss') FROM DUAL ;
Date
-----------------
01-JAN-11
2011-01-01
2011/01/01
20110101 00:00:00
・ADD_MONTHS
Syntax:ADD_MONTHS ( DATE (開始日時),INTEGER (加える月))
ADD_MONTHS関数はDATE (開始日時)にINTEGER (加える月)を計算してDATEをリターンします。 INTEGERが正数なら開始日時以後を、負数なら以前の日を抽出する。 INTEGERは0以外を使用することで、正確なデータを得ることができます。 ただし、月を計算する時小数点は無視されます。
[使用例]
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20110101','yyyymmdd'),1),'yyyymmddhh24miss')
AS "ADD_MONTHS" FROM DUAL
UNION ALL
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20110101','yyyymmdd'),12),'yyyymmddhh24miss')
FROM DUAL
UNION ALL
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20110101','yyyymmdd'),1/24),'yyyymmddhh24miss')
FROM DUAL
UNION ALL
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20110101','yyyymmdd'),-5),'yyyymmddhh24miss')
FROM DUAL
UNION ALL
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20110101','yyyymmdd'),-5/24),'yyyymmddhh24miss')
FROM DUAL
UNION ALL
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20110101','yyyymmdd'),1.9),'yyyymmddhh24miss')
FROM DUAL ;
ADD_MONTHS
---------------
20110201000000 ---> ADD_MONTHS(TO_DATE('20110101','yyyymmdd'),1)
20120101000000 ---> ADD_MONTHS(TO_DATE('20110101','yyyymmdd'),12)
20110101000000 ---> ADD_MONTHS(TO_DATE('20110101','yyyymmdd'),1/24)
20100801000000 ---> ADD_MONTHS(TO_DATE('20110101','yyyymmdd'),-5)
20110101000000 ---> ADD_MONTHS(TO_DATE('20110101','yyyymmdd'),-5/24)
20110201000000 ---> ADD_MONTHS(TO_DATE('20110101','yyyymmdd'),1.9)
・MONTHS_BETWEEN
Syntax : MONTHS_BETWEEN ( date1 , date2 )
Return : NUMBER Data Type
MONTHS_BETWEEN関数は二つの日の間を月で計算してNUMBERタイプでリターンします。 二つの日データのパターンにより下記のようにデータをリターンするので一般的なBETWEENと同じだと考えてはいけません。
date1 = date2 => 0
date1 < date2 => < 0
date1 > date2 => > 0
[使用例]
SELECT MONTHS_BETWEEN(TO_DATE('20111001','yyyymmdd'),TO_DATE('20111001','yyyymmdd'))
AS "MONTHS_BETWEEN" FROM DUAL
UNION ALL
SELECT MONTHS_BETWEEN(TO_DATE('20111001','yyyymmdd'),TO_DATE('20111201','yyyymmdd'))
FROM DUAL
UNION ALL
SELECT MONTHS_BETWEEN(TO_DATE('20111201','yyyymmdd'),TO_DATE('20111001','yyyymmdd'))
FROM DUAL ;
MONTHS_BETWEEN
--------------
0
-2
2
・LAST_DAY
Syntax : LAST_DAY (DATE)
LAST_DAY 関数は、係数値に対応する月の最終日を常に返します。 返されたデータ型は常にDATEです。
[使用例]
SELECT TO_CHAR( SYSDATE, 'yyyymmdd' ) AS "LAST_DAY" FROM DUAL
UNION ALL SELECT TO_CHAR( LAST_DAY(SYSDATE), 'yyyymmdd' ) FROM DUAL;
LAST_DAY
--------
20111026
20111031
・NEXT_DAY
Syntax : NEXT_DAY (DATE [, format])
Return : Always DATE Type
Format:曜日
NEXT_DAY関数は、引数として日付データを受信し、フォーマット形式に適合する日付データを返します。 返されたデータ型は常にDATEです。
[使用例]
SELECT TO_CHAR( NEXT_DAY(SYSDATE, 'Sunday' ), 'yyyymmdd hh24:mi:ss' )
AS "NEXT_DAY" FROM DUAL
UNION ALL
SELECT TO_CHAR( NEXT_DAY(SYSDATE, 'Monday' ), 'yyyymmdd hh24:mi:ss' ) FROM DUAL
UNION ALL
SELECT TO_CHAR( NEXT_DAY(SYSDATE, 'Tuesday' ), 'yyyymmdd hh24:mi:ss' ) FROM DUAL
UNION ALL
SELECT TO_CHAR( NEXT_DAY(SYSDATE, 'Wednesday' ), 'yyyymmdd hh24:mi:ss' ) FROM DUAL
UNION ALL
SELECT TO_CHAR( NEXT_DAY(SYSDATE, 'Thursday' ), 'yyyymmdd hh24:mi:ss' ) FROM DUAL
UNION ALL
SELECT TO_CHAR( NEXT_DAY(SYSDATE, 'Friday' ), 'yyyymmdd hh24:mi:ss' ) FROM DUAL
UNION ALL
SELECT TO_CHAR( NEXT_DAY(SYSDATE, 'Saturday' ), 'yyyymmdd hh24:mi:ss' ) FROM DUAL;
NEXT_DAY
-----------------
20111030 20:55:22
20111031 20:55:22
20111101 20:55:22
20111102 20:55:22
20111027 20:55:22
20111028 20:55:22
20111029 20:55:22
ORA-01846エラーが発生した場合は、NLS_DATE_LANGUAGEをチェックしてから、以下に示すようにセッション設定を設定し、SQLを実行します。]
SQL> ALTER SESSION SET LNS_DATE_LANGUAGE = 'KOREAN' ;
SQL> SELECT TO_CHAR( NEXT_DAY(SYSDATE, 'Saturday' ), 'yyyymmdd hh24:mi:ss' )
AS "NEXT_DAY" FROM DUAL;
1行にエラー:
ORA-01846:指定した曜日が適合しません。
SQL> SELECT TO_CHAR( NEXT_DAY(SYSDATE,'土曜日' ),'yyyymmdd hh24:mi:ss' )
AS "NEXT_DAY" FROM DUAL;
NEXT_DAY
-----------------
20111119 23:04:27
SQL> ALTER SESSION SET NLS DATE_LANGUAGE = 'AMERICAN';
SQL> SELECT TO_CHAR( NEXT_DAY(SYSDATE, 'Saturday' ), 'yyyymmdd hh24:mi:ss' )
AS "NEXT_DAY" FROM DUAL;
NEXT_DAY
-----------------
20111119 23:05:00
・ROUND
Syntax : ROUND( DATE[,format] )
日データをFormat形式に合うように四捨五入する関数です。
・ ROUND( DATE ) =日付(12時の時点)
・ ROUND( DATE,'YEAR' ) =年(6月現在)
・ ROUND( DATE,'Q' ) =四半期(２ヶ月目の15日基準)
・ ROUND( DATE,'MONTH' ) =月(15日基準)
・ ROUND( DATE,'DD' ) =日付(12時の時点)
・ ROUND( DATE,'HH24' ) =時間(30分基準)
・ ROUND( DATE,'MI' ) =分(30秒基準)
・ ROUND( DATE,'DAY' ) = その週が開始された日付
[使用例]
SELECT TO_CHAR( SYSDATE, 'yyyymmdd hh24:mi:ss' ) AS "ROUND( DATE[,format] )"
FROM DUAL
UNION ALL
SELECT TO_CHAR( ROUND(SYSDATE), 'yyyymmdd hh24:mi:ss' ) FROM DUAL
UNION ALL
SELECT TO_CHAR( ROUND(SYSDATE, 'year' ), 'yyyymmdd hh24:mi:ss' ) FROM DUAL
UNION ALL
SELECT TO_CHAR( ROUND(SYSDATE, 'q' ), 'yyyymmdd hh24:mi:ss' ) FROM DUAL
UNION ALL
SELECT TO_CHAR( ROUND(SYSDATE, 'month' ), 'yyyymmdd hh24:mi:ss' ) FROM DUAL
UNION ALL
SELECT TO_CHAR( ROUND(SYSDATE, 'dd' ), 'yyyymmdd hh24:mi:ss' ) FROM DUAL
UNION ALL
SELECT TO_CHAR( ROUND(SYSDATE, 'hh24' ), 'yyyymmdd hh24:mi:ss' ) FROM DUAL
UNION ALL
SELECT TO_CHAR( ROUND(SYSDATE, 'mi' ), 'yyyymmdd hh24:mi:ss' ) FROM DUAL
UNION ALL
SELECT TO_CHAR( ROUND(SYSDATE, 'day' ), 'yyyymmdd hh24:mi:ss' ) FROM DUAL;
ROUND( DATE[,format] )
----------------------
20111025 11:52:52
20111025 00:00:00 ---> ROUND(SYSDATE)
20120101 00:00:00 ---> ROUND(SYSDATE, 'year')
20111001 00:00:00 ---> ROUND(SYSDATE, 'q')
20111101 00:00:00 ---> ROUND(SYSDATE, 'month')
20111025 00:00:00 ---> ROUND(SYSDATE, 'dd')
20111025 12:00:00 ---> ROUND(SYSDATE, 'hh24')
20111025 11:53:00 ---> ROUND(SYSDATE, 'mi')
20111023 00:00:00 ---> ROUND(SYSDATE, 'day')
・TRUNC
Syntax : TRUNC( DATE[,format] )
これは、日付データをformat形式に削減する関数です。
・ TRUNC( DATE ) =日付
・ TRUNC( DATE,'YEAR' ) =年
・ TRUNC( DATE,'Q' ) =四半期
・ TRUNC( DATE,'MONTH' ) =月
・ TRUNC( DATE,'DD' ) =日
・ TRUNC( DATE,'HH24' ) =時間
・ TRUNC( DATE,'MI' ) =分
・ TRUNC( DATE,'DAY' ) = その週が始まった日
[使用例]
SELECT TO_CHAR( SYSDATE, 'yyyymmdd hh24:mi:ss' ) AS "TRUNC( DATE[,format] )"
FROM DUAL
UNION ALL
SELECT TO_CHAR( TRUNC(SYSDATE), 'yyyymmdd hh24:mi:ss' ) FROM DUAL
UNION ALL
SELECT TO_CHAR( TRUNC(SYSDATE, 'year' ), 'yyyymmdd hh24:mi:ss' ) FROM DUAL
UNION ALL
SELECT TO_CHAR( TRUNC(SYSDATE, 'q' ), 'yyyymmdd hh24:mi:ss' ) FROM DUAL
UNION ALL
SELECT TO_CHAR( TRUNC(SYSDATE, 'month' ), 'yyyymmdd hh24:mi:ss' ) FROM DUAL
UNION ALL
SELECT TO_CHAR( TRUNC(SYSDATE, 'dd' ), 'yyyymmdd hh24:mi:ss' ) FROM DUAL
UNION ALL
SELECT TO_CHAR( TRUNC(SYSDATE, 'hh24' ), 'yyyymmdd hh24:mi:ss' ) FROM DUAL
UNION ALL
SELECT TO_CHAR( TRUNC(SYSDATE, 'mi' ), 'yyyymmdd hh24:mi:ss' ) FROM DUAL
UNION ALL
SELECT TO_CHAR( TRUNC(SYSDATE, 'day' ), 'yyyymmdd hh24:mi:ss' ) FROM DUAL;
TRUNC( DATE[,format] )
----------------------
20111025 11:51:13
20111025 00:00:00 ---> TRUNC(SYSDATE)
20110101 00:00:00 ---> TRUNC(SYSDATE, 'year')
20111001 00:00:00 ---> TRUNC(SYSDATE, 'q')
20111001 00:00:00 ---> TRUNC(SYSDATE, 'month')
20111025 00:00:00 ---> TRUNC(SYSDATE, 'dd')
20111025 11:00:00 ---> TRUNC(SYSDATE, 'hh24')
20111025 11:51:00 ---> TRUNC(SYSDATE, 'mi')
20111023 00:00:00 ---> TRUNC(SYSDATE, 'day')
・EXTRACT
Syntax : EXTRACT (element FROM datetime)
EXTRACTはDATEやTIMESTAMPデータのみ抽出する時使われます。 Year,Month,DayはDATEから抽出でき、Hour,Minute,SecondはTIMESTAMPから抽出することができます。
ELEMENT
-------
year
month
day
hour
minute
second
timezone_hour
timezone_minute
timezone_region
timezone_abbr
[使用例]
col sysdate_date for a17
col systimestamp_date for a17
SELECT TO_CHAR(SYSDATE,'yyyymmdd hh24:mi:ss') AS sysdate_date
,TO_CHAR(systimestamp,'yyyymmdd hh24:mi:ss') AS systimestamp_date
,EXTRACT (year FROM sysdate) AS ext_year
,EXTRACT (month FROM sysdate) AS ext_month
,EXTRACT (day FROM sysdate) AS ext_day
,EXTRACT (hour FROM systimestamp) AS ext_hour
,EXTRACT (minute FROM systimestamp) AS ext_minute
,EXTRACT (second FROM systimestamp) AS ext_second
FROM DUAL ;
SYSDATE_DATE SYSTIMESTAMP_DATE EXT_YEAR EXT_MONTH EXT_DAY EXT_HOUR EXT_MINUTE EXT_SECOND
---------------- ------------------ -------- ---------- ---------- ---------- ---------- ---------
20111025 10:54:55 20111025 10:54:55 2011 10 25 1 5 55.544
SELECT TO_CHAR(TO_DATE('20111025 10:45:55','yyyymmdd hh24:mi:ss'),
'yyyymmdd hh24:mi:ss') AS sysdate_date
,TO_CHAR(TO_TIMESTAMP('20111025 10:45:55','yyyymmdd hh24:mi:ss'),
'yyyymmdd hh24:mi:ss') AS systimestamp_date
,EXTRACT (year FROM TO_DATE('20111025 10:45:55','yyyymmdd hh24:mi:ss'))
AS ext_year
,EXTRACT (month FROM TO_DATE('20111025 10:45:55','yyyymmdd hh24:mi:ss'))
AS ext_month
,EXTRACT (day FROM TO_DATE('20111025 10:45:55','yyyymmdd hh24:mi:ss')
AS ext_day
,EXTRACT (hour FROM TO_TIMESTAMP('20111025 10:45:55','yyyymmdd hh24:mi:ss'))
AS ext_hour
,EXTRACT (minute FROM TO_TIMESTAMP('20111025 10:45:55','yyyymmdd hh24:mi:ss'))
AS ext_minute
,EXTRACT (second FROM TO_TIMESTAMP('20111025 10:45:55','yyyymmdd hh24:mi:ss'))
AS ext_second
FROM DUAL ;
SYSDATE_DATE SYSTIMESTAMP_DATE EXT_YEAR EXT_MONTH EXT_DAY EXT_HOUR EXT_MINUTE EXT_SECOND
----------------- ----------------- -------- ---------- ---------- ---------- --------- ---------
20111025 10:45:55 20111025 10:45:55 2011 10 25 10 45 55
ここまで日データ演算に対して調べてみました。
最も難しい部分は日方式で保存されたCHARあるいはVARCHAR2タイプのデータに対する演算です。 なぜなら、演算する対象のデータ タイプと、関数をどのように使うかにより抽出しようと思う結果値が間違えるためです。 今まで前で調べてみた内容に対して正確な理解をしたとすれば、誤ったデータの抽出や発生可能なエラーから解放されるでしょう。
３回にわたってお伝えいたしました、「日データ関連関数紹介」は、皆様のOracle lifeに役立ちますでしょうか？
次回からは、「任意のデータを活用した効率的なSQLの作成」と題しまして、２回シリーズでお伝えいたします。
ご期待ください。それは、see you ^^
