(转)mysql的日期/时间函数

发表:2013-09-10 12:59:07

一、Mysql获得当前日期时间


Now():获得当前的日期+ 时间(date + time )函数:

mysql> select now();

+---------------------+

|now() |

+---------------------+

| 2011-03-0110:26:23 |

+---------------------+

1 row in set (0.00sec)

同等的函数还包括current_timestamp(),localtime(),但是now() 最容易记忆,所以推荐使用。

Sysdate():日期时间函数跟 now()类似,不同之处在于:now()在执行开始时值就得到了, sysdate()在函数执行时动态得到值。

看下面的例子就明白了:

mysql> selectnow(),sleep(3),now();

+---------------------+----------+---------------------+

|now() | sleep(3) |now() |

+---------------------+----------+---------------------+

| 2011-03-01 10:51:43| 0 | 2011-03-01 10:51:43 |

+---------------------+----------+---------------------+

1 row in set (3.02 sec)

mysql> selectsysdate(),sleep(3),sysdate();

+---------------------+----------+---------------------+

|sysdate() |sleep(3) |sysdate() |

+---------------------+----------+---------------------+

| 2011-03-01 10:52:09| 0 | 2011-03-01 10:52:12 |

+---------------------+----------+---------------------+

1 row in set (3.00 sec)

可以看到,虽然中途sleep 3 秒,但 now() 函数两次的时间值是相同的; sysdate() 函数两次得到的时间值相差 3

也可以只取当前日期或者当前时间

Curdate():获得当前日期

mysql> select curdate();

+------------+

| curdate() |

+------------+

| 2011-03-01 |

+------------+

1 row in set (0.01 sec)

Curtime():获得当前时间(time )函数

mysql> select curtime();

+-----------+

| curtime() |

+-----------+

| 10:55:12 |

+-----------+

1 row in set (0.00 sec)

二、Mysql日期时间的抽取extract

通过这一功能,我们可以从一个时间中抽取自己想要的部分,例如

mysql> set @ct='2011-03-0111:16:14.123456';

Query OK, 0 rows affected (0.01 sec)

设置变量ct 为某一时间值,精确到微妙

获取其日期值

mysql> select date(@ct);

+------------+

| date(@ct) |

+------------+

| 2011-03-01 |

+------------+

1 row in set (0.00 sec)

查看此日期所属季度

mysql> selectquarter(@ct);

+--------------+

| quarter(@ct) |

+--------------+

| 1 |

+--------------+

1 row in set (0.00 sec)

查看此日期属于当年哪一周

mysql> select week(@ct);

+-----------+

| week(@ct) |

+-----------+

| 9 |

+-----------+

1 row in set (0.00 sec)

另外还有year(),day(),hour(),minute(),second()等,在此不一一赘述。

采用extract(),也可以实现类似的功能,语法格式为extract(yearfrom @ct)

不足之处在于需要多敲几次键盘

Dayof函数:

Dayofweek(),dayofmonth(),dayofyear()分别返回日期再一周、一月以及一年中的位置

mysql> selectdayofweek(@ct);

+----------------+

| dayofweek(@ct) |

+----------------+

| 3 |

+----------------+

1 row in set (0.00 sec)

注意:其实31号是星期二,但是返回数字3,因为是从Sunday开始算起的(1=Sunday,2=Monday,)

mysql> selectdayofmonth(@ct);

+-----------------+

| dayofmonth(@ct) |

+-----------------+

| 1 |

+-----------------+

1 row in set (0.00 sec)

mysql> selectdayofyear(@ct);

+----------------+

| dayofyear(@ct) |

+----------------+

| 60 |

+----------------+

1 row in set (0.00 sec)

Week()函数

查看日期属于当年的第几周

mysql> selectweekofyear(@ct);

+-----------------+

| weekofyear(@ct) |

+-----------------+

| 9 |

+-----------------+

1 row in set (0.00 sec)

返回星期名和月份名的函数

Dayname()—计算日期是星期几

mysql> selectdayname(@ct);

+--------------+

| dayname(@ct) |

+--------------+

|Tuesday |

+--------------+

1 row in set (0.02 sec)

Monthname()—计算日期是哪一月

mysql> selectmonthname(@ct);

+----------------+

| monthname(@ct) |

+----------------+

|March |

+----------------+

1 row in set (0.00 sec)

Last_day():返回月份中最后一天

mysql> select now();

+---------------------+

|now() |

+---------------------+

| 2011-03-01 13:15:00 |

+---------------------+

1 row in set (0.00 sec)

mysql> selectlast_day(now());

+-----------------+

| last_day(now()) |

+-----------------+

|2011-03-31 |

+-----------------+

1 row in set (0.00 sec)

通过该函数,可以计算出当前月份有多少天

mysql> selectnow(),day(last_day(now())) ;

+---------------------+----------------------+

|now() | day(last_day(now())) |

+---------------------+----------------------+

| 2011-03-01 13:17:12| 31 |

+---------------------+----------------------+

1 row in set (0.00 sec)

三、Mysql 的日期时间计算函数

Date_add():为日期增加一个时间间隔

具体语法为date_add(@ct, interval numyear/quarter/month/week/day/hour/minute/secont/microsecond);

注意: 此函数并不改变变量@ct 的实际值

mysql> set @ct=now();

Query OK, 0 rows affected (0.00 sec)

mysql> select @ct;

+---------------------+

|@ct |

+---------------------+

| 2011-03-01 15:09:16 |

+---------------------+

1 row in set (0.00 sec)

mysql> select date_add(@ct,interval1 day);

+------------------------------+

| date_add(@ct,interval 1 day) |

+------------------------------+

| 2011-03-0215:09:16 |

+------------------------------+

1 row in set (0.00 sec)

mysql> select @ct;

+---------------------+

|@ct |

+---------------------+

| 2011-03-01 15:09:16 |

+---------------------+

1 row in set (0.00 sec)

mysql> select date_add(@ct,interval1 week);

+-------------------------------+

| date_add(@ct,interval 1 week) |

+-------------------------------+

| 2011-03-0815:09:16 |

+-------------------------------+

1 row in set (0.00 sec)

类似功能还有adddate(),addtime() 等函数 ,与之相对应的是date_sub() ,顾名思义就是日期减法

另类日期函数

Period_add(P,N):日期加/减去N 月,其中P 的格式应为yyyymmyymm

Period_diff(P1,P2):日期p1-p2 ,返回N 个月

mysql> selectperiod_add(201103,2),period_add(201103,-2) ;

+----------------------+-----------------------+

| period_add(201103,2) | period_add(201103,-2)|

+----------------------+-----------------------+

| 201105| 201101 |

+----------------------+-----------------------+

1 row in set (0.00 sec)

mysql> selectperiod_diff('201103','201101');

+--------------------------------+

| period_diff('201103','201101') |

+--------------------------------+

| 2 |

+--------------------------------+

1 row in set (0.00 sec)

日期时间相减函数

Datediff(date1,date2):两个日期date1-date2

mysql> selectdatediff('2011-03-09','2011-03-01');

+-------------------------------------+

| datediff('2011-03-09','2011-03-01') |

+-------------------------------------+

| 8 |

+-------------------------------------+

1 row in set (0.00 sec)

mysql> selectdatediff('2011-03-01','2011-03-09');

+-------------------------------------+

| datediff('2011-03-01','2011-03-09') |

+-------------------------------------+

| -8 |

+-------------------------------------+

1 row in set (0.00 sec)

Timediff(time1,time2):两个时间相减

mysql> select timediff('2011-03-0315:33:00','2011-03-02 15:33:59');

+-------------------------------------------------------+

| timediff('2011-03-03 15:33:00','2011-03-0215:33:59') |

+-------------------------------------------------------+

|23:59:01 |

+-------------------------------------------------------+

1 row in set (0.00 sec)

mysql> selecttimediff('15:33:00','15:33:59');

+---------------------------------+

| timediff('15:33:00','15:33:59') |

+---------------------------------+

|-00:00:59 |

+---------------------------------+

1 row in set (0.00 sec)

mysql日期、时间转换函数

Time_to_sec(time):时间—> 秒 转换函数

Sec_to_time(num):--> 时间 转换函数

mysql> selecttime_to_sec('01:00:00');

+-------------------------+

| time_to_sec('01:00:00') |

+-------------------------+

| 3600 |

+-------------------------+

1 row in set (0.00 sec)

mysql> selectsec_to_time(3600);

+-------------------+

| sec_to_time(3600) |

+-------------------+

|01:00:00 |

+-------------------+

1 row in set (0.00 sec)

To_days(date):日期--> 天 转换函数 起始日期为0000-00-00

From_days(num):--> 日期将数字转换为具体的日期

mysql> selectto_days('2011-03-01');

+-----------------------+

| to_days('2011-03-01') |

+-----------------------+

| 734562 |

+-----------------------+

1 row in set (0.00 sec)

mysql> selectfrom_days(734562);

+-------------------+

| from_days(734562) |

+-------------------+

|2011-03-01 |

+-------------------+

1 row in set (0.00 sec)

Str_to_date(str,date):字符串--> 日期转换函数

可以将一些杂乱无章的字符转换为日期格式

mysql> selectstr_to_date('01.03.2011', '%m.%d.%Y');

+---------------------------------------+

| str_to_date('01.03.2011', '%m.%d.%Y')|

+---------------------------------------+

|2011-01-03 |

+---------------------------------------+

1 row in set (0.00 sec)

mysql> selectstr_to_date('01/03/2011', '%m/%d/%Y');

+---------------------------------------+

| str_to_date('01/03/2011', '%m/%d/%Y')|

+---------------------------------------+

|2011-01-03 |

+---------------------------------------+

1 row in set (0.00 sec)

小练习:

以表centralmobile_logs为例,目前该表总共有270多万条数据

mysql> select count(*) fromcentralmobile_logs;

+----------+

| count(*) |

+----------+

| 2725403 |

+----------+

1 row in set (0.00 sec)

现在对其做一些统计

查询过去30 天总共有多少数据

mysql> select count(*) fromcentralmobile_logs where to_days(curdate())-to_days(create_time)<=30;

+----------+

| count(*) |

+----------+

| 2367518 |

+----------+

1 row in set (3.38 sec)

mysql> select count(*) fromcentralmobile_logs where datediff(curdate(),create_time)<=30;

+----------+

| count(*) |

+----------+

| 2367518 |

+----------+

1 row in set (3.29 sec)

查看每月第一天的数据

mysql> select count(*) fromcentralmobile_logs where dayofmonth(create_time)=1;

+----------+

| count(*) |

+----------+

| 161293|

+----------+

1 row in set (3.14 sec)

查看11131 日之前的数据

mysql> select count(*) fromcentralmobile_logs where create_time <='2011-01-3100:00:00';

+----------+

| count(*) |

+----------+

| 413797|

+----------+

1 row in set (0.17 sec)

查看11年整个二月份的数据

mysql> select count(*) fromcentralmobile_logs where monthname(create_time)='February' andyear(create_time)=2011;

+----------+

| count(*) |

+----------+

| 2149284 |

+----------+

1 row in set (3.94 sec)

查看11年每个周日的累积数据

mysql> select count(*) fromcentralmobile_logs where dayname(create_time)='Sunday' andyear(create_time)=2011;

+----------+

| count(*) |

+----------+

| 479033|

+----------+

1 row in set (3.88 sec)

查看每天零点时分插入的数据总和

mysql> select count(*) fromcentralmobile_logs where time(create_time)='00:00:00';

+----------+

| count(*) |

+----------+

| 37 |

+----------+

1 row in set (3.99 sec)


相关文章