mysql 字段 时间_MySQL字段之时间字段

时间戳记 mysql>

create table ts(f1 TIMESTAMP);

Query OK, 0 rows affected (0.13 sec)

mysql> desc date;

+-------+-----------+------+-----+-------------------+-----------------------------+

| Field |

Type | Null | Key |

Default |

Extra |

+-------+-----------+------+-----+-------------------+-----------------------------+

| f1 |

timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

+-------+-----------+------+-----+-------------------+-----------------------------+

具体范围如下

mysql> insert into

tsvalues(19700101080001);

Query OK, 1 row affected (0.20 sec)

mysql> insert into ts

values(20371231235959);

Query OK, 1 row affected (0.19 sec)

mysql> select * from ts ;

+---------------------+

|

f1 |

+---------------------+

| 1970-01-01 08:00:01 |

| 2037-12-31 23:59:59 |

+---------------------+

2 rows in set (0.00 sec)

格式

yyyymmddhhmmss、yymmddhhmmss、yyyymmdd或yymmdd格式

mysql> insert into ts

values(19700101080001);

mysql> insert into ts

values(19700102);

mysql> insert into ts

values(371231);

mysql> insert into ts

values(1231);

自动补上0

+---------------------+

|

f1 |

+---------------------+

| 1970-01-01 08:00:01 |

| 2037-12-31 23:59:59 |

| 1970-01-02 00:00:00 |

| 2037-01-02 00:00:00 |

| 2037-12-31 00:00:00 |

+---------------------+

time 类型

mysql> create table shijian(f1

time);

Query OK, 0 rows affected (0.39 sec)

mysql> desc

shijian;

范围从 -8385959 到 8385959

+-------+------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+------+------+-----+---------+-------+

| f1 | time |

YES | | NULL | |

+-------+------+------+-----+---------+-------+

mysql> insert into shijian

values(-8385959);

Query OK, 1 row affected (0.08 sec)

mysql> insert into shijian

values(8385959);

Query OK, 1 row affected (0.13 sec)

不足7位数字就从右到左赋值 没有就赋0

mysql> insert into shijian

values(159);

+------------+

|

f1 |

+------------+

| 00:01:59 |

| 838:59:59 |

| -838:59:59 |

+------------+

DATETIME类型 范围就是下面的最大最小值

mysql> desc dt;

+-------+----------+------+-----+---------+-------+

| Field |

Type | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| f1 |

datetime | YES | | NULL | |

+-------+----------+------+-----+---------+-------+

mysql> create table dt(f1

DATETIME);

mysql> insert into dt

value(10000101);

mysql> insert into dt

value(99991231);

mysql> insert into dt

values(10000101000000);

Query OK, 1 row affected (0.14 sec)

mysql> insert into dt

value(99991231235959);

Query OK, 1 row affected (0.17 sec)

+---------------------+

|

f1 |

+---------------------+

| 1000-01-01 00:00:00 |

| 9999-12-31 00:00:00 |

| 9999-12-31 00:00:00 |

| 9999-12-31 23:59:59 |

| 1000-01-01 00:00:00 |

+---------------------+

日期

mysql> create table riqi(f1

date);

Query OK, 0 rows affected (0.11 sec)

mysql> desc riqi;

+-------+------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+------+------+-----+---------+-------+

| f1 | date |

YES | | NULL | |

+-------+------+------+-----+---------+-------+

mysql> insert into riqi

values(10000101);

mysql> insert into riqi

values(99991231);

+------------+

|

f1 |

+------------+

| 1000-01-01 |

| 9999-12-31 |

+------------+

年

mysql> create table nian(f1 year);

Query OK, 0 rows affected (0.28 sec)

mysql> desc nian;

+-------+---------+------+-----+---------+-------+

| Field |

Type | Null |

Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| f1 |

year(4) | YES | | NULL | |

+-------+---------+------+-----+---------+-------+

mysql> insert into nian values(1970);

mysql> insert into nian values(2155);

可以使用2位

70以上(包括70)是2000年以前

70以下是2000以后

mysql> insert into nian values(69);

mysql> insert into nian values(70);

mysql> insert into nian values(20);

mysql> insert into nian values(80);

mysql> select * from nian;

+------+

| f1 |

+------+

| 1970 |

| 2155 |

| 1980 |

| 2020 |

| 2069 |

| 1970 |

+------+