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