前回書いた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. データ型書式設定関数のページをご確認ください。
to_charを書いてみる
それではto_charを使ってTIMESTAMP型を意図した表示になるように書いてみます。
準備
使用するテーブルです。
| term_id | term_date_start | term_date_end |
|---|---|---|
| 1 | 2021-01-02T00:00:00Z | 2021-01-08T18:45:00Z |
| 2 | 2021-01-03T15:30:00Z | 2021-01-07T06:15:00Z |
| 3 | 2021-01-05T08:00:00Z | 2021-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_id | start_date | end_date |
|---|---|---|
| 1 | 2021/01/02 | 2021/01/08 |
| 2 | 2021/01/03 | 2021/01/07 |
| 3 | 2021/01/05 | 2021/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_id | start_date | end_date |
|---|---|---|
| 1 | 2021/01/02 00:00 | 2021/01/08 18:45 |
| 2 | 2021/01/03 15:30 | 2021/01/07 06:15 |
| 3 | 2021/01/05 08:00 | 2021/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_id | start_date | end_date |
|---|---|---|
| 1 | 2021/01/02 00:00 SATURDAY | 2021/01/08 18:45 FRI |
| 2 | 2021/01/03 15:30 SUNDAY | 2021/01/07 06:15 THU |
| 3 | 2021/01/05 08:00 TUESDAY | 2021/01/09 18:50 SAT |
DAYはSATURDAY、DYはSATなどテンプレートを使えば簡単に表示できます。
おわり
テンプレート文字列（フォーマット）の書き方はSQLによって異なるので他で慣れていると間違えたりしやすいかと思います。とりあえずパターンを見て書ければいいかなと思っています。
