[转载]mysql 中 时间和日期函数

一、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

转载自：http://www.cnblogs.com/redfox241/archive/2009/07/23/1529092.html