来源：未知 时间：2014-06-08 23:11 作者：xbdadmin 阅读:次
[导读] 一、Mysql获得当前日期时间 Now():获得当前的日期+ 时间（date + time ）函数： mysql select now(); +---------------------+ |now() | +---------------------+ | 2011-03-0110:26:23 | +---------------------+ 1 row in set (0.0...
|
一、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)
注意：其实3 月1号是星期二，但是返回数字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 的格式应为yyyymm 或yymm
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)
查看11年1 月31 日之前的数据
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)
|
自学PHP网专注网站建设学习,PHP程序学习,平面设计学习，以及操作系统学习
京ICP备14009008号-1@版权所有www.zixuephp.com
网站声明：本站所有视频，教程都由网友上传，站长收集和分享给大家学习使用，如由牵扯版权问题请联系站长邮箱904561283@qq.com
