mysql の日付にはどのような型が使用されますか?

YEAR 型

YEAR 型は年を表すために使用されるシングルバイト型で、格納に必要なデータは 1 バイトのみです。 YEAR は、次のようなさまざまな形式で指定できます。

• YEAR は、「1901」から「2155」までの 4 桁の文字列または 4 桁の数値形式で表されます。入力形式は「YYYY」またはYYYYです。たとえば、「2010」または2010と入力すると、データベースに挿入される値は2010になります。

• YEAR '00' ～ '99' の範囲の 2 桁の文字列で表されます。 「00」から「69」、「70」から「99」の範囲の値は、それぞれ 2000 から 2069、1970 から 1999 の範囲の YEAR 値に変換されます。 「0」は「00」と同じ効果があります。範囲外の値を入力した場合は2000に変換されます。

• YEAR 1 ～ 99 の 2 桁の数値で表されます。1 ～ 99 および 70 ～ 99 の範囲の値は、2001 年の範囲の YEAR に変換されます。それぞれ 2069 年までと 1970 年から 1999 年までの値。ここで、値 0 は 2000 ではなく 0000 に変換されることに注意してください。

ヒント: 2 桁の整数の範囲は、2 桁の文字列の範囲とは若干異なります。たとえば、3000 年を挿入する場合、読者は YEAR を表す数値形式 0 を使用する可能性がありますが、実際には、データベースに挿入される値は予想される 3000 ではなく 0000 です。文字列形式の「0」または「00」のみが 3000 として正しく解釈され、不正な YEAR 値は 0000 に変換されます。

TIME 型

TIME 型は、時間情報のみを必要とし、格納に 3 バイトを必要とする値に使用されます。形式は HH:MM:SS です。 HH は時間を表し、MM は分を表し、SS は秒を表します。

TIME 型の値の範囲は -838:59:59～838:59:59 です。時の部分が大きいのは、TIME 型は時刻を表すだけではないためです。 1 日 (24 時間未満である必要があります)、イベントからの時間、または 2 つのイベントの間の時間 (24 時間を超えることも、負の値になる場合もあります) であることもあります。

TIME 値は、以下に示すように、さまざまな形式を使用して指定できます。

• 'D HH:MM:SS' 形式の文字列。 「HH:MM:SS」、「HH:MM」、「D HH」、「SS」などの「非厳密」構文も使用できます。ここでの D は日を表し、0 ～ 34 の値を取ることができます。データベースに挿入するときに、D は時間に変換され、「D*24 HH」形式で保存されます。

• 'HHMMSS' 形式、区切り文字のない文字列、または意味のある時刻を想定した HHMMSS 形式の数値。たとえば、「101112」は「10:11:12」として理解されますが、「106112」は無効であり（意味のない分の部分があります）、保存されると 00:00:00 になります。

ヒント: TIME 列に短縮値を割り当てる場合は、次の点に注意してください: コロンがない場合、MySQL は値を解釈するときに右端の 2 桁が秒を表すものとみなします。 。 (MySQL は TIME 値を現在時刻ではなく過去の時刻として解釈します)。たとえば、読者は「1112」と 1112 が 11:12:00 (つまり、11 時から 12 分後) を意味すると考えるかもしれませんが、MySQL はそれらを 00:11:12 (つまり、11 分 12 秒) として解釈します。 ）。同様に、「12」と 12 は 00:00:12 として解釈されます。逆に、TIME 値にコロンが使用されている場合は、その日の時刻とみなされます。つまり、「11:12」は 00:11:12 ではなく 11:12:00 を意味します。

#例

CREATE TABLE tests (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    start_at TIME,
    end_at TIME
);

INSERT INTO tests(name,start_at,end_at)
VALUES('Test 1', '08:00:00','10:00:00');

SELECT 
    name, start_at, end_at
FROM
    tests;

DATE 型

• 「YYYY-MM-DD」または「YYYYMMDD」文字の形式で表される日付。値の範囲は「1000-01-01」～「9999」です。 - 12-3'。たとえば、「2015-12-31」または「20151231」と入力すると、データベースに挿入される日付は 2015-12-31 になります。

• 以 'YY-MM-DD' 或者 'YYMMDD' 字符串格式表示日期，在这里YY表示两位的年值。MySQL 解释两位年值的规则：'00～69' 范围的年值转换为 '2000~2069'，'70~99' 范围的年值转换为 '1970～1999'。例如，输入 '15-12-31'，插入数据库的日期为 2015-12-31；输入 '991231'，插入数据库的日期为 1999-12-31。

• 以 YYMMDD 数字格式表示的日期，与前面相似，00~69 范围的年值转换为 2000～2069，80～99 范围的年值转换为 1980～1999。例如，输入 151231，插入数据库的日期为 2015-12-31，输入 991231，插入数据库的日期为 1999-12-31。

• 使用 CURRENT_DATE 或者 NOW()，插入当前系统日期。

示例

我们来看看下面的例子。

首先，创建一个新表名为people，其中出生日期列为DATE数据类型。

CREATE TABLE people (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birth_date DATE NOT NULL
);

接下来，在people表中插入一行。

INSERT INTO people(first_name,last_name,birth_date)
VALUES('John','Doe','1990-09-01');

然后，查询数据来自people表。

SELECT 
    first_name, 
    last_name, 
    birth_date
FROM
    people;

之后，使用两位数年份格式将数据插入people表中。

INSERT INTO people(first_name,last_name,birth_date)
VALUES('Jack','Daniel','01-09-01'),
      ('Lily','Bush','80-09-01');

在第一行中，我们使用01（范围00-69）作为年份，因此MySQL将其转换为2001年。在第二行中，我们使用80（范围70-99）作为年份，MySQL将其转换为1980年。

最后，我们可以从people表中查询数据，以检查数据是否已根据转换规则进行转换。

SELECT 
    first_name, 
    last_name, 
    birth_date
FROM
    people;

DATETIME 类型

DATETIME 类型用于需要同时包含日期和时间信息的值，在存储时需要 8 个字节。日期格式为 'YYYY-MM-DD HH:MM:SS'，其中 YYYY 表示年，MM 表示月，DD 表示日，HH 表示小时，MM 表示分钟，SS 表示秒。

在给 DATETIME 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATETIME 的日期格式即可，如下所示。

• 以 'YYYY-MM-DD HH：MM：SS' 或者 'YYYYMMDDHHMMSS' 字符串格式表示的日期，取值范围为 '1000-01-01 00：00：00'～'9999-12-3 23：59：59'。例如，输入 '2014-12-31 05：05：05' 或者 '20141231050505’，插入数据库的 DATETIME 值都为 2014-12-31 05：05：05。

• 以 'YY-MM-DD HH：MM：SS' 或者 'YYMMDDHHMMSS' 字符串格式表示的日期，在这里 YY 表示两位的年值。与前面相同，'00～79' 范围的年值转换为 '2000～2079'，'80～99' 范围的年值转换为 '1980～1999'。例如，输入 '14-12-31 05：05：05'，插入数据库的 DATETIME 为 2014-12-31 05：05：05；输入 141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。

• 以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。例如，输入 20141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05；输入 140505050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。

DATETIME值可以包括具有YYYY-MM-DD HH:MM:SS[.fraction]例如的格式的尾随小数秒到微秒 2015-12-20 10:01:00.999999。包含小数秒精度时，DATETIME值需要更多存储空间，如下表所示：

例如，2015-12-20 10:01:00.999999 需要8个字节，5个字节用于2015-12-20 10:01:00 ，3个字节用于.999999 而2015-12-20 10:01:00.9 只需要6个字节，1个字节用于小数秒精度。

TIMESTAMP 类型

TIMESTAMP 的显示格式与 DATETIME 相同，显示宽度固定在 19 个字符，日期格式为 YYYY-MM-DD HH：MM：SS，在存储时需要 4 个字节。但是 TIMESTAMP 列的取值范围小于 DATETIME 的取值范围，为 '1970-01-01 00：00：01'UTC～'2038-01-19 03：14：07'UTC。在插入数据时，要保证在合法的取值范围内。

提示：协调世界时（英：Coordinated Universal Time，法：Temps Universel Coordonné）又称为世界统一时间、世界标准时间、国际协调时间。英文（CUT）和法文（TUC）的缩写不同，作为妥协，简称 UTC。

TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外，还有一个最大的区别是：

• DATETIME 在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；

• 而 TIMESTAMP 值的存储是以 UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。

提示：如果为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值，结果值的时间部分被设置为 '00：00：00'，因此 DATE 值未包含时间信息。如果为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值，结果值的时间部分被删除，因此DATE 值未包含时间信息。

MySQL TIMESTAMP以UTC值存储。但是，MySQL将DATETIME值存储为没有时区的值。我们来看下面的例子。

首先，将当前连接的时区设置为+00:00。

SET time_zone = '+00:00';

接下来，创建一个表命名timestamp_n_datetime是由两列组成：ts与dt用TIMESTAMP和DATETIME使用下面的语句类型。

CREATE TABLE timestamp_n_datetime (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ts TIMESTAMP,
    dt DATETIME
);

然后，将当前日期和时间插入 timestamp_n_datetime 表的两个ts和每dt列 ，

INSERT INTO timestamp_n_datetime(ts,dt)
VALUES(NOW(),NOW());

之后，从timestamp_n_datetime表中查询数据。

SELECT 
    ts, 
    dt
FROM
    timestamp_n_datetime;

DATETIME和TIMESTAMP列中的值都相同。

最后，将连接的时区设置为+03:00并再次从timestamp_n_datetime表中查询数据。

SET time_zone = '+03:00';
 
SELECT 
    ts, 
    dt
FROM
    timestamp_n_datetime;

如您所见，TIMESTAMP列中的值不同。这是因为当我们更改时区时，TIMESTAMP列以UTC格式存储日期和时间值，根据新时区调整列的值。

这意味着如果您使用TIMESTAMP数据存储日期和时间值，则在将数据库移动到位于不同时区的服务器时应认真考虑。

【相关推荐：mysql视频教程】