[28] [[SQL]] [[データベース]]において[[日時]]は重要な[[データ型]]です。 [[SQL]] の他の部分と同じように、また他の[[プラットフォーム]]の[[日時表現]]と同じように、 [[実装]]ごとに多種多様に取り扱われていて、[[可搬性]]はそれほど高くありません。 * 値域 [49] 取り扱い可能な[[日時]]の[[範囲]]は[[データベース]]の実装によりまちまちです。 同じ製品でも[[データ型]]によって異なることがあります。 ** [TIME[-4712-01-01]]から [63] [[ユリウス紀元]] [82] >>80 >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バイトの記憶域が必要です。 [61] >>34 >Oracle DatabaseのDate型が扱える範囲は、 >-4712/01/01 00:00:00 ~ 9999/12/31 23:59:59 >です。(Oracle Databaseの日本語マニュアルでは「西暦前4712年」という記載ですが、ここではマイナス表記で記載します) >[SNIP[]]ユリウス暦からグレゴリオ暦への改暦は、1582年10月に行われたものとして処理され、ユリウス暦時代の年月日は、ユリウス暦に従った日付で扱われ、グレゴリオ暦へ改暦されて以降は、グレゴリオ暦として扱われます。 [70] 0年以前については >>64 参照。 [62] >>34 > PostgreSQLのtimestamp型が扱える範囲は、バージョン8.4以降では、 西暦紀元前4713/01/01 ~ 294276/12/31 です。 > 「5874897年まで扱える」としていたバージョンもありましたが、PostgreSQL 8.4から、timestamp型の内部型を倍精度浮動小数点数から8バイト整数に変更したことで、現在の範囲になりました。 今の範囲でも、他のデータベースに比べて十分大きいのですけれど。内部的には「2000/01/01 00:00:00」からの経過秒数を持っています。 >[SNIP[]]全期間にわたってグレゴリオ暦として扱われます。 ** [TIME[0001-01-01]]から[TIME[9999-12-31]] [FIG(quote)[ [FIGCAPTION[ [32] [CITE@en[datetime2 (Transact-SQL)]] ([TIME[2017-03-10 18:54:29 +09:00]]) ]FIGCAPTION] > 0001-01-01 through 9999-12-31 ]FIG] [67] >>34 >Microsoft SQL Serverのdatetime2型やDb2のtimestamp型など、現在の多くのデータベースでは、 0001/01/01 00:00:00 ~ 9999/12/31 23:59:59 の範囲を扱うことができる日付時刻型が用意されています。 >[SNIP[]]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 の範囲を扱えるようになっています。これらはいずれも、全期間にわたってグレゴリオ暦として扱われます。 ** [TIME[0100-01-01]]から[TIME[9999-12-31]] [68] >>34 > Microsoft Accessの日付/時刻型は、 100/01/01 00:00:00~9999/12/31 23:59:59 の範囲です。 Accessの場合、内部数値の「0」は「1899/12/30」を表します。 >[SNIP[]] > Microsoft Accessでは、全期間にわたってグレゴリオ暦として扱われます。 ;; [69] 内部表現は [[Lotus 1-2-3暦]]、表示は[[先発グレゴリオ暦]] ** [TIME[1000-01-01]]から[TIME[9999-12-31]] [65] >>34 >MySQLのdatetime型が扱える範囲は、 1000/01/01 00:00:00 ~ 9999/12/31 23:59:59 です。 > この開始日時は「サポートしている範囲」なので、それ以前の日付も入力できます。 MySQLでは、全期間にわたってグレゴリオ暦として扱われます。 [66] 「サポート」とは??? ** [TIME[1400-01-01]]から[TIME[9999-12-31]] [47] [CITE@en-us[SQL Differences Between Impala and Hive | 5.6.x | Cloudera Documentation]], [TIME[2020-05-28T12:43:35.000Z]], [TIME[2021-01-16T08:14:17.494Z]] >The Impala [CODE[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. ** [TIME[1582-10-15]]から[TIME[9999-12-31]] [48] [[イタリア]]における[[グレゴリオ改暦]]以来 [FIG(quote)[ [FIGCAPTION[ [30] [CITE@en[date (Transact-SQL)]] ([TIME[2017-03-10 18:52:14 +09:00]]) ]FIGCAPTION] > For Informatica, the range is limited to 1582-10-15 (October 15, 1582 CE) to 9999-12-31 (December 31, 9999 CE). ]FIG] [FIG(quote)[ [FIGCAPTION[ [31] [CITE@en[datetime (Transact-SQL)]] ([TIME[2017-03-10 18:53:05 +09:00]]) ]FIGCAPTION] > January 1, 1753, through December 31, 9999 ]FIG] ** [TIME[1753-01-01]]から [58] [[英国]]における[[グレゴリオ改暦]] [FIG(quote)[ [FIGCAPTION[ [34] [CITE@ja[グレゴリオ暦?ユリウス暦? データベースによって異なる、日付時刻型が扱える範囲 | ユニリタブログ]] ([TIME[2017-03-10 18:24:03 +09:00]]) ]FIGCAPTION] > 大英帝国では、1752年の9月にユリウス暦からグレゴリオ暦に改暦しています。そのため、1752年9月には19日しか存在しません。改暦を跨いで対応できるようにすると大変なので、Sybase SQL Serverでは、グレゴリオ暦になってからの日付を扱うように、改暦の翌年である1753年を日付時刻型が扱える範囲の開始年としました。そして、それをMicrosoft SQL Serverも引き継いでいる、ということです。 ]FIG] [80] [CITE@ja[Oracle TimesTen In-Memory Database SQLリファレンス・ガイド]], [TIME[2019-11-25T16:40:46.000Z]], [TIME[2022-04-22T09:50:07.251Z]] > 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] [CITE@ja[日付]], [[JasmineSoft]], [TIME[2022-04-21T04:38:01.000Z]], [TIME[2022-04-22T09:47:50.392Z]] > 年は1753年〜9999年の範囲で指定できます。 ;; [79] [[SQL]] には対応していない製品。 [83] [CITE@ja[診療報酬明細作成プログラム: [[suchowan's blog]]]], 2013年06月13日, [TIME[2022-04-22T09:54:41.000Z]] >プログラムの都合上初診の日付が必要なので、プログラムが仮につけた 日付が、1753/01/01 - そう「イギリスおよびその植民地」でユリウス暦 からグレゴリオ暦への改暦が行われた翌年の初日なのでした。 ** [TIME[1900-01-01]]から[TIME[2079-06-06]] [FIG(quote)[ [FIGCAPTION[ [33] [CITE@en[smalldatetime (Transact-SQL)]] ([TIME[2017-03-10 18:56:22 +09:00]]) ]FIGCAPTION] > 1900-01-01 through 2079-06-06 ]FIG] [57] [CITE@ja-jp[SqlDateTime 構造体 (System.Data.SqlTypes) | Microsoft Docs]], [[dotnet-bot]], [TIME[2022-04-22T09:08:49.000Z]] > SqlDateTime では、00:00:00 AM 1/1/1900 を起点とした相対的な差が格納されます。 つまり、"00:00:00 AM 1/1/1900" を整数に変換すると、0 が返されます。 ** [TIME[1970-01-01]]から[TIME[2038-01-19]] [60] 32ビット[[Unix time]] (いわゆる[[2038年問題]]) [59] >>34 >一昔前のデータベースでは、 >1970/01/01 00:00:00 ~ 2038/01/19 03:14:07 >など、期間の範囲しか扱えませんでした。 * 0値 [3] 特別な値として、[[日付]] [DFN[[CODE[0000-00-00]]]] や[[時刻]] [DFN[[CODE[00:00:00]]]]、[[日時]] [DFN[[CODE[0000-00-00 00:00:00]]]]、[[年]] [DFN[[CODE[0000]]]] が使われています。 [4] [CODE[2009-'''00-00''']] のように[[月日]]を [N[0]] としたり、 [CODE[2009-01-'''00''']] のように[[日]]を [N[0]] としたりもできます [SRC[>>2]]。 * 年 [5] 69年までを2000年代、70年からを1900年代として扱います。 ;; [[2桁西暦年号の解釈]]参照。 [40] [[MySQL]] では[[紀元前]]は扱えません。 [41] [[PostgreSQL]] と [[Oracle]] では[[紀元前]]の表現方法が異なります。 ;; [[紀元前]]参照。 ** 2桁年号 [55] [[2桁年号]]の扱い - [53] >>51 - [54] [CITE@ja-jp[two digit year cutoff サーバー構成オプションの構成 - SQL Server | Microsoft Docs]], [[rwestMSFT]], [TIME[2022-04-22T09:02:14.000Z]] ** 0年とそれ以前 [64] >>34 >>Oracle Databaseでは、ユリウス日の計算に天文学方式を使用しています。この方式では、紀元前4713年は-4712として計算されます。これに対し、歴史学方式では、紀元前4713年は-4713として計算されます。Oracleのユリウス日を、歴史学方式で計算した値と比較する場合には、紀元前の日付に365日の違いがあることに注意してください。 ( https://docs.oracle.com/cd/E57425_01/121/SQLRF/sql_elements001.htm ) >[SNIP[]] >Oracle Databaseのマニュアルでは、この「-1年」を「西暦前1年」と記載しています。なので、説明がややこしいことになっているのです。 >[SNIP[]] >…なのですが、西暦紀元前1年のデータを入れようと考えて、「0000/01/01」(西暦に0)を指定するとエラーになります… >[SNIP[]]ここからも、西暦0年が存在するようなユリウス通日が返ってきます。でも、「西暦0年」は扱えないのです。 [71] [[天文学的紀年法]]なのに「西暦前」というのはおかしい。 [[Oracle]] の誤訳か。訳者が勝手に変えるとも思えないから原文がおかしいのか。 実装がいかれてるならドキュメントの部署の誤記ではなく製品仕様レベルで間違ってるのか? * 暦 [1] [[MySQL]] と [[PostgreSQL]] は、[[先発グレゴリオ暦]]を採用しています。 [SEE[ [[先発グレゴリオ暦]] ]] [39] [[Oracle]] は、[[イタリア]]における[[グレゴリオ改暦]]より前は、 [[先発ユリウス暦]]を採用しています。 (イタリア式[[ユリウスグレゴリオ暦]]) [SRC[>>61]] [REFS[ [FIG(quote)[ [FIGCAPTION[ [44] [CITE[単位の歴史]] ([TIME[2017-01-05 22:28:14 +09:00]]) ]FIGCAPTION] > 標準SQLでは、「"日付時刻リテラル"定義の中で、"日付時刻の値"はグレゴリオ暦に従った日付と時間の自然法則に則る」と明記されています。 PostgreSQLは標準SQLの指針に従い、グレゴリオ暦が使われる以前の年に対してもグレゴリオ暦で日付を数えます。 この規則は先発グレゴリオ暦として知られています。 ]FIG] [FIG(quote)[ [FIGCAPTION[ [45] [CITE@en[MySQL :: MySQL 8.0 Reference Manual :: 13.8 What Calendar Is Used By MySQL?]] ([TIME[2017-03-10 16:38:11 +09:00]]) ]FIGCAPTION] > 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. ]FIG] ]REFS] * 秒の小数部 [50] [[秒の小数部]]と[[日時]]の精度 [51] [CITE@ja-jp[datetime (Transact-SQL) - SQL Server | Microsoft Docs]], [[MikeRayMSFT]], [TIME[2022-04-22T08:59:21.000Z]] [73] >>72 >>82 * 時間帯 [75] [[時間帯記述]]の方法と利用する[[時間帯]]の[[データベース]] -*-*- [9] [[MySQL]] は [[tzdata]] を使っています [SRC[>>7, >>8]]。 [10] [[tzdata]] の主たる形式の他、[[閏秒]]オプション版も提供されています。 ;; [[閏秒]]処理の違いについては [[tzdata]] 参照。 [11] [[tzdata]] の名前の他に、 [[システム時間帯]]を表す [DFN[[CODE[SYSTEM]]]] や、 [CODE[+10:00]] や [CODE[-6:00]] のような[[時差]]の表記にも対応しています [SRC[>>8]]。 -*-*- [24] [[PostgreSQL]] は [[tzdata]] と [[POSIX時間帯]]を使っています [SRC[>>23, >>25, >>26]]。 -*-*- [37] [[Oracle]] は[[tzdata]]を使っています [SRC[>>36, >>72]]。 [74] >>72 >+|- hh:miの形式(-12:59から+14:00までの値を使用)で1つのエンティティとして解釈されます。 -*-*- [17] [[SQL]] では[[時間帯を持たない日時]]の[[データ型]]が使われることも多いです。 [[SQL]] 内での[[時間帯]]を持つ[[データ型]]との変換時や、 [[SQL]] を使う[[プログラミング言語]]等での[[時間帯]]を持つ[[データ型]]との変換時に、 取り扱いに注意が必要となります。 [18] [[時間帯を持たない日時]]の[[データ型]]と [[Unix time]] との相互変換の手段が提供されていることがありますが、その場合の [[Unix time]] は本来の [[UTC]] 基準の値ではなく、[[地方時]]基準の値になります。 * 日数と秒数 [13] [[MySQL]] の [CODE[TO_DAYS]] は[[日数]]、 [CODE[TO_SECONDS]] は[[秒数]]を返します。 [14] [TIME[0000年1月1日][0000-01-01]]の[[日数]]は [N[1]] です [SRC[>>12]]。 ;; [42] これは [[MATLAB]] の [CODE[datenum]] と同じ数え方です。 [15] [TIME[0000年1月1日][0000-01-01]]の[[秒数]]は [N[86400]] です [SRC[>>12]]。 これは[[正子]]を表しているようなので、その1日前からの[[秒数]]ということになります。 * 異なる時代の日時の扱い [27] [[過去の日時]]、[[将来の日時]]も参照。 * 構文 ** 日時形式 [52] >>51 [76] [CITE@ja[リテラル]], [TIME[2022-04-22T09:41:16.000Z]] [89] [CITE@en[MySQL :: MySQL 8.0 Reference Manual :: 14.7 Date and Time Functions]], [TIME[2024-10-15T03:28:35.000Z]] ** 時間長形式 [22] [[PostgreSQL]] は独自の[[時間長形式]] ([CODE[reltime]], [CODE[timestamp]] も参照。) と [[ISO 8601の時間形式]]に対応しています [SRC[>>23]]。 [77] >>76 * 日時形式の記述 [56] [CITE@ja-jp[CAST および CONVERT (Transact-SQL) - SQL Server | Microsoft Docs]], [[markingmyname]], [TIME[2022-04-22T09:05:08.000Z]] * データ型 [FIG(short list)[ [6] - [CODE[YEAR]] - [CODE[DATE]] - [CODE[DATETIME]] - [CODE[TIMESTAMP]] ]FIG] [72] [CITE@ja[データ型]], [TIME[2022-04-22T09:36:05.000Z]] ** 日の時刻のみ [81] >>80 * 関連 [86] [[SASの日時]], [[日時特殊値]] * メモ [2] [CITE@en[MySQL :: MySQL 8.0 Reference Manual :: 12.3 Date and Time Types]] ([TIME[2017-03-10 16:41:32 +09:00]]) [7] [CITE@en[MySQL :: Time zone description tables]] ([TIME[2017-03-10 16:55:11 +09:00]]) [8] [CITE@en[MySQL :: MySQL 8.0 Reference Manual :: 11.6 MySQL Server Time Zone Support]] ([TIME[2017-03-10 16:55:59 +09:00]]) [12] [CITE@en[MySQL :: MySQL 8.0 Reference Manual :: 13.7 Date and Time Functions]] ([TIME[2017-03-10 17:23:58 +09:00]]) [19] [CITE@en[PostgreSQL: Documentation: 9.6: Date/Time Types]] ([TIME[2017-03-10 17:45:06 +09:00]]) [16] [CITE@en[PostgreSQL: Documentation: 9.6: Date/Time Functions and Operators]] ([TIME[2017-03-10 17:35:44 +09:00]]) [23] [CITE@en[PostgreSQL: Documentation: 9.6: Date/Time Types]] ([TIME[2017-03-10 17:52:45 +09:00]]) [25] [CITE@en[PostgreSQL: Documentation: 9.6: pg_timezone_abbrevs]] ([TIME[2017-03-10 17:55:40 +09:00]]) [26] [CITE@en[PostgreSQL: Documentation: 9.6: Date/Time Configuration Files]] ([TIME[2017-03-10 17:55:50 +09:00]]) [FIG(quote)[ [FIGCAPTION[ [20] [CITE@en[PostgreSQL: Documentation: 9.6: Date/Time Types]] ([TIME[2017-03-10 17:47:48 +09:00]]) ]FIGCAPTION] > 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 ]FIG] [FIG(quote)[ [FIGCAPTION[ [21] [CITE@en[PostgreSQL: Documentation: 9.6: Date/Time Types]] ([TIME[2017-03-10 17:48:18 +09:00]]) ]FIGCAPTION] > 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 ]FIG] [29] [CITE@ja[日付/時刻型]] ([TIME[2017-03-10 18:50:07 +09:00]]) [FIG(quote)[ [FIGCAPTION[ [35] [CITE@en[PostgreSQL: Documentation: 9.2: Date/Time Types]] ([TIME[2017-03-10 19:30:22 +09:00]]) ]FIGCAPTION] > 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. ]FIG] [36] [CITE@ja[データ型]] ([TIME[2017-02-24 10:24:15 +09:00]]) [FIG(quote)[ [FIGCAPTION[ [38] [CITE[質問!ITmedia - Oracle11gのDATE型]] ([TIME[2017-03-10 19:59:40 +09:00]]) ]FIGCAPTION] > Oracle はユリウス日 (紀元前4713年1月1日) からの経過日数+秒で日付データ > を管理しています。 > 紀元前4713年1月1日から2009年3月15日の経過日数は、2454906日です。 > こでに、11:42 は (午前とすると) 経過秒は42120秒となります。 > 1日は、86400 秒なので0.4875 となり、2454906.4875 として格納されます。 ]FIG] [43] [CITE@ja-jp[datetime (Transact-SQL) - SQL Server | Microsoft Docs]] ([[MikeRayMSFT]]著, [TIME[2019-05-11 12:59:58 +09:00]]) [46] [CITE@ja[2.5.4 日時値関数]] ([TIME[2019-09-12 11:24:39 +09:00]]) [84] [CITE[日付/時刻キーワード]], [TIME[2022-07-13T08:37:05.000Z]], [TIME[2010-12-29T13:54:24.083Z]] [85] [CITE[dBASE .DBF File Structure]], [TIME[2004-10-14T04:00:00.000Z]], [TIME[2023-11-25T06:36:02.578Z]] >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] [CITE@ja[Data types | BigQuery | Google Cloud]], [TIME[2024-03-05T22:25:11.000Z]], [TIME[2024-03-06T04:01:31.001Z]] [88] [CITE@ja[BigQuery に日時データをロードする場合のタイムゾーンとフォーマットについて確認してみた | DevelopersIO]], [[みかみ]], [TIME[2024-03-06T04:02:05.000Z]]