MySQL 5.6 リファレンスマニュアル  /  ...  /  DATE、DATETIME、および TIMESTAMP 型

11.3.1 DATE、DATETIME、および TIMESTAMP 型

DATEDATETIME、および TIMESTAMP 型は関連しています。このセクションでは、これらの特徴、似ている点、および異なる点について説明します。MySQL は、セクション9.1.3「日付リテラルと時間リテラル」で説明している複数の形式で、DATEDATETIME、および TIMESTAMP 値を認識します。DATE および DATETIME 範囲の説明では、サポートされているとは、以前の値は機能するが、保証はないということを意味します。

DATE 型は、日付部分を含むが時間部分は含まない値に使用されます。MySQL では、DATE 値の取得と表示は 'YYYY-MM-DD' 形式で行われます。サポートしている範囲は '1000-01-01' から '9999-12-31' です。

DATETIME 型は、日付と時間の両方の部分を含む値に使用されます。MySQL では、DATETIME 値の取得と表示は 'YYYY-MM-DD HH:MM:SS' 形式で行われます。サポートしている範囲は '1000-01-01 00:00:00' から '9999-12-31 23:59:59' です。

TIMESTAMP データ型は、日付と時間の両方の部分を含む値に使用されます。TIMESTAMP には、'1970-01-01 00:00:01' UTC から '2038-01-19 03:14:07' UTC の範囲があります。

DATETIME または TIMESTAMP 値には、マイクロ秒 (6 桁) までの精度で後続の小数秒部分を含めることができます。特に、MySQL 5.6.4 以降では、DATETIME または TIMESTAMP カラムに挿入された値の小数部はすべて破棄されずに格納されます。小数部が含まれる場合、これらの値の形式は 'YYYY-MM-DD HH:MM:SS[.fraction]' であり、DATETIME 値の範囲は '1000-01-01 00:00:00.000000' から '9999-12-31 23:59:59.999999' であり、TIMESTAMP 値の範囲は '1970-01-01 00:00:01.000000' から '2038-01-19 03:14:07.999999' です。小数部は、常に時間の残りの部分から小数点で区分する必要があります。これ以外の小数秒区切り文字は認識されません。MySQL の小数秒のサポートの詳細は、セクション11.3.6「時間値での小数秒」を参照してください。

TIMESTAMP および (MySQL 5.6.5 以降の) DATETIME データ型では、自動初期化と現在の日付および時間への自動更新機能が用意されています。詳細は、セクション11.3.5「TIMESTAMP および DATETIME の自動初期化および更新機能」を参照してください。

MySQL は、TIMESTAMP 値を、ストレージでは現在のタイムゾーンを UTC に変換し、取得では UTC から現在のタイムゾーンに戻します。(DATETIME などのほかの型ではこれは行われません。)デフォルトでは、接続ごとの現在のタイムゾーンはサーバーの時間です。タイムゾーンは接続ごとに設定できます。タイムゾーン設定が一定であるかぎり、格納した値と同じ値に戻すことができます。TIMESTAMP 値を格納したあとで、タイムゾーンを変更して値を取り出すと、取り出された値は格納した値とは異なります。これは、同じタイムゾーンが両方向への変換に使用されなかったために起こります。現在のタイムゾーンは、time_zone システム変数の値として使用できます。詳細は、セクション10.6「MySQL Server でのタイムゾーンのサポート」を参照してください。

無効な DATEDATETIME、または TIMESTAMP 値は、適切な型のゼロ値 ('0000-00-00' または '0000-00-00 00:00:00') に変換されます。

MySQL では日付値解釈の特定のプロパティーに注意してください。

  • MySQL は、文字列として指定された値に、緩やかな形式を使用でき、この形式では、どの句読点文字でも日付部分と時間部分の区切り文字として使用できます。場合によっては、この構文は偽りになることがあります。たとえば、'10:11:12' などの値は、:区切り文字のために時間値のように見えることがありますが、日付のコンテキストで使用された場合は、'2010-11-12' の年と解釈されます。値 '10:45:15' は、'45' が有効な月ではないので、'0000-00-00' に変換されます。

    日付および時間の部分と小数秒部分との間の区切り文字として認識される唯一の文字が小数点です。

  • サーバーは、月と日の値が、それぞれが 1 から 12 と 1 から 31 の範囲内にあるだけではなく、有効である必要があります。厳密モードが無効になっていると、'2004-04-31' のような無効な日付は '0000-00-00' に変換され、警告メッセージが表示されます。厳密モードが有効なときは、無効な日付によってエラーが発生します。このような日付を許可するには、ALLOW_INVALID_DATES を有効にします。詳細は、セクション5.1.7「サーバー SQL モード」を参照してください。

  • MySQL は、日または月カラムにゼロを含んだ TIMESTAMP 値や、無効な日付の値を受け入れません。このルールに対する唯一の例外は、特殊なゼロ値である '0000-00-00 00:00:00' です。

  • MySQL 5.6.4 より前では、テーブルから選択しない場合、CAST()TIMESTAMP 値を文字列として扱います。(これは、FROM DUAL を指定した場合にも当てはまります。)セクション12.10「キャスト関数と演算子」を参照してください。

  • 2 桁の年を含む日付の値は、世紀が不明なためあいまいです。MySQL は次のルールを使用して 2 桁の年の値を解釈します。

    • 00-69 の範囲の値は 2000-2069 に変換されます。

    • 70-99 の範囲の値は 1970-1999 に変換されます。

    セクション11.3.8「日付での 2 桁の年」も参照してください。

注記

MySQL Server は、MAXDB SQL モードを有効にして実行できます。この場合、TIMESTAMPDATETIME と同じです。テーブル作成時にこのモードが有効になっている場合、TIMESTAMP カラムは DATETIME カラムとして作成されます。この結果、このようなカラムは DATETIME 表示形式を使用し、同じ範囲の値を持ち、自動初期化機能や、現在の日付と時間に自動的に更新する機能はありません。セクション5.1.7「サーバー SQL モード」を参照してください。


User Comments
  Posted by Linus Rachlis on November 12, 2009
You can automatically convert an invalid date to the valid equivalent like this:

SELECT '2008-02-31' + INTERVAL 0 DAY;
# 2008-03-02

This lets you avoid storing invalid dates without bothering the user and without having to manually program a fix into your app. I suppose in >=5.0.2, you'd have to enable ALLOW_INVALID_DATES to avoid having it converted to '0000-00-00'. This also works with DATETIMEs.
  Posted by Kadir Güngör on June 12, 2009
In addition to Linus Rachlis,
if you add 0 month to an invalid date, you could get last day of the month, for example

SELECT '2008-02-31' + INTERVAL 0 MONTH;
# 2008-02-29

It works as

SELECT LAST_DAY('2008-02-31');
# 2008-02-29
  Posted by yaswanth reddy on October 22, 2011
even we can add a trigger to check a valid date so that it atomatically changes to correct date
  Posted by DBA Lead on December 13, 2011
A possible problem that can happen with date_format and ORDER/GROUP BY is explained at http://www.geeksww.com/tutorials/database_management_systems/mysql/tips_and_tricks/mysql_dateformat_function_and_order_by.php
  Posted by Brian Layman on April 2, 2014
Please note that year restriction of 1000-9999 appears to be in place in versions 5.0.2+. The wording of this documentation doesn't make it clear whether or not this year restriction remained in place.

The ODBC driver at least seems to enforce the restriction by bombing out when exporting a year such as 193/08/17.

The 5.0.2 change appears to add an additional validity check and not replace any existing restrictions.
  Posted by heidi heidi on March 3, 2015
STEP 1: Download the db_school database then import the database on your MySQL server.

You must have the following tables:
tbl_semester
tbl_schoolyear
tbl_students

STEP 2: Examine each table structure.

STEP 3: Create a trigger that will automatically generate the
id number (studid) of each new student. The ID number
is generated as describe below.

Examine the sample ID Number : 00015010

0001 = Number of records (since this is the first record)
5 = Month (numeric)
0 = Current Selected Semester
10 = Current Selected School Year

Assume: The tbl_students has 200 records then the next id number should be,
02015010
Note: ID number should be eight characters only.

STEP 4: To check if your trigger works..try execute the following comand.

One at a time..please...

1. INSERT INTO tbl_students VALUES('123','Taborda','Rogie','BSIT',now());
2. INSERT INTO tbl_students VALUES('341','Viloria','Angela','BSIT',now());
3. INSERT INTO tbl_students VALUES('512','Astrero','Jessa','BSIT',now());
Sign Up Login You must be logged in to post a comment.