Datetime type in column type in MySQL tutorial

This article mainly introduces you to the relevant knowledge points of the date and time type in mysql column type. I hope it will be helpful to friends in need!

Recommended reference tutorial: "mysql tutorial"

Column type (data type)type in column type in MySQL tutorial>

The so-called column type actually refers to data Type, that is, a unified classification of data, from a system perspective is to be able to manage it in a unified way and make better use of limited space.

In SQL, data types are divided into three major categories: Numeric type, string type and date and time type.

Date-time type

Date-time data, as the name suggests, is a data type used to represent date and time. There are five types in total. They are:

• datetime: Date time, its format is yyyy-MM-dd HH:mm:ss, the range represented is From Datetime type in column type in MySQL tutorial000 to 9999, there are zero values, that is, 0000-00-00 0000:00;

• date: date, that is The date part of datetime;

• time: time, or time period, is a specified Between time intervals, including positive and negative times;

• timestamp: timestamp, but it is not a real timestamp, it is from Datetime type in column type in MySQL tutorial970 is calculated starting from the year, the format is consistent with datetime;

• ##year: year, there are two formats, respectively year(2) and year(4).

Execute the following SQL statement to create a date schedule for testing:

-- 创建日期时间表create table my_date(
    dDatetime type in column type in MySQL tutorial datetime,
    d2 date,
    dDatetime type in column type in MySQL tutorial time,
    d4 timestamp,
    dDatetime type in column type in MySQL tutorial year)charset utf8;

When we insert data,

time in the date and time type can be a negative number, or even a very large negative number; year can be inserted using 2-digit data, or using 4-digit data insertion; timestamp, as long as the current record is updated, this field will be automatically updated to the current time, and the timestamp type defaults to a non-empty .

-- 插入测试数据insert into my_date values (&#Datetime type in column type in MySQL tutorial9;20Datetime type in column type in MySQL tutorial7-0Datetime type in column type in MySQL tutorial-06 Datetime type in column type in MySQL tutorialDatetime type in column type in MySQL tutorial:Datetime type in column type in MySQL tutorialDatetime type in column type in MySQL tutorial:00&#Datetime type in column type in MySQL tutorial9;,&#Datetime type in column type in MySQL tutorial9;20Datetime type in column type in MySQL tutorial7-0Datetime type in column type in MySQL tutorial-06&#Datetime type in column type in MySQL tutorial9;,&#Datetime type in column type in MySQL tutorial9;Datetime type in column type in MySQL tutorialDatetime type in column type in MySQL tutorial:Datetime type in column type in MySQL tutorialDatetime type in column type in MySQL tutorial:00&#Datetime type in column type in MySQL tutorial9;,&#Datetime type in column type in MySQL tutorial9;20Datetime type in column type in MySQL tutorial7-0Datetime type in column type in MySQL tutorial-06 Datetime type in column type in MySQL tutorialDatetime type in column type in MySQL tutorial:Datetime type in column type in MySQL tutorialDatetime type in column type in MySQL tutorial:00&#Datetime type in column type in MySQL tutorial9;,20Datetime type in column type in MySQL tutorial7);insert into my_date values (&#Datetime type in column type in MySQL tutorial9;20Datetime type in column type in MySQL tutorial7-0Datetime type in column type in MySQL tutorial-06 Datetime type in column type in MySQL tutorialDatetime type in column type in MySQL tutorial:Datetime type in column type in MySQL tutorialDatetime type in column type in MySQL tutorial:00&#Datetime type in column type in MySQL tutorial9;,&#Datetime type in column type in MySQL tutorial9;20Datetime type in column type in MySQL tutorial7-0Datetime type in column type in MySQL tutorial-06&#Datetime type in column type in MySQL tutorial9;,&#Datetime type in column type in MySQL tutorial9;-Datetime type in column type in MySQL tutorialDatetime type in column type in MySQL tutorialDatetime type in column type in MySQL tutorial:Datetime type in column type in MySQL tutorialDatetime type in column type in MySQL tutorial:00&#Datetime type in column type in MySQL tutorial9;,&#Datetime type in column type in MySQL tutorial9;20Datetime type in column type in MySQL tutorial7-0Datetime type in column type in MySQL tutorial-06 Datetime type in column type in MySQL tutorialDatetime type in column type in MySQL tutorial:Datetime type in column type in MySQL tutorialDatetime type in column type in MySQL tutorial:00&#Datetime type in column type in MySQL tutorial9;,69);insert into my_date values (&#Datetime type in column type in MySQL tutorial9;20Datetime type in column type in MySQL tutorial7-0Datetime type in column type in MySQL tutorial-06 Datetime type in column type in MySQL tutorialDatetime type in column type in MySQL tutorial:Datetime type in column type in MySQL tutorialDatetime type in column type in MySQL tutorial:00&#Datetime type in column type in MySQL tutorial9;,&#Datetime type in column type in MySQL tutorial9;20Datetime type in column type in MySQL tutorial7-0Datetime type in column type in MySQL tutorial-06&#Datetime type in column type in MySQL tutorial9;,&#Datetime type in column type in MySQL tutorial9;-2 Datetime type in column type in MySQL tutorialDatetime type in column type in MySQL tutorial:Datetime type in column type in MySQL tutorialDatetime type in column type in MySQL tutorial:00&#Datetime type in column type in MySQL tutorial9;,&#Datetime type in column type in MySQL tutorial9;20Datetime type in column type in MySQL tutorial7-0Datetime type in column type in MySQL tutorial-06 Datetime type in column type in MySQL tutorialDatetime type in column type in MySQL tutorial:Datetime type in column type in MySQL tutorialDatetime type in column type in MySQL tutorial:00&#Datetime type in column type in MySQL tutorial9;,70);

As shown in the figure above, the above three records have been inserted successfully. Next, let’s verify the timestamp type field when updating the record

d4Whether it will be updated automatically:

-- 更新记录，验证时间戳类型的字段是否会自动更新update my_date set dDatetime type in column type in MySQL tutorial = &#Datetime type in column type in MySQL tutorial9;20Datetime type in column type in MySQL tutorial7-0Datetime type in column type in MySQL tutorial-06 Datetime type in column type in MySQL tutorialDatetime type in column type in MySQL tutorial:24:00&#Datetime type in column type in MySQL tutorial9; where dDatetime type in column type in MySQL tutorial = Datetime type in column type in MySQL tutorial970;

As shown in the picture above, obviously all our conclusions have been verified.

The above is the detailed content of Datetime type in column type in MySQL tutorial. For more information, please follow other related articles on the PHP Chinese website!