MySQL数据库（9）：数据类型-时间日期类型

时间日期类型

特殊性：

1、year 插入2位数，有如下规则

• 69以下为20+
• 70以上为19+

2、timestamp可以自动更新

timestamp null default current_timestamp on update current_timestamp

  

 

  
1
 

3、time可以存放小时超过24的值

4、时间time,可以使用简单的日期 + 空格 + 数字，会自动转换该数字成天数*24 + 后面的时间

示例

create table my_date(
    date_field date,
    time_field time,
    datetime_field datetime,
    timestamp_field timestamp,
    year_field year
);

mysql> desc my_date;
+-----------------+-----------+------+-----+---------+-------+
| Field           | Type      | Null | Key | Default | Extra |
+-----------------+-----------+------+-----+---------+-------+
| date_field      | date      | YES  |     | NULL    |       |
| time_field      | time      | YES  |     | NULL    |       |
| datetime_field  | datetime  | YES  |     | NULL    |       |
| timestamp_field | timestamp | YES  |     | NULL    |       |
| year_field      | year(4)   | YES  |     | NULL    |       |
+-----------------+-----------+------+-----+---------+-------+

-- 插入正常数据
insert into
my_date(date_field, time_field, datetime_field, timestamp_field, year_field)
values ('1970-01-01', '10:12:12', '1970-01-01 10:12:12', '1970-01-01 10:12:12', 1970);

mysql> select * from my_date;
+------------+------------+---------------------+---------------------+------------+
| date_field | time_field | datetime_field      | timestamp_field     | year_field |
+------------+------------+---------------------+---------------------+------------+
| 1970-01-01 | 10:12:12   | 1970-01-01 10:12:12 | 1970-01-01 10:12:12 |       1970 |
+------------+------------+---------------------+---------------------+------------+

-- 插入time小时数超过24
insert into
my_date(date_field, time_field, datetime_field, timestamp_field, year_field)
values ('1970-01-01', '512:12:12', '1970-01-01 10:12:12', '1970-01-01 10:12:12', 50);

mysql> select * from my_date;
+------------+------------+---------------------+---------------------+------------+
| date_field | time_field | datetime_field      | timestamp_field     | year_field |
+------------+------------+---------------------+---------------------+------------+
| 1970-01-01 | 10:12:12   | 1970-01-01 10:12:12 | 1970-01-01 10:12:12 |       1970 |
| 1970-01-01 | 512:12:12  | 1970-01-01 10:12:12 | 1970-01-01 10:12:12 |       2050 |
+------------+------------+---------------------+---------------------+------------+

-- time格式转换
insert into
my_date(date_field, time_field, datetime_field, timestamp_field, year_field)
values ('1970-01-01', '5 12:12:12', '1970-01-01 10:12:12', '1970-01-01 10:12:12', 50);

mysql> select * from my_date;
+------------+------------+---------------------+---------------------+------------+
| date_field | time_field | datetime_field      | timestamp_field     | year_field |
+------------+------------+---------------------+---------------------+------------+
| 1970-01-01 | 10:12:12   | 1970-01-01 10:12:12 | 1970-01-01 10:12:12 |       1970 |
| 1970-01-01 | 512:12:12  | 1970-01-01 10:12:12 | 1970-01-01 10:12:12 |       2050 |
| 1970-01-01 | 132:12:12  | 1970-01-01 10:12:12 | 1970-01-01 10:12:12 |       2050 |
+------------+------------+---------------------+---------------------+------------+

  

 

  
 
 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
  
20
  
21
  
22
  
23
  
24
  
25
  
26
  
27
  
28
  
29
  
30
  
31
  
32
  
33
  
34
  
35
  
36
  
37
  
38
  
39
  
40
  
41
  
42
  
43
  
44
  
45
  
46
  
47
  
48
  
49
  
50
  
51
  
52
  
53
  
54
  
55
  
56
  
57
 

PHP中的时间转换函数

• 字符串转时间戳 strtotime
• 时间戳转字符串 date

通常使用int保存时间戳

文章来源: pengshiyu.blog.csdn.net，作者：彭世瑜，版权归原作者所有，如需转载，请联系作者。

原文链接：pengshiyu.blog.csdn.net/article/details/124789534