to_char()でTIMESTAMP型をフォーマットして表示する方法!PostgreSQL

2021-02-14

前回書いたTSRANGEで取得した結果が気に入らないので、意図した表示になるよう調べてみました。

TIMESTAMP型をフォーマットする

TIMESTAMP型フォーマットするにはto_charを使用すれば良いようです。

to_char()

to_char()はタイムスタンプを文字列に変換する際に使用されます。

to_charの書き方

to_char(timestamp, text)

textの部分にフォーマットする形式を書きます。試しに書いてみると以下のようになります。

to_char( columnA, 'HH12:MI:SS' )

この「HH12:MI:SS」のことを出力テンプレート文字列と言います。

テンプレートパターン

日時や日付を文字列に変換する際にどのような形式で表示するか指定することが可能です。その際に使用されるパターンの一覧です。

パターン説明
HH時(01~12)
HH12時(01~12)
HH24時(00~23)
MI分(00~59)
SS秒(00~59)
AM、am、 PM、またはpm午前/午後の指定(ピリオドなし)
A.M.、a.m.、 PM、またはp.m.午前/午後の指定(ピリオド付き)
Y,YYY句読点(コンマ)付き年(4桁以上)
YYYY年(4桁以上)
Month大文字で書き始める完全な月名(9文字になるように空白でパッド)
MON大文字での短縮形の月名(英語では3文字、現地語化された場合は可変長)
Mon大文字で書き始める短縮形の月名(英語では3文字。現地語化された場合は可変長)
MM月番号(01~12)
DDひと月通算の日にち番号(01~31)
DAY大文字での完全な曜日名(9文字になるように空白でパッド)
PostgreSQL文書 9.8. データ型書式設定関数より

量が多いので一部抜粋しています。詳細は、PostgreSQL文書 9.8. データ型書式設定関数のページをご確認ください。

to_charを書いてみる

それではto_charを使ってTIMESTAMP型を意図した表示になるように書いてみます。

準備

使用するテーブルです。

term_idterm_date_startterm_date_end
12021-01-02T00:00:00Z2021-01-08T18:45:00Z
22021-01-03T15:30:00Z2021-01-07T06:15:00Z
32021-01-05T08:00:00Z2021-01-09T18:50:00Z

CREATE文です。

-- CREATE文
CREATE TABLE term (
term_id numeric(8, 0) unique not null,
term_date_start TIMESTAMP,
term_date_end TIMESTAMP,
primary key(term_id)
);
-- INSERT文
insert into term
values(0001, '2021/01/02 0:00','2021/01/08 18:45'),
(0002, '2021/01/03 15:30','2021/01/07 6:15'),
(0003, '2021/01/05 8:00','2021/01/09 18:50')

テスト環境

SQL FIDDLEのPostgreSQL9.6で動作確認しました。

年月日のみ取り出す YYYY/MM/DD

TIMESTAMP型でも年月日のみ取得することもできます。

SELECT
    term_id,
    TO_CHAR(term_date_start, 'YYYY/MM/DD') AS start_date,
    TO_CHAR(term_date_end, 'YYYY/MM/DD') AS end_date
FROM
    term

結果

term_idstart_dateend_date
12021/01/022021/01/08
22021/01/032021/01/07
32021/01/052021/01/09

スラッシュ「/」をハイフン「-」にすることで「2021-01-02」と表示することも可能です。

年月日時分を取り出す

TIMESTAMP型から年月日時分を取得してみます。

SELECT
    term_id,
    TO_CHAR(term_date_start, 'YYYY/MM/DD HH24:MI') AS start_date,
    TO_CHAR(term_date_end, 'YYYY/MM/DD HH24:MI') AS end_date
FROM
    term

結果

term_idstart_dateend_date
12021/01/02 00:002021/01/08 18:45
22021/01/03 15:302021/01/07 06:15
32021/01/05 08:002021/01/09 18:50

'YYYY/MM/DD HH24:MI' にSSをつけて'YYYY/MM/DD HH24:MI:SS'にすると秒も取り出せます。
例:2021/01/02 00:00:00

おまけ 曜日も表示する

英語ですが曜日も出してみようと思います。

SELECT
    term_id,
    TO_CHAR(term_date_start, 'YYYY/MM/DD HH24:MI DAY') AS start_date,
    TO_CHAR(term_date_end, 'YYYY/MM/DD HH24:MI DY') AS end_date
FROM
    term

結果

term_idstart_dateend_date
12021/01/02 00:00 SATURDAY2021/01/08 18:45 FRI
22021/01/03 15:30 SUNDAY2021/01/07 06:15 THU
32021/01/05 08:00 TUESDAY2021/01/09 18:50 SAT

DAYはSATURDAY、DYはSATなどテンプレートを使えば簡単に表示できます。

おわり

テンプレート文字列(フォーマット)の書き方はSQLによって異なるので他で慣れていると間違えたりしやすいかと思います。とりあえずパターンを見て書ければいいかなと思っています。

PostgreSQL, SQL

Posted by Nakamoto