各DBMSで日付型の値から曜日を取り出す
最初、PostgreSQLでdate_part関数を使用している処理があって、それをMySQLではどう書けばいいかというのを調べたことから始まったのだが。
手始めに次のブログを見つけた。
で、結局はMySQLではdate_formatは使わずに別の関数を使ったのだが、それも含めて、整理してみようと思ったらしい。
参考にしたのは、次の本。
【改訂第3版】 SQLポケットリファレンス (POCKET REFERENCE)
- 作者: 朝井淳
- 出版社/メーカー: 技術評論社
- 発売日: 2009/04/29
- メディア: 単行本(ソフトカバー)
- 購入: 6人 クリック: 117回
- この商品を含むブログ (17件) を見る
ざっと探して見つけたのは次の関数。(引数の d はDATE型の列名)
- (01-1) DATE_FORMAT (d, '%w')
- (01-2) DATE_FORMAT (d, '%a')
- (01-3) DATE_FORMAT (d, '%W')
- (02-1) DATE_PART ('dow', d)
- (03-1) DATENAME (weekday, d)
- (04-1) DATEPART (weekday, d)
- (04-2) DATEPART (w, d)
- (05-1) DAYNAME (d)
- (06-1) DAYOFWEEK (d)
- (07-1) EXTRACT ('dow' FROM d)
- (07-2) EXTRACT (DOW FROM d)
- (07-3) EXTRACT ('isodow' FROM d)
- (07-4) EXTRACT (ISODOW FROM d)
- (07-5) EXTRACT (WEEKDAY FROM d)
- (08-1) WEEKDAY (d)
- (09-1) TO_CHAR (d, 'D')
- (09-2) TO_CHAR (d, 'DY')
- (09-3) TO_CHAR (d, 'DAY')
- (10-1) VARCHAR_FORMAT (d, 'D')
- (10-2) VARCHAR_FORMAT (d, 'DY')
- (10-3) VARCHAR_FORMAT (d, 'DAY')
ちなみに、各関数に対する、各DBMSの対応状況はこんな感じ(で合っているはず)。
関数名 | P o s t g r e S Q L |
M y S Q L |
D B 2 |
O r a c l e |
S Q L S e r v e r |
F i r e b i r d |
D e r b y |
S Q L i t e |
H 2 |
H S Q L D B |
|
---|---|---|---|---|---|---|---|---|---|---|---|
( 1) | DATE_FORMAT | O | |||||||||
( 2) | DATE_PART | O | |||||||||
( 3) | DATENAME | O | |||||||||
( 4) | DATEPART | O | |||||||||
( 5) | DAYNAME | O | O | O | O | ||||||
( 6) | DAYOFWEEK | O | O | O | O | ||||||
( 7) | EXTRACT | O | * | *1 | * | O | * | * | |||
( 8) | WEEKDAY | O | |||||||||
( 9) | TO_CHAR | O | *2 | O | O | ||||||
(10) | VARCHAR_FORMAT | *2 |
- *:関数自体はあるが、曜日取得には使えない。
- *1:v9.7以前では、関数が提供されていない。v9.7以降でも、曜日取得には使えない。
- *2:v9.7以前では、関数自体はあるが、曜日取得には使えない。
前提とするテーブルは次のものとする。
CREATE TABLE hoge(d DATE NOT NULL PRIMARY KEY)
ただし、SQL Server 2005だけはDATE型なんて知らないよ、と怒られるので次のものを使用する。
CREATE TABLE hoge(d DATETIME NOT NULL PRIMARY KEY)
一応、念のため(謎)、DatabaseMetaDataのgetDatabaseProductName()とgetDatabaseProductVersion()で取得したものも書いておく。
DBMS名 | getDatabaseProductName() |
---|---|
getDatabaseProductVersion() | |
DB2 9.1 | DB2/NT |
SQL09012 | |
DB2 9.5 | DB2/NT |
SQL09052 | |
DB2 9.7 | DB2/NT |
SQL09072 | |
Oracle 10g XE | Oracle |
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production | |
PostgreSQL 8.3 | PostgreSQL |
8.3.7 | |
PostgreSQL 8.4 | PostgreSQL |
8.4.2 | |
MySQL 5.1 | MySQL |
5.1.32-community | |
Firebird 2.1 | Firebird 2.1.WI-V2.1.0.17798 Firebird 2.1/tcp (proteus)/P10 |
WI-V2.1.0.17798 Firebird 2.1.WI-V2.1.0.17798 Firebird 2.1/tcp (proteus)/P10 | |
Derby 10.05.03.00 | Apache Derby |
10.5.3.0 - (802917) | |
SQLite 03.06.14.02 | SQLite |
3.6.3 | |
SQLServer 2005 | Microsoft SQL Server |
9.00.4053 | |
SQLServer 2008 | Microsoft SQL Server |
10.00.2531 | |
H2 1.2.126 | H2 |
1.2.126 (2009-12-18) | |
HSQLDB 1.8.1 | HSQL Database Engine |
1.8.1 |
で、次が、各DBMSで実行したときの結果。いろんなパターンがあって表の中に書ききれないので、詳細な説明は表の後に書いてある。
DBMS名 | 01-1 | 01-2 | 01-3 | 02-1 | 03-1 | 04-1 | 04-2 |
---|---|---|---|---|---|---|---|
05-1 | 06-1 | 07-1 | 07-2 | 07-3 | 07-4 | 07-5 | |
08-1 | 09-1 | 09-2 | 09-3 | 10-1 | 10-2 | 10-3 | |
DB2 9.1 | X | X | X | X | X | X | X |
lsj | S1I | X | X | X | X | X | |
X | X | X | X | X | X | X | |
DB2 9.5 | X | X | X | X | X | X | X |
lsj | S1I | X | X | X | X | X | |
X | X | X | X | X | X | X | |
DB2 9.7 | X | X | X | X | X | X | X |
lsc | S1I | X | X | X | X | X | |
X | S1S | ssu | lsu | S1S | ssu | lsu | |
Oracle 10g XE | X | X | X | X | X | X | X |
X | X | X | X | X | X | X | |
X | S1S | ssj | lsj | X | X | X | |
PostgreSQL 8.3 | X | X | X | S0F | X | X | X |
X | X | S0F | S0F | M1F | M1F | X | |
X | S1S | ssu | lsu | X | X | X | |
PostgreSQL 8.4 | X | X | X | S0F | X | X | X |
X | X | S0F | S0F | M1F | M1F | X | |
X | S1S | ssu | lsu | X | X | X | |
MySQL 5.1 | S0S | ssc | lsc | X | X | X | X |
lsc | S1I | X | X | X | X | X | |
M0I | X | X | X | X | X | X | |
Firebird 2.1 | X | X | X | X | X | X | X |
X | X | X | X | X | X | S0I | |
X | X | X | X | X | X | X | |
Derby 10.05.03.00 | X | X | X | X | X | X | X |
X | X | X | X | X | X | X | |
X | X | X | X | X | X | X | |
SQLite 03.06.14.02 | X | X | X | X | X | X | X |
X | X | X | X | X | X | X | |
X | X | X | X | X | X | X | |
SQLServer 2005 | X | X | X | X | lsj | S1I | S1I |
X | X | X | X | X | X | X | |
X | X | X | X | X | X | X | |
SQLServer 2008 | X | X | X | X | lsj | S1I | S1I |
X | X | X | X | X | X | X | |
X | X | X | X | X | X | X | |
H2 1.2.126 | X | X | X | X | X | X | X |
lsc | S1I | X | X | X | X | X | |
X | X | X | X | X | X | X | |
HSQLDB 1.8.1 | X | X | X | X | X | X | X |
lsc | S1I | X | X | X | X | X | |
X | ssj | X | X | X | X | X |
- S0F: Sunday origin/0 origin/float (つまり、日=0.0、月=1.0、‥、土=6.0)
- S0S: Sunday origin/0 origin/string (つまり、日='0'、月='1'、‥、土='6')
- S0I: Sunday origin/0 origin/integer (つまり、日=0、月=1、‥、土=6)
- S1S: Sunday origin/1 origin/string (つまり、日='1'、月='2'、‥、土='7')
- S1I: Sunday origin/1 origin/integer (つまり、日=1、月=2、‥、土=7)
- M0I: Monday origin/0 origin/integer (つまり、日=6、月=0、‥、土=5)
- M1F: Monday origin/1 origin/float (つまり、日=7.0、月=1.0、‥、土=6.0)
- ssu: short style/string/upper case (つまり、"SUN"、"MON"、‥、"SAT")
- lsu: long style/string/upper case (つまり、"SUNDAY"、"MONDAY"、‥、"SATURDAY")
- lsc: long style/string/camel case (つまり、"Sunday"、"Monday"、‥、"Saturday")
- lsj: long style/string/Japanese (つまり、"日曜日"、"月曜日"、‥、"土曜日")
- ssc: short style/string/camel case (つまり、"Sun"、"Mon"、‥、"Sat")
- ssj: short style/string/Japanese (つまり、"日"、"月"、‥、"土")
ただし、言語設定とかによって、日本語が出るのか英語が出るのかは変わる可能性があります。あくまでも私の現環境では、ということで。
大雑把に分けると、「X」は動作せず、「S1S」のようなupper caseは数値、「ssu」のようなlower caseは文字列、という風にしてみたのだけど、やはり見づらい。何かもっといい表現方法があるはず‥
年月日時分秒までは、各DBMSで共通のやり方を見つけられそうなのだけど、曜日は各ベンダーでばらばらなのだなぁと改めて感じたらしい。
ちなみに、実行したJavaプログラムは次のもの。