MySQL：MySQL日期数据范例、MySQL时候范例利用总结 -

wcp88888888

浏览: 472468 次
性别:
来自: 北京

最近访客更多访客>>

MGT

lonfiy

qinghe3012

shine_fu

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

MySQL：MySQL日期数据范例、MySQL时候范例利用总结

博客分类：

数据库

MySQL Unix

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 会替你去向理。

一样平常环境下，我偏向于利用 datetime 日期范例。

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> 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 日期时候 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
select extract(second_microsecond from @dt); -- 30123456

MySQL Extract() 函数除了没有date(),time() 的功效外，其他功效一应具全。而且还具有拔取‘day_microsecond’ 等功效。注重这里不是只拔取 day 和 microsecond，而是从日期的 day 部门一向拔取到 microsecond 部门。够刁悍的吧！

MySQL Extract() 函数独一欠好的处所在于：你必要多敲几回键盘。

3. MySQL dayof... 函数：dayofweek(), dayofmonth(), dayofyear()

别离返回日期参数，在一周、一月、一年中的位置。

set @dt = '2008-08-08';

select dayofweek(@dt); -- 6
select dayofmonth(@dt); -- 8
select dayofyear(@dt); -- 221

日期 '2008-08-08' 是一周中的第 6 天（1 = Sunday, 2 = Monday, ..., 7 = Saturday）；一月中的第 8 天；一年中的第 221 天。

4. MySQL week... 函数：week(), weekofyear(), dayofweek(), weekday(), yearweek()

set @dt = '2008-08-08';

select week(@dt); -- 31
select week(@dt,3); -- 32
select weekofyear(@dt); -- 32

select dayofweek(@dt); -- 6
select weekday(@dt); -- 4

select yearweek(@dt); -- 200831

MySQL week() 函数，可以有两个参数，详细可看手册。 weekofyear() 和 week() 一样，都是计较“某天”是位于一年中的第几周。 weekofyear(@dt) 等价于 week(@dt,3)。

MySQL weekday() 函数和 dayofweek() 近似，都是返回“某天”在一周中的位置。分歧点在于参考的尺度， weekday：(0 = Monday, 1 = Tuesday, ..., 6 = Sunday)； dayofweek：（1 = Sunday, 2 = Monday, ..., 7 = Saturday）

MySQL yearweek() 函数，返回 year(2008) + week 位置(31)。

5. MySQL 返回礼拜和月份名称函数：dayname(), monthname()

set @dt = '2008-08-08';

select dayname(@dt); -- Friday
select monthname(@dt); -- August

思虑，若何返回中文的名称呢？

6. MySQL last_day() 函数：返回月份中的末了一天。

select last_day('2008-02-01'); -- 2008-02-29
select last_day('2008-08-08'); -- 2008-08-31

MySQL last_day() 函数很是有效，好比我想获得当前月份中有几多天，可以如许来计较：

mysql> select now(), day(last_day(now())) as days;

+---------------------+------+
| now() | days |
+---------------------+------+
| 2008-08-09 11:45:45 | 31 |
+---------------------+------+

三、MySQL 日期时候计较函数
1. MySQL 为日期增添一个时候距离：date_add()

set @dt = now();

select date_add(@dt, interval 1 day); -- add 1 day
select date_add(@dt, interval 1 hour); -- add 1 hour
select date_add(@dt, interval 1 minute); -- ...
select date_add(@dt, interval 1 second);
select date_add(@dt, interval 1 microsecond);
select date_add(@dt, interval 1 week);
select date_add(@dt, interval 1 month);
select date_add(@dt, interval 1 quarter);
select date_add(@dt, interval 1 year);

select date_add(@dt, interval -1 day); -- sub 1 day

MySQL adddate(), addtime()函数，可以用 date_add() 来替换。下面是 date_add() 实现 addtime() 功效示例：

mysql> set @dt = '2008-08-09 12:12:33';

mysql>
mysql> select date_add(@dt, interval '01:15:30' hour_second);

+------------------------------------------------+
| date_add(@dt, interval '01:15:30' hour_second) |
+------------------------------------------------+
| 2008-08-09 13:28:03 |
+------------------------------------------------+

mysql> select date_add(@dt, interval '1 01:15:30' day_second);

+-------------------------------------------------+
| date_add(@dt, interval '1 01:15:30' day_second) |
+-------------------------------------------------+
| 2008-08-10 13:28:03 |
+-------------------------------------------------+

date_add() 函数，别离为 @dt 增添了“1小时 15分 30秒” 和 “1天 1小时 15分 30秒”。发起：老是利用 date_add() 日期时候函数来替换 adddate(), addtime()。

2. MySQL 为日期减去一个时候距离：date_sub()

mysql> select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second);

+----------------------------------------------------------------+
| date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second) |
+----------------------------------------------------------------+
| 1997-12-30 22:58:59 |
+----------------------------------------------------------------+

MySQL date_sub() 日期时候函数和 date_add() 用法同等，不再赘述。别的，MySQL 中另有两个函数 subdate(), subtime()，发起，用 date_sub() 来替换。

3. MySQL 另类日期函数：period_add(P,N), period_diff(P1,P2)

函数参数“P” 的格局为“YYYYMM” 大概 “YYMM”，第二个参数“N” 暗示增添或减去 N month（月）。

MySQL period_add(P,N)：日期加/减去N月。

mysql> select period_add(200808,2), period_add(20080808,-2)

+----------------------+-------------------------+
| period_add(200808,2) | period_add(20080808,-2) |
+----------------------+-------------------------+
| 200810 | 20080806 |
+----------------------+-------------------------+

MySQL period_diff(P1,P2)：日期 P1-P2，返回 N 个月。

mysql> select period_diff(200808, 200801);

+-----------------------------+
| period_diff(200808, 200801) |
+-----------------------------+
| 7 |
+-----------------------------+

在 MySQL 中，这两个日期函数，一样平常环境下很罕用到。

4. MySQL 日期、时候相减函数：datediff(date1,date2), timediff(time1,time2)

MySQL datediff(date1,date2)：两个日期相减 date1 - date2，返回天数。

select datediff('2008-08-08', '2008-08-01'); -- 7
select datediff('2008-08-01', '2008-08-08'); -- -7

MySQL timediff(time1,time2)：两个日期相减 time1 - time2，返回 time 差值。

select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); -- 08:08:08
select timediff('08:08:08', '00:00:00'); -- 08:08:08

注重：timediff(time1,time2) 函数的两个参数范例必需不异。

四、MySQL 日期转换函数、时候转换函数
1. MySQL （时候、秒）转换函数：time_to_sec(time), sec_to_time(seconds)

select time_to_sec('01:00:05'); -- 3605
select sec_to_time(3605); -- '01:00:05'

2. MySQL （日期、天数）转换函数：to_days(date), from_days(days)

select to_days('0000-00-00'); -- 0
select to_days('2008-08-08'); -- 733627

select from_days(0); -- '0000-00-00'
select from_days(733627); -- '2008-08-08'

3. MySQL Str to Date （字符串转换为日期）函数：str_to_date(str, format)

select str_to_date('08/09/2008', '%m/%d/%Y'); -- 2008-08-09
select str_to_date('08/09/08' , '%m/%d/%y'); -- 2008-08-09
select str_to_date('08.09.2008', '%m.%d.%Y'); -- 2008-08-09
select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30
select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30

可以看到，str_to_date(str,format) 转换函数，可以把一些混乱无章的字符串转换为日期格局。别的，它也可以转换为时候。“format” 可以参看 MySQL 手册。

4. MySQL Date/Time to Str（日期/时候转换为字符串）函数：date_format(date,format), time_format(time,format)

mysql> select date_format('2008-08-08 22:23:00', '%W %M %Y');

+------------------------------------------------+
| date_format('2008-08-08 22:23:00', '%W %M %Y') |
+------------------------------------------------+
| Friday August 2008 |
+------------------------------------------------+

mysql> select date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s');

+----------------------------------------------------+
| date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s') |
+----------------------------------------------------+
| 20080808222301 |
+----------------------------------------------------+

mysql> select time_format('22:23:01', '%H.%i.%s');

+-------------------------------------+
| time_format('22:23:01', '%H.%i.%s') |
+-------------------------------------+
| 22.23.01 |
+-------------------------------------+

MySQL 日期、时候转换函数：date_format(date,format), time_format(time,format) 可以或许把一个日期/时候转换成各类百般的字符串格局。它是 str_to_date(str,format) 函数的一个逆转换。

5. MySQL 得到国度地域时候格局函数：get_format()

MySQL get_format() 语法：

get_format(date|time|datetime, 'eur'|'usa'|'jis'|'iso'|'internal'

MySQL get_format() 用法的全数示例：

select get_format(date,'usa') ; -- '%m.%d.%Y'
select get_format(date,'jis') ; -- '%Y-%m-%d'
select get_format(date,'iso') ; -- '%Y-%m-%d'
select get_format(date,'eur') ; -- '%d.%m.%Y'
select get_format(date,'internal') ; -- '%Y%m%d'
select get_format(datetime,'usa') ; -- '%Y-%m-%d %H.%i.%s'
select get_format(datetime,'jis') ; -- '%Y-%m-%d %H:%i:%s'
select get_format(datetime,'iso') ; -- '%Y-%m-%d %H:%i:%s'
select get_format(datetime,'eur') ; -- '%Y-%m-%d %H.%i.%s'
select get_format(datetime,'internal') ; -- '%Y%m%d%H%i%s'
select get_format(time,'usa') ; -- '%h:%i:%s %p'
select get_format(time,'jis') ; -- '%H:%i:%s'
select get_format(time,'iso') ; -- '%H:%i:%s'
select get_format(time,'eur') ; -- '%H.%i.%s'
select get_format(time,'internal') ; -- '%H%i%s'

MySQL get_format() 函数在现实顶用到机遇的比力少。

6. MySQL 拼集日期、时候函数：makdedate(year,dayofyear), maketime(hour,minute,second)

select makedate(2001,31); -- '2001-01-31'
select makedate(2001,32); -- '2001-02-01'

select maketime(12,15,30); -- '12:15:30'

五、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
select unix_timestamp('2008-08-08'); -- 1218124800
select unix_timestamp('2008-08-08 12:30:00'); -- 1218169800

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 时区（timezone）转换函数
convert_tz(dt,from_tz,to_tz)

select convert_tz('2008-08-08 12:00:00', '+08:00', '+00:00'); -- 2008-08-08 04:00:00

时区转换也可以经由过程 date_add, date_sub, timestampadd 来实现。

select date_add('2008-08-08 12:00:00', interval -8 hour); -- 2008-08-08 04:00:00
select date_sub('2008-08-08 12:00:00', interval 8 hour); -- 2008-08-08 04:00:00
select timestampadd(hour, -8, '2008-08-08 12:00:00'); -- 2008-08-08 04:00:00

分享到：

Mysql 中使用DATE_FORMAT函数按月、周统计 ... | activeMQ

2011-01-13 12:51
浏览 1439
评论(0)
分类:数据库
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

MySQL：MySQL日期数据范例、MySQL时候范例利用总结

评论

发表评论

相关推荐

最近访客 更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

MySQL：MySQL日期数据范例、MySQL时候范例利用总结

评论

发表评论

相关推荐

(转)SQLServer行列转换 Pivot UnPivot

SQL中使用WITH AS提高性能－使用公用表表达式（CTE）简化嵌套SQL

mybatis 模糊查询

ibatis 入门

转 JTA简介

数据库 left join,right join,inner join用法举例

sql Server like 匹配符的介绍

sql Server varchar和nvarchar的区别

sql in

sql like

order by

count 函数

group by

having 子句

内连接

外链接

subquery 框架

UNION

Union all

sql 中Intersect

最近访客更多访客>>