时间日期类型
特殊性:
1、year 插入2位数,有如下规则
69以下为20+70以上为19+
2、timestamp可以自动更新
timestamp null default current_timestamp on update current_timestamp
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 intomy_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小时数超过24insert intomy_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 intomy_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保存时间戳