What type is used for mysql dates?

There are 5 types: 1. YEAR, used to represent the year, the format is "YYYY"; 2. TIME, used for values ​​that only require time information, the format is "HH:MM:SS"; 3 , DATE, used when only date values ​​are needed, the format is "YYYY-MM-DD"; 4. DATETIME, used for values ​​that need to contain both date and time information, the format is "YYYY-MM-DD HH:MM:SS" ;5. TIMESTAMP, similar to DATETIME, is used for values ​​that need to contain both date and time information.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

There are multiple data types representing dates in MySQL: YEAR, TIME, DATE, DTAETIME, TIMESTAMP.

YEAR type

The YEAR type is a single-byte type used to represent the year and requires only 1 byte for storage. YEAR can be specified in various formats, as follows:

• YEAR expressed in a 4-digit string or 4-digit number format, ranging from '1901' to '2155'. The input format is 'YYYY' or YYYY. For example, if you enter '2010' or 2010, the values ​​inserted into the database will be 2010.

• YEAR expressed as a 2-digit string in the range '00' to '99'. Values ​​in the ranges of '00' to '69' and '70' to '99' are converted to YEAR values ​​in the ranges of 2000 to 2069 and 1970 to 1999 respectively. '0' has the same effect as '00'. Values ​​inserted outside the range will be converted to 2000.

• YEAR expressed as a 2-digit number, ranging from 1 to 99. Values ​​in the ranges of 1 to 99 and 70 to 99 are converted to YEAR in the range of 2001 to 2069 and 1970 to 1999 respectively. value. Note that here a value of 0 will be converted to 0000, not 2000.

Tip: The two-digit integer range is slightly different from the two-digit string range. For example, to insert the year 3000, a reader might use the numeric format 0 to represent YEAR, but in fact, the value inserted into the database is 0000, not 3000 as expected. Only '0' or '00' in string format can be correctly interpreted as 3000, illegal YEAR values ​​will be converted to 0000.

TIME type

TIME type is used for values ​​that only require time information and requires 3 bytes for storage. The format is HH:MM:SS. HH represents hours, MM represents minutes, and SS represents seconds.

The value range of the TIME type is -838:59:59～838:59:59. The reason why the hour part is so large is that the TIME type can not only be used to represent the time of a day (must be less than 24 hours), It could also be the time since an event or the time between two events (which can be greater than 24 hours, or even negative).

TIME values ​​can be specified using various formats, as shown below.

• 'D HH:MM:SS' format string. You can also use these "non-strict" syntaxes: 'HH:MM:SS', 'HH:MM', 'D HH' or 'SS'. D here represents the day, which can take a value between 0 and 34. When inserting into the database, D is converted to hours and saved in the format "D*24 HH".

• 'HHMMSS' format, a string without separators, or a numeric value in HHMMSS format, assuming a meaningful time. For example, '101112' is understood as '10:11:12', but '106112' is not legal (it has a meaningless minutes part) and will become 00:00:00 when stored.

Tip: When assigning abbreviated values ​​to the TIME column, please note: If there is no colon, MySQL assumes that the rightmost two digits represent seconds when interpreting the value. (MySQL interprets TIME values ​​as past time rather than current time). For example, a reader might think '1112' and 1112 mean 11:12:00 (that is, 12 minutes after 11 o'clock), but MySQL interprets them as 00:11:12 (that is, 11 minutes and 12 seconds). Likewise '12' and 12 are interpreted as 00:00:12. On the contrary, if a colon is used in the TIME value, it is definitely regarded as the time of the day, that is, '11:12' means 11:12:00, not 00:11:12.

Example

Let us see an example of using data type in TIME table.

First, create a new table named tests with four columns: id, name, start_at, and end_at. The data type of the start_at and end_at columns is TIME.

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

Secondly, insert a row in the tests table.

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

Third, query data from the tests table.

SELECT 
    name, start_at, end_at
FROM
    tests;

DATE type

DATE type is used when only a date value is required, without the time part, 3 bytes are required for storage. The date format is 'YYYY-MM-DD', where YYYY represents the year, MM represents the month, and DD represents the day.

When assigning values ​​to DATE type fields, you can use string type or numeric type data to insert, as long as it conforms to the date format of DATE. As shown below:

• The date expressed in the format of 'YYYY-MM-DD' or 'YYYYMMDD' characters, the value range is '1000-01-01'～'9999- 12-3'. For example, enter '2015-12-31' or '20151231', and the date inserted into the database will be 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视频教程】

The above is the detailed content of What type is used for mysql dates?. For more information, please follow other related articles on the PHP Chinese website!