MySQL常用日期函数

2019独角兽企业重金招聘Python工程师标准>>>

当前时间

SELECT
    now(),
    sysdate(),
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP (),
    sleep(1),
    now(),
    sysdate(),
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP ()

结果大概如下：

用的最多的就是now()函数了，CURRENT_TIMESTAMP主要是用在datetime类型作为默认值。sysdate()是一个实时的，而其他的在一个session中都是有缓存的。

日期转字符串

select date_format(now(), '%Y-%m-%d %H:%i:%s');

这个函数使用主要看习惯，一般不会在数据库层做转换，而会放在应用层。但是很多人习惯将查询条件转换为字符串比较。比如Java中的util.Date类型和MySQL数据库中的datetime类型只是比较到天(某年某月某日)的时候很多人写的像下面这样：

 DATE_FORMAT(charge_date,"%Y-%m-%d") >= DATE_FORMAT(#{chargeDate}, '%Y-%m-%d')

其实这样因为字符串比较的原因，效率是比较低的。后面介绍效率高一些的方法。

字符串转日期

SELECT
    str_to_date('01/31/2018', '%m/%d/%Y'),
    str_to_date('01.31.2018', '%m.%d.%Y'),
    str_to_date(
        '01.31.2018 08/09/30',
        '%m.%d.%Y %h/%i/%s'
    );

字符串转日期函数应该也是根据个人习惯的使用多少,这样就很多查询就只需要做一次转换就可以了。如果不是特别要求数据库层不能做转换的话，可以在应用层直接传字符串。

日期和天数转换

select to_days(now()),from_days(737090);

to_days转换为天，from_days从天数得的日期。用的多的是to_days,对于datetime只比较到天数(日期)，不比较时间的话完全可以使用to_days来完成。像下面这样：

WHERE TO_DAYS(charge_date)=TO_DAYS(#{chargeDate})

convert

select now(),convert(now(),date)

convert不仅仅是一个日期转换函数，但是在datetime 和 date类型比较的时候就可以使用convert来，而不是str_to_date 或者 date_format 例如,在MySQL数据库中charge_date是date类型，而Java中使用的是java.util.Date的时候就可以使用下面的SQL：

charge_date = CONVERT(#{date},DATE)

日期加减

SELECT
    date_add(now(), INTERVAL 1 DAY),
    date_add(now(), INTERVAL 1 HOUR),
    date_add(now(), INTERVAL 1 MINUTE),
    date_add(now(), INTERVAL 1 SECOND),
    date_add(now(), INTERVAL 1 MICROSECOND),
    date_add(now(), INTERVAL 1 WEEK),
    date_add(now(), INTERVAL 1 MONTH),
    date_add(now(), INTERVAL 1 QUARTER),
    date_add(now(), INTERVAL 1 YEAR),
    date_add(
        now(),
        INTERVAL '01:00:00' HOUR_SECOND
    ),
    date_add(
        now(),
        INTERVAL '1 00:00:00' DAY_SECOND
    );

日期加法没什么特别的，从上到下依次是当前日期加1天，1小时，1分钟，1秒钟，1微妙，1星期，1个月，1季度(3个月)，1年。

后2个说一下 HOUR_SECOND指的是加的时间格式是从"小时到秒",同理DAY_SECOND表示的加的时间是从"天到秒"。

日期的减法操作完全一样，只需要把date_add替换为date_sub就可以了。

日期差值

select datediff('2018-01-30', '2018-01-31'),datediff('2018-01-31', '2018-01-30');

日期差datediff函数就是返回2个日期相差的天数，注意2个日期大小关系与返回值的正负关系。第一个日期大就为正值。与datediff相识的是timediff，不过很少用了。

时间秒转换

select time_to_sec('00:00:05'),sec_to_time(3600);

时间(time)和秒的相互转换也很少用。可以通过这2个函数计算一天有多少秒，这个秒数对应的是一天中的哪一个时间等。

转载于:https://my.oschina.net/u/2474629/blog/1616068