## 时间日期类型


类型 | 名称 | 存储字节数| 格式 | 表示范围 | 初始值
|- | - | - | - | - | -
Date | 日期类型 | 3字节 |  `YYYY-mm-dd` | `1000-01-01~9999-12-12` | `0000-00-00`
Time | 时间类型 | 3字节 | `HH:ii:ss` | `-838:59:59~838:59:59` | 
Datetime  |  日期时间类型 |  8字节 | `YYYY-mm-dd HH:ii:ss` | `1000-01-01 00:00:00 ~ 9999-12-12 23:59:59` |  `0000-00-00 00:00:00` | 
Timestamp |  时间戳类型 | | `YYYY-mm-dd HH:ii:ss` | 表示从格林威治时间开始
Year| 年类型 | 1字节 | `0~99`/ 四位数具体年 | `1900-2155`

特殊性：

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

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

2、timestamp可以自动更新
```sql
timestamp null default current_timestamp on update current_timestamp
```

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

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

示例

```sql
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 |
+------------+------------+---------------------+---------------------+------------+
```

PHP中的时间转换函数
- 字符串转时间戳 strtotime
- 时间戳转字符串 date

通常使用int保存时间戳