ITPub博客

首页 > Linux操作系统 > Linux操作系统 > mysql日期函数小结及个人实验

mysql日期函数小结及个人实验

原创 Linux操作系统 作者:myownstars 时间:2011-03-02 15:55:12 0 删除 编辑
最近搜集了一下网上关于mysql的日期/时间函数的介绍,整理后自己进行了一些小实验

一、        Mysql获得当前日期时间

Now(): 获得当前的日期+时间(date + time)函数:
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2011-03-01 10:26:23 |
+---------------------+
1 row in set (0.00 sec)
同等的函数还包括current_timestamp(),localtime(),但是now()最容易记忆,所以推荐使用。

Sysdate(): 日期时间函数跟 now() 类似,不同之处在于:now() 在执行开始时值就得到了, sysdate() 在函数执行时动态得到值。
看下面的例子就明白了:
mysql> select now(),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> select sysdate(),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-01 11: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> select quarter(@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(year from @ct),
不足之处在于需要多敲几次键盘

Dayof函数:
Dayofweek(),dayofmonth(),dayofyear()分别返回日期再一周、一月以及一年中的位置
mysql> select dayofweek(@ct);
+----------------+
| dayofweek(@ct) |
+----------------+
|              3 |
+----------------+
1 row in set (0.00 sec)
注意:其实3月1号是星期二,但是返回数字3,因为是从Sunday开始算起的(1=Sunday,2=Monday,…)

mysql> select dayofmonth(@ct);
+-----------------+
| dayofmonth(@ct) |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)

mysql> select dayofyear(@ct);
+----------------+
| dayofyear(@ct) |
+----------------+
|             60 |
+----------------+
1 row in set (0.00 sec)

Week()函数
查看日期属于当年的第几周
mysql> select weekofyear(@ct);
+-----------------+
| weekofyear(@ct) |
+-----------------+
|               9 |
+-----------------+
1 row in set (0.00 sec)


返回星期名和月份名的函数
Dayname()—计算日期是星期几
mysql> select dayname(@ct);
+--------------+
| dayname(@ct) |
+--------------+
| Tuesday      |
+--------------+
1 row in set (0.02 sec)

Monthname()—计算日期是哪一月
mysql> select monthname(@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> select last_day(now());
+-----------------+
| last_day(now()) |
+-----------------+
| 2011-03-31      |
+-----------------+
1 row in set (0.00 sec)
通过该函数,可以计算出当前月份有多少天
mysql> select now(),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 num year/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,interval 1 day);
+------------------------------+
| date_add(@ct,interval 1 day) |
+------------------------------+
| 2011-03-02 15: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,interval 1 week);
+-------------------------------+
| date_add(@ct,interval 1 week) |
+-------------------------------+
| 2011-03-08 15:09:16           |
+-------------------------------+
1 row in set (0.00 sec)

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

另类日期函数
Period_add(P,N): 日期加/减去N月,其中P的格式应为yyyymm或yymm
Period_diff(P1,P2): 日期p1-p2,返回N个月

mysql> select period_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> select period_diff('201103','201101');
+--------------------------------+
| period_diff('201103','201101') |
+--------------------------------+
|                              2 |
+--------------------------------+
1 row in set (0.00 sec)

日期时间相减函数
Datediff(date1,date2): 两个日期date1-date2
mysql> select datediff('2011-03-09','2011-03-01');
+-------------------------------------+
| datediff('2011-03-09','2011-03-01') |
+-------------------------------------+
|                                   8 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> select datediff('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-03 15:33:00','2011-03-02 15:33:59');
+-------------------------------------------------------+
| timediff('2011-03-03 15:33:00','2011-03-02 15:33:59') |
+-------------------------------------------------------+
| 23:59:01                                              |
+-------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select timediff('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> select time_to_sec('01:00:00');
+-------------------------+
| time_to_sec('01:00:00') |
+-------------------------+
|                    3600 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select sec_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> select to_days('2011-03-01');
+-----------------------+
| to_days('2011-03-01') |
+-----------------------+
|                734562 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select from_days(734562);
+-------------------+
| from_days(734562) |
+-------------------+
| 2011-03-01        |
+-------------------+
1 row in set (0.00 sec)


Str_to_date(str,date): 字符串-->日期 转换函数
可以将一些杂乱无章的字符转换为日期格式
mysql> select str_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> select str_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)



小练习:
以表justin为例,目前该表总共有270多万条数据
mysql> select count(*) from justin;
+----------+
| count(*) |
+----------+
|  2725403 |
+----------+
1 row in set (0.00 sec)

现在对其做一些统计

查询过去30天总共有多少数据
mysql> select count(*) from justin where to_days(curdate())- to_days(create_time)<=30;
+----------+
| count(*) |
+----------+
|  2367518 |
+----------+
1 row in set (3.38 sec)

mysql> select count(*) from justin where datediff(curdate(),create_time) <=30;
+----------+
| count(*) |
+----------+
|  2367518 |
+----------+
1 row in set (3.29 sec)

查看每月第一天的数据
mysql> select count(*) from justin where dayofmonth(create_time)=1;
+----------+
| count(*) |
+----------+
|   161293 |
+----------+
1 row in set (3.14 sec)

查看11年1月31日之前的数据
mysql> select count(*) from justin where create_time <='2011-01-31 00:00:00';
+----------+
| count(*) |
+----------+
|   413797 |
+----------+
1 row in set (0.17 sec)

查看11年整个二月份的数据
mysql> select count(*) from justin where monthname(create_time)='February' and year(create_time)=2011;
+----------+
| count(*) |
+----------+
|  2149284 |
+----------+
1 row in set (3.94 sec)

查看11年每个周日的累积数据
mysql> select count(*) from justin where dayname(create_time)='Sunday' and year(create_time)=2011;
+----------+
| count(*) |
+----------+
|   479033 |
+----------+
1 row in set (3.88 sec)

查看每天零点时分插入的数据总和
mysql> select count(*) from justin where time(create_time)='00:00:00';
+----------+
| count(*) |
+----------+
|       37 |
+----------+
1 row in set (3.99 sec)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-688369/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3160200