備忘録的プログラミングリファレンス

日付・時刻型 DATE, TIMESTAMP, TIME

　ここでいう日付・時刻型とは、データベースで扱う日付や時間に関するデータ型のことです。

　日付・時刻型は主に以下があります。

Date 型
タイプ名概要
DATEYYYY-MM-DD。1000-01-01 〜 9999-12-31
TIMESTAMPYYYY-MM-DD HH:MI:SS。1970-01-01 00:00:00 〜 2037-12-31 23:59:59
TIMEHHH:MI:SS。-838:59:59 〜 838:59:59

　他にもデータベースごとに日付・時刻を表すデータタイプがありますので調べてみてください。

　日付・時刻型に定義したカラムにデータを記録するには使用したデータタイプに合わせた日付・時刻を表すフォーマットを使用する必要があります。

日付・時刻型について

　日付・時刻型で扱う日付や時間を表す要素には以下があります。

タイプ名概要
日付
YYYY年度。1000 ~ 9999
MM月度。01 ~ 12
DD日にち。01 ~ 31
時刻
HH時間。01 ~ 23
MI分。01 ~ 59
SS秒数。01 ~ 59
HHH経過時間。-838 ~ 838

　以下は日付・時刻を表す主なデータイプです。

タイプ名概要
DATEYYYY-MM-DD。1000-01-01 〜 9999-12-31
TIMESTAMPYYYY-MM-DD HH:MI:SS。1970-01-01 00:00:00 〜 2037-12-31 23:59:59
TIMEHHH:MI:SS。-838:59:59 〜 838:59:59

　データタイプで指定されている日付、時刻を表すフォーマットに従った入力データのみを受け付けます。
　例えば TIMESTAMP 型に定義したカラムには 'YYYY-MM-DD HH:MI:SS' のような形式の文字列を入力しないとエラーになります。
　以下のように定義した場合には date_time カラムへの日付・時刻データは 'YYYY-MM-DD HH:MI:SS' のような形式である必要があります。

TIMESTAMP 型の定義
CREATE TABLE sales_slip (
	..
	date_time 	TIMESTAMP DEFAULT CURRENT_TIMESTAMP,	/* 日付 */
	..
);

..
> UPDATE sales_slip
 	SET date_time = '2023-10-29 07:17:00'
	WHERE id = 1;

　日付・時刻型に入力可能な形式には ISO 8601 に定義されたものに以下があります。

タイプ名概要
日付
YYYY-MM-DD2023-10-29
YYYYMMDD20231029
YYMMDD231029
時刻
HH:MI:SS07:17:00
HHMISS071700
日付と時刻
YYYY-MM-DD HH:MI:SS2023-10-29 07:17:00
YYYY-MM-DDTHH:MI:SS2023-10-29T07:17:00
YYYYMMDDTHHMISS20231029T071700

　日付と時刻を合わせるためにはTを挟みます。
　PostgreSQL では20231029 071700のように空白文字を挟むことができます。

　TIMESTAMP 型は MySQL ではデフォルトでタイムゾーン有りになります。PostgreSQL では WITH TIMEZONE というオプションを付加しないとデフォルトではタイムゾーンなしになります。

日付・時刻型に関する関数

　日付・時刻型に関する関数には、現在の日付、時刻を返す関数、データタイプに変換する CAST() 関数や日付を表す文字列をデータタイプに合わせて変換する関数があります。

現在の日時

　現在の日時を返す関数には NOW()、CURRENT_TIMESTAMP、CURRENT_DATE、CURRENT_TIME があります。

NOW()
> SELECT NOW();

..
| NOW()               |
+---------------------+
| 2023-10-31 20:33:03 |

　NOW() 関数は現在の日時を返します。CURRENT_TIMESTAMP は NOW() 関数のシノニムです。
　CURRENT_TIMESTAMP() という書き方もできます。

　CURRENT_DATE は現在の日付を返します。

CURRENT_DATE
> SELECT CURRENT_DATE;

..
| CURRENT_DATE |
+--------------+
| 2023-10-31   |

　CURRENT_TIME は現在の時刻を返します。

CURRENT_TIME
> SELECT CURRENT_TIME;

..
| CURRENT_TIME |
+--------------+
| 20:42:06     |

　CURRENT_TIMESTAMP、CURRENT_DATE、CURRENT_TIME は日付・時刻型カラムの初期値にも利用できます。

初期値 CURRENT_TIMESTAMP
CREATE TABLE sales_slip (
	..
	date_time 	TIMESTAMP DEFAULT CURRENT_TIMESTAMP,	/* 日付 */
	..
);

日付・時刻を表す文字列の変換

　文字列の日付を日付・時刻型に変換する関数がデータベースごとに用意されています。

　MySQL では STR_TO_DATE()、PostgreSQL では to_date() や to_timestamp() です。
　MySQL では日付および時間関数として、PostgreSQL ではデータ型書式設定関数として解説されています。

　これらの関数に渡す引数は決められた指定子を使ったフォーマット文字列である必要があります。

　例えば MySQL における日付・時刻を示すフォーマット文字列を作成するための指定子には以下があります。

MySQL 日付・時刻のフォーマット用指定子
指定子概要
%Y４桁の年度
%y２桁の年度
%M月度の名称
%m２桁の月度（　01 ~ 12　）
%d日にち（　01 ~ 31　）
%j１年間の何日目（　01 ~ 366　）
%W曜日の名称
%H時間（　00 ~ 23　）
%h時間（　01 ~ 12　）
%i分（　00 ~ 59　）
%s秒（　00 ~ 59　）
%p午前（ A.M. ）または午後（ P.M. ）
%fマイクロ秒数（　000000 ~ 999999　）

　MySQL で文字列で表された日時を日付・日時型に変換するには以下のようにします。

STR_TO_DATE() ( MySQL )
> SELECT STR_TO_DATE( 'October 29, 2023', '%M %d, %Y' );

..
| STR_TO_DATE( 'October 29, 2023', '%M %d, %Y' ) |
+------------------------------------------------+
| 2023-10-29                                     |

　PostgreSQL では日付・時刻型用に以下のような指定子があります。

PostgreSQL 日付・時刻のフォーマット用指定子
指定子概要
YYYY４桁の年度
YY２桁の年度
Month月度の名称
MM２桁の月度（　01 ~ 12　）
DD日にち（　01 ~ 31　）
DDD１年間の何日目（　01 ~ 366　）
Day曜日の名称
HH24時間（　00 ~ 23　）
HH時間（　01 ~ 12　）
MI分（　00 ~ 59　）
SS秒（　00 ~ 59　）
AM, A.M.,am, a.m., PM, P.M., pm, p.m.午前（ A.M. ）または午後（ P.M. ）
USマイクロ秒数（　000000 ~ 999999　）

　PostgreSQL では TO_DATE() 関数で日付型データに変換されます。

TO_DATE() ( PostgreSQL )
> SELECT TO_DATE( 'October 29, 2023', 'Month DD, YYYY' );

..
to_date
------------
2023-10-29                              |

データタイプへの変換

　日付と時刻を表す文字列を CAST 関数によって DATE、TIMESTAMP、TIME といったデータタイプに変換することができます。

　例えば DATE データタイプに CAST で変換するには以下のようにします。

DATE へ変換
> SELECT CAST( '2023-10-29 07:17:00' AS DATE );

..
| CAST( '2023-10-29 07:17:00' AS DATE ) |
+---------------------------------------+
| 2023-10-29                            |

　TIME タイプに変換するには以下のようにします。

DATE へ変換
> SELECT CAST( '2023-10-29 07:17:00' AS TIME );

..
| CAST( '2023-10-29 07:17:00' AS TIME ) |
+---------------------------------------+
| 07:17:00                              |

　MySQL では TIMESTAMP への変換はできません。

日付・時刻として表された文字列をCAST 関数で変換できる文字列には以下のものがあります。

  • 2023-10-29 07:17:00
  • 2023/10/29 07:17:00
  • 20231029071700

　MySQL では日付と時刻を表す DATETIME タイプへの変換が可能です。

DATE へ変換
> SELECT CAST( '20231029071700' AS DATETIME );

..
| CAST( '20231029071700' AS DATETIME ) |
+--------------------------------------+
| 2023-10-29 07:17:00                  |

　PostgreSQL では TIMESTAMP タイプへの変換が可能です。ただし、日付と時刻の間に空白文字が必要です。

DATE へ変換
> SELECT CAST( '20231029 071700' AS TIMESTAMP );

..
      timestamp
---------------------
 2023-10-29 07:17:00

　CAST 関数はデータベースごとに仕様が異なります。詳しくはデータベースごとの CAST 関数で調べてみてください。

タイムゾーン

　インターネットのある世界では日中や夜間といった時間帯が違う場所からのアクセスが日常となっています。
　地域ごとに時間帯が違うため、アクセスしている場所が必ずしも同じ時間帯ではない場合があります。

　コンピュータの世界では時間は、地球を経度によって 24 に分けて協定標準時（ Coordinated Universal Time, UTC ）を基準に何時間違うかという時差によって時刻を表す方法があります。
　この 24 に分けられた地域ごとでは同じ時間帯を使用しています。この時間帯ごとの地域をタイムゾーン（時間帯、時刻帯）といいます。

　協定標準時（ UTC ）はグリニッジ標準時（ Greenwich Mean Time、GMT ）の 1 種です。

MySQL タイムゾーンごとの確認

　MySQL では UTC_TIMESTAMP() 関数で協定標準時（ UTC ）の基準の箇所の時刻を知ることができます。
　または、 CONVERT_TZ() 関数でタイムゾーンごとの時刻を知ることができます。ただし、こちらの関数は タイムゾーンデータ のロードと設定を済ませておく必要があります。

基準の時刻　MySQL
> SELECT CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP   |
+---------------------+
| 2023-11-02 07:41:34 |
+---------------------+

> SELECT UTC_TIMESTAMP();
+---------------------+
| UTC_TIMESTAMP()     |
+---------------------+
| 2023-11-01 22:41:58 |
+---------------------+

　上記はクエリの実行時間が違うために秒数に違いがあります。

PostgreSQL タイムゾーンごとの確認

　PostgreSQL では TIMEZONE( zone, timestamp ) 関数で指定地域の時刻を知ることができます。
　第１引数に地域名を第２引数に TIMESTAMP 型の日時を渡します。

地域ごとの時刻　PostgreSQL
> SELECT CURRENT_TIMESTAMP;
       current_timestamp
-------------------------------
2023-11-01 21:09:03.333288+09


> SELECT TIMEZONE( 'America/New_York', CURRENT_TIMESTAMP );
       timezone
----------------------------
2023-11-01 08:10:42.243594


> SELECT TIMEZONE( 'Asia/Shanghai', CURRENT_TIMESTAMP );
       timezone
----------------------------
2023-11-01 20:11:52.529265

　インターネットの世界では、協定標準時（ UTC ）を設けて地域ごとを時差によって表すことで時間の概念を理解しやすくしてあります。

　協定標準時（ UTC ）の基準、0 時はロンドンになっています。ロンドンを基準に地球を経度 15° ずつに分けています。 東京はロンドよりも 9 時間進んでいます。これをUTC +9と表します。

　インターネットを利用したデータベースでは場合によっては時刻を統一する必要があります。
　ローカルのみでデータベースを利用する場合は問題になり難いのですが、世界中から利用する場合には問題が発生します。地域ごとの時刻を入力値として記録する場合にはズレが生じます。
　サーバーで統一した日時を利用するや日時を入力する場合にはタイムゾーンごとのオフセット値を必要とするといった配慮が必要になります。

　TIMESTAMP 型は、MySQL ではデフォルトでタイムゾーン有りになります。PostgreSQL では WITH TIMEZONE というオプションを付加しないとデフォルトではタイムゾーンなしになることに注意が必要です。

　データベースを世界中から利用し、時刻を記録する場合は協定標準時（ UTC ）を意識してデータベースの設計を行った方がよいでしょう。

CURRENT_TIMESTAMP を初期値に

　日付・時刻型カラムのデフォルト値として CURRENT_TIMESTAMP を利用するには以下のようにします。

地域ごとの時刻　PostgreSQL
CREATE TABLE sales_slip{
	..
	date_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	..
}

PostgreSQL 基準時刻を初期値に

　PostgreSQL でカラムのデフォルト値としてタイムゾーンを使用するには以下のようにします。

タイムゾーンを考慮　PostgreSQL
CREATE TABLE sales_slip{
	..
	date_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
	..
}

日時要素のみを取得

　日付・時刻データから年度や月度、日付のみを取り出すには EXTRACT() 関数を使用します。

EXTRACT() 関数
> SELECT
 	EXTRACT( YEAR FROM date_time ) AS year,
 	EXTRACT( MONTH FROM date_time ) AS month
 	FROM sales_slip;
+------+-------+
| year | month |
+------+-------+
| 2023 |    10 |
| 2023 |     9 |
| 2023 |     9 |
| 2023 |     9 |
| 2023 |     9 |
+------+-------+

　EXTRACT()は日時要素を取得するための関数です。EXTRACT( 日時要素 FROM 日時データ )といった使い方をします。
　年度は YEAR、月度は MONTH、日にちは DAY です。時間は HOUR、分は MINUTE、秒は SECOND です。