日付・時刻・テキストの相互変換

PostgreSQLで学ぶ時系列分析

Jasmin Ludolf

Content Developer, DataCamp

フォーマット文字列

YYYY: 4 桁の年
MM: 2 桁の月（数値）
DD: 2 桁の日（数値）
HH24: 24 時間制の 2 桁の時
HH12: 12 時間制の 2 桁の時
HH: 12 時間制の 2 桁の時
MI: 分
SS: 秒

文字列を日付に変換

TO_DATE(): 文字列を日付に変換
TO_DATE('text string', 'format string')
不適切なフォーマット文字列は誤ったデータやエラーの原因になります

SELECT
    TO_DATE('2023-01-15', 'YYYY-MM-DD') AS date_1,
    TO_DATE('20231501', 'YYYYDDMM') AS date_2,
    TO_DATE('Jan 15, 2023', 'Mon DD, YYYY') AS date_3;

|date_1    |date_2    |date_3    |
|----------|----------|----------|
|2023-01-15|2023-01-15|2023-01-15|

文字列を日付に変換

CAST(): データ型を相互変換
任意のデータ型で使用可能
CAST('string' AS data_type)

SELECT
    CAST('2023-01-15' AS DATE) AS date_1,
    CAST('20230115' AS DATE) AS date_2,
    CAST('Jan 15, 2023' AS DATE) AS date_3;

|date_1    |date_2    |date_3    |
|----------|----------|----------|
|2023-01-15|2023-01-15|2023-01-15|

キャスト演算子

:: はキャスト演算子
動作は CAST() と同じ
PostgreSQL のみ

SELECT
    '2023-01-15'::DATE AS date_1,
    '20230115'::DATE AS date_2,
    'Jan 15, 2023'::DATE AS date_3;

|date_1    |date_2    |date_3    |
|----------|----------|----------|
|2023-01-15|2023-01-15|2023-01-15|

日付時刻へ変換

TO_TIMESTAMP(): 文字列を日付時刻に変換
TO_TIMESTAMP('string', 'format string')
日付・時刻・タイムゾーンを含む

SELECT
    TO_TIMESTAMP('Jan 15, 2023 14:02:01', 'Mon DD, YYYY HH24:MI:SS') AS date_time;

|date_time                |
|-------------------------|
|2023-01-15 14:02:01+01:00|

Unix 時間の変換

Unix 時間は TO_TIMESTAMP() で変換できます

SELECT
     TO_TIMESTAMP(1483444800) AT TIME ZONE 'UTC' as datetime;

|datetime           |
|-------------------|
|2017-01-03 12:00:00|

Unix 時間の抽出

EXTRACT(): 値を取得します
Unix 時間では、エポックから時刻までの経過秒を計算します

SELECT
    EXTRACT(epoch FROM TIMESTAMP '2017-01-03 12:00:00') AS unix_time;

|unix_time |
|----------|
|1483444800|

フィールドの変換

形式が統一されたフィールド

-> TO_DATE または TO_TIMESTAMP を使用

date
Jan 15, 2023
Jan 16, 2023
Jan 17, 2023

SELECT TO_DATE(date, 'Mon DD, YYYY');

|date      |
|----------|
|2023-01-15|
|2023-01-16|
|2023-01-17|

フィールドの変換

形式がばらつくフィールド

-> CAST() または :: を使用

date
2023-01-15
20230116
Jan 17, 2023

SELECT CAST(date AS DATE);

|date      |
|----------|
|2023-01-15|
|2023-01-16|
|2023-01-17|

日付・時刻をテキストへ変換

TO_CHAR(): 日時データをテキストに変換
TO_CHAR(date or time, 'format string')

SELECT
    timestamp_field,
    TO_CHAR(timestamp_field, 'YYYY-mm-DD HH12:MI:SS') AS timestamp_text
FROM timetable;

|timestamp_field    |timestamp_text     |
|-------------------|-------------------|
|2015-07-14 11:49:00|2015-07-14 11:49:00|
|2020-10-18 20:53:50|2020-10-18 08:53:50|
|2020-12-31 12:59:59|2020-12-31 12:59:59|

データ型の確認

SELECT
    pg_typeof(timestamp_field) AS "type of(timestamp_field)",
    pg_typeof(TO_CHAR(timestamp_field, 'YYYY-mm-DD HH12:MI:SS')) 
    AS "type of(timestamp_text)"
FROM timetable;

|type of(timestamp_field)   |type of(timestamp_text)|
|---------------------------|-----------------------|
|timestamp without time zone|text                   |

区切り記号の違い

SELECT
    TO_CHAR(timestamp_field, 'YYYY/mm/DD') AS slashes,
    TO_CHAR(timestamp_field, 'YYYY.mm.DD') AS dots,
    TO_CHAR(timestamp_field, '"Year": YYYY "Month": mm "Day": DD') AS labels
FROM timetable;

|slashes   |dots      |labels                      |
|----------|----------|----------------------------|
|2015/07/14|2015.07.14|Year: 2015 Month: 07 Day: 14|
|2020/10/18|2020.10.18|Year: 2020 Month: 10 Day: 18|
|2020/12/31|2020.12.31|Year: 2020 Month: 12 Day: 31|
|2021/01/01|2021.01.01|Year: 2021 Month: 01 Day: 01|

非数値テキスト

SELECT
    TO_CHAR(timestamp_field, 'Dy, Mon DD, YYYY') AS date
FROM timetable;

|date             |
|-----------------|
|Tue, Jul 14, 2015|
|Sun, Oct 18, 2020|
|Thu, Dec 31, 2020|
|Fri, Jan 01, 2021|

カスタム形式

SELECT TO_CHAR(time_field, 'HH24:MI') AS "HH:MM"
FROM timetable;

|HH:MM|
|-----|
|11:49|
|20:53|
|12:59|
|00:01|

AM と PM

SELECT
    TO_CHAR(time_field, 'HH12:MI:SS AM') AS "AM",
    TO_CHAR(time_field, 'HH12:MI:SS pm') AS pm
FROM timetable;

|AM         |pm         |
|-----------|-----------|
|11:49:00 AM|11:49:00 am|
|08:53:50 PM|08:53:50 pm|
|12:59:59 PM|12:59:59 pm|
|12:01:02 AM|12:01:02 am|

MDY 形式

SELECT
    TO_CHAR(date_field, 'MM/DD/YYYY') AS "MDY Numeric",
    TO_CHAR(date_field, 'Mon DD, YYYY') AS "MDY Expanded",
    TO_CHAR(timetz_field, 'HH24:MI:SS TZ') AS upper_case,
    TO_CHAR(timetz_field, 'HH24:MI:SS tz') AS lower_case,
    TO_CHAR(timetz_field, 'HH24:MI:SS OF') AS utc_offset
FROM timetable;

|MDY Numeric|MDY Expanded|upper_case  |lower_case  |utc_offset  |
|-----------|------------|------------|------------|------------|
|12/31/2020 |Dec 31, 2020|18:49:00 UTC|18:49:00 utc|18:49:00 +00|

練習しましょう！

PostgreSQLで学ぶ時系列分析