yearmonthmysql_MySQL⽇期数据类型、时间类型使⽤总结MySQL ⽇期类型：⽇期格式、所占存储空间、⽇期范围 ⽐较。
⽇期类型 存储空间 ⽇期格式 ⽇期范围
------------ --------- --------------------- -----------------------------------------
datetime 8 bytes YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
timestamp 4 bytes YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 ~ 2038
date 3 bytes YYYY-MM-DD 1000-01-01 ~ 9999-12-31
year 1 bytes YYYY 1901 ~ 2155
在 MySQL 中创建表时，对照上⾯的表格，很容易就能选择到合适⾃⼰的数据类型。不过到底是选择 datetime 还是 timestamp，可能会有点犯难。这两个⽇期时间类型各有优点：datetime 的⽇期范围⽐较⼤；timestamp 所占存储空间⽐较⼩，只是 datetime 的⼀半。
另外，timestamp 类型的列还有个特性：默认情况下，在 insert, update 数据时，timestamp 列会⾃动以当前时间
(CURRENT_TIMESTAMP)填充/更新。“⾃动”的意思就是，你不去管它，MySQL 会替你去处理。
建表的代码为:
create table t8 (
`id1` timestamp NOT NULL default CURRENT_TIMESTAMP,
`id2` datetime default NULL
);
⼀般情况下，我倾向于使⽤ datetime ⽇期类型。
两者之间的⽐较:
1. timestamp容易所⽀持的范围⽐timedate要⼩。 并且容易出现超出的情况
2.timestamp⽐较受时区timezone的影响以及MYSQL版本和服务器的SQL MODE的影响.
MySQL 时间类型：时间格式、所占存储空间、时间范围。
时间类型 存储空间 时间格式 时间范围
------------ --------- --------------------- -----------------------------------------
time 3 bytes HH:MM:SS -838:59:59 ~ 838:59:59
time 时间范围居然有这么⼤的范围，特别是 time 可以取负值，有点奇怪。后来，看了 MySQL ⼿册才知道这是为了满⾜两个⽇期时间相减才这样设计的。
select timediff('2000:01:31 23:59:59', '2000:01:01 00:00:00'); -- 743:59:59
select timediff('2000:01:01 00:00:00', '2000:01:31 23:59:59'); -- -743:59:59
select timediff('23:59:59', '12:00:00'); -- 11:59:59
注意，timediff 的两个参数只能是 datetime/timestamp, time 类型的，并且这两个参数类型要相同。即：datetime/timestamp 和datetime/timestamp ⽐较；time 和 time 相⽐较。
虽然 MySQL 中的⽇期时间类型⽐较丰富，但遗憾的是，⽬前(2008-08-08)这些⽇期时间类型只能⽀持到秒级别，不⽀持毫秒、微秒。也没有产⽣毫秒的函数。
《MySQL：MySQL⽇期数据类型、MySQL时间类型使⽤总结》适⽤于 MySQL 5.X 及以上版本。
⼀、MySQL 获得当前⽇期时间 函数
1.1 获得当前⽇期+时间(date + time)函数：now()
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2008-08-08 22:20:46 |
+---------------------+
除了 now() 函数能获得当前的⽇期时间外，MySQL 中还有下⾯的函数：
current_timestamp()
,
current_timestamp
,localtime()
,localtime
,localtimestamp -- (v4.0.6)
,localtimestamp() -- (v4.0.6)
这些⽇期时间函数，都等同于 now()。鉴于 now() 函数简短易记，建议总是使⽤ now() 来替代上⾯列出的函数。
1.2 获得当前⽇期+时间(date + time)函数：sysdate()
sysdate() ⽇期时间函数跟 now() 类似，不同之处在于：now() 在执⾏开始时值就得到了， sysdate() 在函数执⾏时动态得到值。看下⾯的例⼦就明⽩了：
mysql> select now(), sleep(3), now();
+---------------------+----------+---------------------+
| now() | sleep(3) | now() |
+---------------------+----------+---------------------+
| 2008-08-08 22:28:21 | 0 | 2008-08-08 22:28:21 |
+---------------------+----------+---------------------+
mysql> select sysdate(), sleep(3), sysdate();
+---------------------+----------+---------------------+
| sysdate() | sleep(3) | sysdate() |
+---------------------+----------+---------------------+
| 2008-08-08 22:28:41 | 0 | 2008-08-08 22:28:44 |
+---------------------+----------+---------------------+
可以看到，虽然中途 sleep 3 秒，但 now() 函数两次的时间值是相同的； sysdate() 函数两次得到的时
间值相差 3 秒。MySQL Manual 中是这样描述 sysdate() 的：Return the time at which the function executes。
sysdate() ⽇期时间函数，⼀般情况下很少⽤到。
2. 获得当前⽇期(date)函数：curdate()
mysql创建表数据类型mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2008-08-08 |
+------------+
其中，下⾯的两个⽇期函数等同于 curdate()：
current_date()
,current_date
3. 获得当前时间(time)函数：curtime()
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 22:41:30 |
+-----------+
其中，下⾯的两个时间函数等同于 curtime()：
current_time()
,current_time
4. 获得当前 UTC ⽇期时间函数：utc_date(), utc_time(), utc_timestamp()
mysql> select utc_timestamp(), utc_date(), utc_time(), now()
+---------------------+------------+------------+---------------------+
| utc_timestamp() | utc_date() | utc_time() | now() |
+---------------------+------------+------------+---------------------+
| 2008-08-08 14:47:11 | 2008-08-08 | 14:47:11 | 2008-08-08 22:47:11 |
+---------------------+------------+------------+---------------------+
因为我国位于东⼋时区，所以本地时间 = UTC 时间 + 8 ⼩时。UTC 时间在业务涉及多个国家和地区的时候，⾮常有⽤。
五、MySQL 时间戳(Timestamp)函数
1. MySQL 获得当前时间戳函数：current_timestamp, current_timestamp()
mysql> select current_timestamp, current_timestamp();
+---------------------+---------------------+
| current_timestamp | current_timestamp() |
+---------------------+---------------------+
| 2008-08-09 23:22:24 | 2008-08-09 23:22:24 |
+---------------------+---------------------+
2. MySQL (Unix 时间戳、⽇期)转换函数：
unix_timestamp(),
unix_timestamp(date),
from_unixtime(unix_timestamp),
from_unixtime(unix_timestamp,format)
下⾯是⽰例：
select unix_timestamp(); -- 1218290027 ===得到当前时间的UNIX时间值
将具体时间来转换成timestamp
select unix_timestamp('2008-08-08'); -- 1218124800
select unix_timestamp('2008-08-08 12:30:00'); -- 1218169800
将timestamp来转换成具体时间
select from_unixtime(1218290027); -- '2008-08-09 21:53:47'
select from_unixtime(1218124800); -- '2008-08-08 00:00:00'
select from_unixtime(1218169800); -- '2008-08-08 12:30:00'
select from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x'); -- '2008 8th August 12:30:00 2008'
3. MySQL 时间戳(timestamp)转换、增、减函数：
timestamp(date) -- date to timestamp
timestamp(dt,time) -- dt + time
timestampadd(unit,interval,datetime_expr) --
timestampdiff(unit,datetime_expr1,datetime_expr2) --
请看⽰例部分：
select timestamp('2008-08-08'); -- 2008-08-08 00:00:00
select timestamp('2008-08-08 08:00:00', '01:01:01'); -- 2008-08-08 09:01:01
select timestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-18 09:01:01
select timestampadd(day, 1, '2008-08-08 08:00:00'); -- 2008-08-09 08:00:00
select date_add('2008-08-08 08:00:00', interval 1 day); -- 2008-08-09 08:00:00
MySQL timestampadd() 函数类似于 date_add()。
select timestampdiff(year,'2002-05-01','2001-01-01'); -- -1
select timestampdiff(day ,'2002-05-01','2001-01-01'); -- -485
select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); -- -12
select datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00'); -- 7
MySQL timestampdiff() 函数就⽐ datediff() 功能强多了，datediff() 只能计算两个⽇期(date)之间相差的天数。
====================================================================================⼆、MySQL ⽇期时间 Extract(选取) 函数。
1. 选取⽇期时间的各个部分：⽇期、时间、年、季度、⽉、⽇、⼩时、分钟、秒、微秒set @dt = '2008-09-10 07:15:30.123456';
select date(@dt); -- 2008-09-10
select time(@dt); -- 07:15:30.123456
select year(@dt); -- 2008
select quarter(@dt); -- 3
select month(@dt); -- 9
select week(@dt); -- 36
select day(@dt); -- 10
select hour(@dt); -- 7
select minute(@dt); -- 15
select second(@dt); -- 30
select microsecond(@dt); -- 123456
2. MySQL Extract() 函数，可以上⾯实现类似的功能：
set @dt = '2008-09-10 07:15:30.123456';
select extract(year from @dt); -- 2008
select extract(quarter from @dt); -- 3
select extract(month from @dt); -- 9
select extract(week from @dt); -- 36
select extract(day from @dt); -- 10
select extract(hour from @dt); -- 7
select extract(minute from @dt); -- 15
select extract(second from @dt); -- 30
select extract(microsecond from @dt); -- 123456
select extract(year_month from @dt); -- 200809
select extract(day_hour from @dt); -- 1007
select extract(day_minute from @dt); -- 100715
select extract(day_second from @dt); -- 10071530
select extract(day_microsecond from @dt); -- 10071530123456
select extract(hour_minute from @dt); -- 715
select extract(hour_second from @dt); -- 71530
select extract(hour_microsecond from @dt); -- 71530123456
select extract(minute_second from @dt); -- 1530
select extract(minute_microsecond from @dt); -- 1530123456
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论