mySQL日期函数并运行_mysql日期相关的函数

1、获取当前时间：

/**

获得当前日期+时间(date + time)函数：now(), 常用

**/

select now() fromdual;/**

获取当前时间戳，current_timestamp或者current_timestamp()

**/

select current_timestamp, current_timestamp() fromdual;/**

获得当前日期+时间(date + time)函数：sysdate(),

和now()不同的是，now() 在执行开始时值就得到了， sysdate() 在函数执行时动态得到值

**/

select now(), sleep(3), sysdate() from dual;

运行结果：

2、日期/时间操作

如果在日期列上有索引，在进行操作不建议对这些字段进行日期函数操作，因为这样该列的索引将无效。应该转换一下思路，对操作的值进行函数操作，而非列。

其中日期格式化的format串有：

说明符

说明

%a

工作日的缩写名称  (Sun..Sat)

%b

月份的缩写名称  (Jan..Dec)

%c

月份，数字形式(0..12)

%D

带有英语后缀的该月日期  (0th, 1st, 2nd, 3rd, ...)

%d

该月日期, 数字形式 (00..31)

%e

该月日期, 数字形式(0..31)

%f

微秒 (000000..999999)

%H

小时(00..23)

%h

小时(01..12)

%I

小时 (01..12)

%i

分钟,数字形式 (00..59)

%j

一年中的天数 (001..366)

%k

小时 (0..23)

%l

小时 (1..12)

%M

月份名称 (January..December)

%m

月份, 数字形式 (00..12)

%p

上午(AM)或下午( PM)

%r

时间 , 12小时制 (小时hh:分钟mm:秒数ss 后加 AM或PM)

%S

秒 (00..59)

%s

秒 (00..59)

%T

时间 , 24小时制 (小时hh:分钟mm:秒数ss)

%U

周 (00..53), 其中周日为每周的第一天

%u

周 (00..53), 其中周一为每周的第一天

%V

周 (01..53), 其中周日为每周的第一天 ; 和 %X同时使用

%v

周 (01..53), 其中周一为每周的第一天 ; 和 %x同时使用

%W

工作日名称 (周日..周六)

%w

一周中的每日 (0=周日..6=周六)

%X

该周的年份，其中周日为每周的第一天, 数字形式,4位数;和%V同时使用

%x

该周的年份，其中周一为每周的第一天, 数字形式,4位数;和%v同时使用

%Y

年份, 数字形式,4位数

%y

年份, 数字形式 (2位数)

%%

‘%’文字字符

2.1、日期/时间转字符串、字符串转日期/时间

常用：

1 /**2 将日期格式化成字符串，mysql在日期的处理上比较宽松，data_format的第一个参数是日期格式的字符串其实也能解析，不过不建议这么操作3 **/

4 select DATE_FORMAT(now(),'%Y%m%d %H:%i:%s');5 select DATE_FORMAT('20160711111203','%Y-%m-%d %H:%i:%s');6

7 /**8 将日期格式的字符串转换成日期/时间，该format字符串支持date和time9 **/

10 select STR_TO_DATE('2016-07-11 11:12:03','%Y-%m-%d %H:%i:%s');11

12 /**13 将time(不包括日期)转换成字符串形式14 **/

15 select TIME_FORMAT(now(),'%Y%m%d %H:%i:%s');

运行结果：

20160712 11:27:47

2016-07-11 11:12:03

2016-07-11 11:12:03

00000000 11:27:47

其他：

(日期、天数)转换函数：to_days(date), from_days(days)

/**

结果：736522

**/

select TO_DAYS(now()) fromdual;/**

结果：1916-07-15

**/

select FROM_DAYS(736522) from dual;

(时间、秒)转换函数：time_to_sec(time), sec_to_time(seconds)

/**

结果：3605

**/

select time_to_sec('01:00:05');/**

结果：01:00:05

**/

select sec_to_time(3605);

拼凑日期、时间函数：makdedate(year,dayofyear), maketime(hour,minute,second)

/**

结果：2016-01-30

**/

select makedate(2016,30);/**

结果：2016-02-01

**/

select makedate(2016,32);/**

结果：12:15:30

**/

select maketime(12,15,30);

(Unix 时间戳、日期)转换函数：unix_timestamp(),unix_timestamp(date),from_unixtime(unix_timestamp),from_unixtime(unix_timestamp,format)

select now(),unix_timestamp(); --2016-07-12 11:41:56 , 1468294916

select unix_timestamp('2016-07-12'); --1468252800

select unix_timestamp('2016-07-12 11:38:37'); --1468294717

select from_unixtime(1468294702); --2016-07-12 11:38:22

select from_unixtime(1468294717); --2016-07-12 11:38:37

select from_unixtime(1468294717, '%Y %D %M %h:%i:%s %x'); --2016 12th July 11:38:37 2016

2.2、日期计算函数

日期/时间的加减：DATE_ADD(date,INTERVAL expr type) 、DATE_SUB(date,INTERVAL expr type)

还有几个跟上面2个方法差不多的方法，不过可以用上面2个替换掉

/**

和DATE_ADD()是一样的

**/ADDDATE(date,INTERVAL expr type)/**

简写，直接加多少天，例如：select ADDDATE(now(),1)

**/ADDDATE(expr,days)/**

和DATE_SUB()是一样的

**/SUBDATE(date,INTERVAL expr unit)/**

简写，直接减多少天，例如：select SUBDATE(now(),1)

**/SUBDATE(expr,days)

另外还有ADDTIME(expr1,expr2)、 SUBTIME(expr1,expr2)

例子：

/**

增加一天 数字为负数则为减一天

结果为：2016-07-12 14:16:13 , 2016-07-13 14:16:13

**/

select now(),date_add(now(), interval 1 day);/**

减掉一天

结果为：2016-07-12 14:16:13 , 2016-07-11 14:16:13

**/

select now(),date_sub(now(), interval 1 day);/**

复合型相减

结果为：2016-07-12 14:20:05 , 2015-05-12 14:20:05

其中'1 2'中间的分隔符可为：空格 或者 - 或者 ,

**/

select now(),date_sub(now(), interval '1 2' YEAR_MONTH);

其中type的可选值有：

MICROSECOND

间隔单位：毫秒

SECOND

间隔单位：秒

MINUTE

间隔单位：分钟

HOUR

间隔单位：小时

DAY

间隔单位：天

WEEK

间隔单位：星期

MONTH

间隔单位：月

QUARTER

间隔单位：季度

YEAR

间隔单位：年

SECOND_MICROSECOND

复合型，间隔单位：秒、毫秒，expr可以用两个值来分别指定秒和毫秒

MINUTE_MICROSECOND

复合型，间隔单位：分、毫秒

MINUTE_SECOND

复合型，间隔单位：分、秒

HOUR_MICROSECOND

复合型，间隔单位：小时、毫秒

HOUR_SECOND

复合型，间隔单位：小时、秒

HOUR_MINUTE

复合型，间隔单位：小时分

DAY_MICROSECOND

复合型，间隔单位：天、毫秒

DAY_SECOND

复合型，间隔单位：天、秒

DAY_MINUTE

复合型，间隔单位：天、分

DAY_HOUR

复合型，间隔单位：天、小时

YEAR_MONTH

复合型，间隔单位：年、月

2.3、计算日期/时间的差

计算日期(day)的差：DATEDIFF(expr1,expr2)， 结果为：expr1-expr2

select DATEDIFF(now(),date_add(now(), interval 1 day)); ---1

select DATEDIFF(now(),now()); --0

计算时间差：TIMEDIFF(expr1,expr2)，结果为：expr1-expr2，格式为: 小时:分:秒

/**

结果为：2016-07-12 14:40:03 2016-07-12 18:00:03 03:20:00

**/

select now(),DATE_ADD(now(),INTERVAL 200 minute),TIMEDIFF(DATE_ADD(now(),INTERVAL 200 minute),now())

计算指定单位的差：TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)，其中unit参考上面的type的那个表格，计算过程是：datetime_expr2-datetime_expr1

/**

结果：2016-07-12 14:51:50 2016-07-12 15:51:50 1

**/

select now(),DATE_ADD(now(),INTERVAL 1 hour),TIMESTAMPDIFF(HOUR,now(),DATE_ADD(now(),INTERVAL 1 hour))