読者です 読者をやめる 読者になる 読者になる

HHeLiBeXの日記 正道編

日々の記憶の記録とメモ‥

各DBMSで日付型の値から曜日を取り出す

SQL PostgreSQL MySQL DB2 Oracle SQL Server Firebird Derby SQLite H2 HSQLDB

最初、PostgreSQLでdate_part関数を使用している処理があって、それをMySQLではどう書けばいいかというのを調べたことから始まったのだが。
手始めに次のブログを見つけた。

で、結局はMySQLではdate_formatは使わずに別の関数を使ったのだが、それも含めて、整理してみようと思ったらしい。
参考にしたのは、次の本。

【改訂第3版】 SQLポケットリファレンス (POCKET REFERENCE)

【改訂第3版】 SQLポケットリファレンス (POCKET REFERENCE)

あと、次のWebページ。

ざっと探して見つけたのは次の関数。(引数の 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プログラムは次のもの。