昨日Windowsのタスクバーで曜日を変更した流れで、PostgreSQLの日付/時刻型データから曜日や月の英語名を出すクエリの整理。実行環境はWindows7 32bit + PostgreSQL Portable 9.4。使う関数to_charの詳細は下記ドキュメントにあります。
» PostgreSQL 9.4.0文書 : データ型書式設定関数(日本語)
» PostgreSQL 9.4.4 Documentation : Data Type Formatting Functions
基本形
to_charの第一引数に時刻/日付型データを入れ、第二引数の書式にMonthなどのキーワードを含む文字列を入力。クエリのコメントにある「9文字パディング」は、日本語ドキュメントにある「9文字になるように空白でパッド」と同じ意味です。
SELECT n :: date,
to_char(n, 'Month'), -- 月名, 先頭大文字, 9文字パディング
to_char(n, 'Mon'), -- 短縮月名, 先頭大文字, 全て3文字
to_char(n, 'Day'), -- 曜日名, 先頭大文字, 9文字パディング
to_char(n, 'Dy') -- 短縮曜日名, 先頭大文字, 3文字
FROM now() AS n;
n | to_char | to_char | to_char | to_char
------------+-----------+---------+-----------+---------
2015-07-19 | July | Jul | Sunday | Sun
上の結果のとおり、月の短縮形Monでは、普通短縮しないJune, Julyや本来4文字になるSeptemberも、全て3文字になります。下記ウェブサイトにある「コンピューターシステムや会社の文書など特定の状況下ではピリオド無しで3文字に統一することもあります」の一例ですね。
» メール通信講座 英語de日記 : 意外に間違えてしまう、月名(month)の短縮形
全て大文字/小文字にする場合
検索等の利便を考えて、曜日・月名を全て大文字/小文字に統一したい場合。to_char関数ではそれ用の書式が既にあり、先ほどのMonth, Day等を全て大(小)文字にするだけ。
SELECT n :: date,
to_char(n, 'MONTH, MON, DAY, DY') -- 全て大文字
FROM now() AS n;
n | to_char
------------+--------------------------------
2015-07-19 | JULY , JUL, SUNDAY , SUN
SELECT n :: date,
to_char(n, 'month, mon, day, dy') -- 全て小文字
FROM now() AS n;
n | to_char
------------+--------------------------------
2015-07-19 | july , jul, sunday , sun
空白を除きたければ、書式の先頭にFMを付ける
上の結果のように、非短縮形の月・曜日名は、常に9文字分となるよう半角空白が付加されます(これがパディング)。こうしたくなければ、書式の先頭にFMを付加。これで「字詰めモード(空白パディングの無効、および0の除去)」になります。
SELECT n :: date,
'---' || to_char(n, 'FMMonth---FMDay') || '---'
FROM now() AS n;
n | ?column?
------------+---------------------
2015-07-19 | ---July---Sunday---
英語の月・曜日名の一覧を出すクエリ
いろんな方法があると思いますが、今日は連番を出すgenerate_series関数を使い、月名は、各月1日の日付型データを作ってto_char関数へ。曜日名は1月1日~7日をto_charへ渡し、ついでに2種類の曜日番号(dow, isodow)との対応が分かるようにしました。
SELECT *, to_char(concat, 'Month Mon')
FROM generate_series(1, 12) AS month,
cast(concat('2015-', month, '-1') AS date);
month | concat | to_char
-------+------------+---------------
1 | 2015-01-01 | January Jan
2 | 2015-02-01 | February Feb
3 | 2015-03-01 | March Mar
4 | 2015-04-01 | April Apr
5 | 2015-05-01 | May May
6 | 2015-06-01 | June Jun
7 | 2015-07-01 | July Jul
8 | 2015-08-01 | August Aug
9 | 2015-09-01 | September Sep
10 | 2015-10-01 | October Oct
11 | 2015-11-01 | November Nov
12 | 2015-12-01 | December Dec
(12 行)
SELECT extract(dow FROM concat) AS dow,
extract(isodow FROM concat) AS isodow,
to_char(concat, 'Day Dy')
FROM generate_series(1, 7) AS day,
cast(concat('2015-1-', day) AS date)
ORDER BY 1;
dow | isodow | to_char
-----+--------+---------------
0 | 7 | Sunday Sun
1 | 1 | Monday Mon
2 | 2 | Tuesday Tue
3 | 3 | Wednesday Wed
4 | 4 | Thursday Thu
5 | 5 | Friday Fri
6 | 6 | Saturday Sat
(7 行)
逆に、月名を月番号に変換するクエリ
何の役に立つか分かりませんが、最後に。月名から各月1日を表す文字列を作り、日付型にキャストして月番号を抽出するだけ。月名は短縮形・非短縮形どちらも使えます。
SELECT month, extract(month FROM concat)
FROM unnest(
-- 下に、変換したい月名をカンマ区切りで自由に入力
'{
January,
Feb,
March,
Apr,
May,
Jun,
Jul,
August,
Sep,
Sept,
Oct,
November,
Dec
}' :: text[]) AS t(month),
cast(concat('1 ', month, ' 2015') AS date);
month | date_part
----------+-----------
January | 1
Feb | 2
March | 3
Apr | 4
May | 5
Jun | 6
Jul | 7
August | 8
Sep | 9
Sept | 9
Oct | 10
November | 11
Dec | 12
(13 行)