[28] SQL データベースにおいて日時は重要なデータ型です。 SQL の他の部分と同じように、また他のプラットフォームの日時表現と同じように、 実装ごとに多種多様に取り扱われていて、可搬性はそれほど高くありません。
[49] 取り扱い可能な日時の範囲はデータベースの実装によりまちまちです。 同じ製品でもデータ型によって異なることがあります。
DATE値の書式はYYYY-MM-DD HH:MI:SSで、範囲は-4712-01-01(紀元前4712年1月1日)から9999-12-31(西暦9999年12月31日)です。小数秒はありません。DATE型では7バイトの記憶域が必要です。
TIMESTAMP値の書式は、YYYY-MM-DD HH:MI:SS [.FFFFFFFFF]です。小数秒の精度の範囲は0から9で、デフォルトは6です。日付範囲は-4712-01-01(紀元前4712年1月1日)から9999-12-31(西暦9999年12月31日)です。TIMESTAMP型では12バイトの記憶域が必要です。
Oracle DatabaseのDate型が扱える範囲は、
-4712/01/01 00:00:00 ~ 9999/12/31 23:59:59
です。(Oracle Databaseの日本語マニュアルでは「西暦前4712年」という記載ですが、ここではマイナス表記で記載します)
ユリウス暦からグレゴリオ暦への改暦は、1582年10月に行われたものとして処理され、ユリウス暦時代の年月日は、ユリウス暦に従った日付で扱われ、グレゴリオ暦へ改暦されて以降は、グレゴリオ暦として扱われます。
PostgreSQLのtimestamp型が扱える範囲は、バージョン8.4以降では、 西暦紀元前4713/01/01 ~ 294276/12/31 です。
「5874897年まで扱える」としていたバージョンもありましたが、PostgreSQL 8.4から、timestamp型の内部型を倍精度浮動小数点数から8バイト整数に変更したことで、現在の範囲になりました。 今の範囲でも、他のデータベースに比べて十分大きいのですけれど。内部的には「2000/01/01 00:00:00」からの経過秒数を持っています。
全期間にわたってグレゴリオ暦として扱われます。
Microsoft SQL Serverのdatetime2型やDb2のtimestamp型など、現在の多くのデータベースでは、 0001/01/01 00:00:00 ~ 9999/12/31 23:59:59 の範囲を扱うことができる日付時刻型が用意されています。
Microsoft SQL Server 2008以降では、datetime2型という 0001/01/01 00:00:00 ~ 9999/12/31 23:59:59 の範囲を扱える日付時刻型が追加されています。 また、Microsoft SQL Serverの生き別れた兄の子にあたるSybase Adaptive Server Enterprise(Sybase ASE)では、datetime型自体が拡張されていて、 0001/01/01 00:00:00 ~ 9999/12/31 23:59:59 の範囲を扱えるようになっています。これらはいずれも、全期間にわたってグレゴリオ暦として扱われます。
Microsoft Accessの日付/時刻型は、 100/01/01 00:00:00~9999/12/31 23:59:59 の範囲です。 Accessの場合、内部数値の「0」は「1899/12/30」を表します。
Microsoft Accessでは、全期間にわたってグレゴリオ暦として扱われます。
MySQLのdatetime型が扱える範囲は、 1000/01/01 00:00:00 ~ 9999/12/31 23:59:59 です。
この開始日時は「サポートしている範囲」なので、それ以前の日付も入力できます。 MySQLでは、全期間にわたってグレゴリオ暦として扱われます。
[66] 「サポート」とは???
[47] SQL Differences Between Impala and Hive | 5.6.x | Cloudera Documentation, , https://docs.cloudera.com/documentation/enterprise/5-6-x/topics/impala_langref_unsupported.html#langref_hiveql_semantics
The Impala
TIMESTAMP
type can represent dates ranging from 1400-01-01 to 9999-12-31. This is different from the Hive date range, which is 0000-01-01 to 9999-12-31.
[80] Oracle TimesTen In-Memory Database SQLリファレンス・ガイド, , https://docs.oracle.com/cd/F25597_01/document/products/timesten/html/E05176-03/SQL_Reference_Guide-03-11.htm
TT_DATE値の書式はYYYY-MM-DDで、範囲は1753-01-01(1753年1月1日)から9999-12-31(西暦9999年12月31日)です。TT_DATEデータ型では4バイトの記憶域が必要です。
TT_TIMESTAMP値の書式は、YYYY-MM-DD HH:MI:SS [.FFFFFF]です。小数秒の精度は6です。範囲は1753-01-01 00:00:00(1753年1月1日の午前0時)から9999-12-31 23:59:59(9999年12月31日の午後11時59分59秒)です。TT_TIMESTAMP型では8バイトの記憶域が必要です。TT_TIMESTAMPは、TIMESTAMPデータ型より高速で、TIMESTAMP型より記憶域サイズが小さくなります。
[78] 日付, JasmineSoft, , https://wagby.com/manual9/modelitem-date.html
年は1753年〜9999年の範囲で指定できます。
[83] 診療報酬明細作成プログラム: suchowan's blog, 2013年06月13日, https://suchowan.at.webry.info/201306/article_13.html
プログラムの都合上初診の日付が必要なので、プログラムが仮につけた 日付が、1753/01/01 - そう「イギリスおよびその植民地」でユリウス暦 からグレゴリオ暦への改暦が行われた翌年の初日なのでした。
[57] SqlDateTime 構造体 (System.Data.SqlTypes) | Microsoft Docs, dotnet-bot, https://docs.microsoft.com/ja-jp/dotnet/api/system.data.sqltypes.sqldatetime?view=net-6.0
SqlDateTime では、00:00:00 AM 1/1/1900 を起点とした相対的な差が格納されます。 つまり、"00:00:00 AM 1/1/1900" を整数に変換すると、0 が返されます。
[60] 32ビットUnix time (いわゆる2038年問題)
一昔前のデータベースでは、
1970/01/01 00:00:00 ~ 2038/01/19 03:14:07
など、期間の範囲しか扱えませんでした。
[3] 特別な値として、日付 0000-00-00
や時刻 00:00:00
、日時
0000-00-00 00:00:00
、年 0000
が使われています。
[4] 2009-00-00
のように月日を 0 としたり、
2009-01-00
のように日を 0 としたりもできます >>2。
[5] 69年までを2000年代、70年からを1900年代として扱います。
[41] PostgreSQL と Oracle では紀元前の表現方法が異なります。
Oracle Databaseでは、ユリウス日の計算に天文学方式を使用しています。この方式では、紀元前4713年は-4712として計算されます。これに対し、歴史学方式では、紀元前4713年は-4713として計算されます。Oracleのユリウス日を、歴史学方式で計算した値と比較する場合には、紀元前の日付に365日の違いがあることに注意してください。 ( https://docs.oracle.com/cd/E57425_01/121/SQLRF/sql_elements001.htm )
Oracle Databaseのマニュアルでは、この「-1年」を「西暦前1年」と記載しています。なので、説明がややこしいことになっているのです。
…なのですが、西暦紀元前1年のデータを入れようと考えて、「0000/01/01」(西暦に0)を指定するとエラーになります…
ここからも、西暦0年が存在するようなユリウス通日が返ってきます。でも、「西暦0年」は扱えないのです。
[71] 天文学的紀年法なのに「西暦前」というのはおかしい。 Oracle の誤訳か。訳者が勝手に変えるとも思えないから原文がおかしいのか。 実装がいかれてるならドキュメントの部署の誤記ではなく製品仕様レベルで間違ってるのか?
[1] MySQL と PostgreSQL は、先発グレゴリオ暦を採用しています。
[39] Oracle は、イタリアにおけるグレゴリオ改暦より前は、 先発ユリウス暦を採用しています。 (イタリア式ユリウスグレゴリオ暦) >>61
[51] datetime (Transact-SQL) - SQL Server | Microsoft Docs, MikeRayMSFT, https://docs.microsoft.com/ja-jp/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver15
[9] MySQL は tzdata を使っています >>7, >>8。
[10] tzdata の主たる形式の他、閏秒オプション版も提供されています。
[11] tzdata の名前の他に、
システム時間帯を表す SYSTEM
や、
+10:00
や -6:00
のような時差の表記にも対応しています >>8。
[24] PostgreSQL は tzdata と POSIX時間帯を使っています >>23, >>25, >>26。
[37] Oracle はtzdataを使っています >>36, >>72。
+|- hh:miの形式(-12:59から+14:00までの値を使用)で1つのエンティティとして解釈されます。
[17] SQL では時間帯を持たない日時のデータ型が使われることも多いです。 SQL 内での時間帯を持つデータ型との変換時や、 SQL を使うプログラミング言語等での時間帯を持つデータ型との変換時に、 取り扱いに注意が必要となります。
[18] 時間帯を持たない日時のデータ型と Unix time との相互変換の手段が提供されていることがありますが、その場合の Unix time は本来の UTC 基準の値ではなく、地方時基準の値になります。
[13] MySQL の TO_DAYS
は日数、 TO_SECONDS
は秒数を返します。
[15] の秒数は 86400 です >>12。 これは正子を表しているようなので、その1日前からの秒数ということになります。
[76] リテラル, https://docs.oracle.com/cd/E57425_01/121/SQLRF/sql_elements003.htm#BABGIGCJ
[89] MySQL :: MySQL 8.0 Reference Manual :: 14.7 Date and Time Functions, https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_from-unixtime
[22] PostgreSQL は独自の時間長形式
(reltime
, timestamp
も参照。) と ISO 8601の時間形式に対応しています
>>23。
[56] CAST および CONVERT (Transact-SQL) - SQL Server | Microsoft Docs, markingmyname, https://docs.microsoft.com/ja-jp/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15
[72] データ型, https://docs.oracle.com/cd/E57425_01/121/SQLRF/sql_elements001.htm
[2] MySQL :: MySQL 8.0 Reference Manual :: 12.3 Date and Time Types () https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html
[7] MySQL :: Time zone description tables () https://dev.mysql.com/downloads/timezones.html
[8] MySQL :: MySQL 8.0 Reference Manual :: 11.6 MySQL Server Time Zone Support () https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html
[12] MySQL :: MySQL 8.0 Reference Manual :: 13.7 Date and Time Functions () https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html
[19] PostgreSQL: Documentation: 9.6: Date/Time Types () https://www.postgresql.org/docs/current/static/datatype-datetime.html
[16] PostgreSQL: Documentation: 9.6: Date/Time Functions and Operators () https://www.postgresql.org/docs/current/static/functions-datetime.html
[23] PostgreSQL: Documentation: 9.6: Date/Time Types () https://www.postgresql.org/docs/current/static/datatype-datetime.html
[25] PostgreSQL: Documentation: 9.6: pg_timezone_abbrevs () https://www.postgresql.org/docs/current/static/view-pg-timezone-abbrevs.html
[26] PostgreSQL: Documentation: 9.6: Date/Time Configuration Files () https://www.postgresql.org/docs/current/static/datetime-config-files.html
[29] 日付/時刻型 () https://msdn.microsoft.com/ja-JP/library/ff848733.aspx
[36] データ型 () https://docs.oracle.com/cd/E57425_01/121/SQLRF/sql_elements001.htm
[43] datetime (Transact-SQL) - SQL Server | Microsoft Docs (MikeRayMSFT著, ) https://docs.microsoft.com/ja-jp/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-2017
[46] 2.5.4 日時値関数 () https://software.fujitsu.com/jp/manual/manualfiles/M100005/J2X17484/02Z200/J7484-00-02-05-04.html
[84] 日付/時刻キーワード, , https://web.archive.org/web/20101229135210/http://www.postgresql.jp/document/pg800doc/html/datetime-keywords.html
[85] dBASE .DBF File Structure, , http://www.dbase.com/Knowledgebase/INT/db7_file_fmt.htm
D Date 8 bytes - date stored as a string in the format YYYYMMDD.
@ Timestamp 8 bytes - two longs, first for date, second for time. The date is the number of days since 01/01/4713 BC. Time is hours * 3600000L + minutes * 60000L + Seconds * 1000L
[87] Data types | BigQuery | Google Cloud, , https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#timestamp_type
[88] BigQuery に日時データをロードする場合のタイムゾーンとフォーマットについて確認してみた | DevelopersIO, みかみ, https://dev.classmethod.jp/articles/bigquery-timestamp-timezone-format/