[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」からの経過秒数を持っています。
全期間にわたってグレゴリオ暦として扱われます。
0001-01-01 through 9999-12-31
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
TIMESTAMPtype 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.
For Informatica, the range is limited to 1582-10-15 (October 15, 1582 CE) to 9999-12-31 (December 31, 9999 CE).
January 1, 1753, through December 31, 9999
大英帝国では、1752年の9月にユリウス暦からグレゴリオ暦に改暦しています。そのため、1752年9月には19日しか存在しません。改暦を跨いで対応できるようにすると大変なので、Sybase SQL Serverでは、グレゴリオ暦になってからの日付を扱うように、改暦の翌年である1753年を日付時刻型が扱える範囲の開始年としました。そして、それをMicrosoft SQL Serverも引き継いでいる、ということです。
[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 - そう「イギリスおよびその植民地」でユリウス暦 からグレゴリオ暦への改暦が行われた翌年の初日なのでした。
1900-01-01 through 2079-06-06
[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
標準SQLでは、「"日付時刻リテラル"定義の中で、"日付時刻の値"はグレゴリオ暦に従った日付と時間の自然法則に則る」と明記されています。 PostgreSQLは標準SQLの指針に従い、グレゴリオ暦が使われる以前の年に対してもグレゴリオ暦で日付を数えます。 この規則は先発グレゴリオ暦として知られています。
A calendar applied to dates when it was not actually in use is called proleptic. Thus, if we assume there was never a cutover and Gregorian rules always rule, we have a proleptic Gregorian calendar. This is what is used by MySQL, as is required by standard SQL.
[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
ISO ISO 8601, SQL standard 1997-12-17 07:37:16-08
SQL traditional style 12/17/1997 07:37:16.00 PST
Postgres original style Wed Dec 17 07:37:16 1997 PST
German regional style 17.12.1997 07:37:16.00 PST
SQL, DMY day/month/year 17/12/1997 15:37:16.00 CET
SQL, MDY month/day/year 12/17/1997 07:37:16.00 PST
Postgres, DMY day/month/year Wed 17 Dec 07:37:16 1997 PST
[29] 日付/時刻型 () https://msdn.microsoft.com/ja-JP/library/ff848733.aspx
When timestamp values are stored as eight-byte integers (currently the default), microsecond precision is available over the full range of values. When timestamp values are stored as double precision floating-point numbers instead (a deprecated compile-time option), the effective limit of precision might be less than 6. timestamp values are stored as seconds before or after midnight 2000-01-01. When timestamp values are implemented using floating-point numbers, microsecond precision is achieved for dates within a few years of 2000-01-01, but the precision degrades for dates further away. Note that using floating-point datetimes allows a larger range of timestamp values to be represented than shown above: from 4713 BC up to 5874897 AD.
[36] データ型 () https://docs.oracle.com/cd/E57425_01/121/SQLRF/sql_elements001.htm
Oracle はユリウス日 (紀元前4713年1月1日) からの経過日数+秒で日付データ
を管理しています。
紀元前4713年1月1日から2009年3月15日の経過日数は、2454906日です。
こでに、11:42 は (午前とすると) 経過秒は42120秒となります。
1日は、86400 秒なので0.4875 となり、2454906.4875 として格納されます。
[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/