特殊性：
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 | +------------+------------+---------------------+---------------------+------------+
PHP中的时间转换函数
字符串转时间戳 strtotime
时间戳转字符串 date
