玖叶教程网

1.获得系统时间 函数：now()

SELECT NOW() AS '系统时间';

2.获得当前时间戳函数：current_timestamp

SELECT current_timestamp

日期转换函数、时间转换函数

3.（日期/时间转换为字符串）函数：date_format(date,format)； time_format(time,format)

SELECT DATE_FORMAT(NOW(), '%Y%m%d%H%i%s');

SELECT TIME_FORMAT(NOW(), '%H:%i:%s');

4.（字符串转换为日期）函数：str_to_date(str, format)

SELECT STR_TO_DATE('2019.07.01', '%Y.%m.%d') AS '格式时间';

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

SELECT TO_DAYS('2019-06-10') AS '天数'

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

SELECT MAKEDATE(2019,31) AS 'MAKEDATE';

SELECT MAKETIME(23,15,30) AS '时间格式';

7.（Unix 时间戳、日期）转换函数

unix_timestamp(),

unix_timestamp(date),

from_unixtime(unix_timestamp),

from_unixtime(unix_timestamp,format)

select unix_timestamp();

SELECT UNIX_TIMESTAMP('2019-07-03');

SELECT UNIX_TIMESTAMP('2019-07-03 23:14:00');

8.时间间隔：date_add()

SET @datatime = NOW();

SELECT DATE_ADD(@datatime, INTERVAL 1 DAY);

SELECT DATE_ADD(@datatime, INTERVAL 1 HOUR);

SELECT DATE_ADD(@datatime, INTERVAL 1 MINUTE);

SELECT DATE_ADD(@datatime, INTERVAL 1 SECOND);

SELECT DATE_ADD(@datatime, INTERVAL 1 MICROSECOND);

SELECT DATE_ADD(@datatime, INTERVAL 1 WEEK);

SELECT DATE_ADD(@datatime, INTERVAL 1 MONTH);

SELECT DATE_ADD(@datatime, INTERVAL 1 QUARTER);

SELECT DATE_ADD(@datatime, INTERVAL 1 YEAR);

SELECT DATE_ADD(@datatime, INTERVAL -1 DAY) AS '时间';

9.日期、时间相减函数：datediff(date1,date2), timediff(time1,time2)

SELECT DATEDIFF('2019-01-03', '2019-01-01') AS '相差多少天';

10. 计算日期是星期几 DAYOFWEEK(date)

SELECT DAYOFWEEK('2019-07-03') AS '今天是星期几';

11. 计算本月是第几天 DAYOFMONTH(date)

SELECT DAYOFMONTH('2019-07-02') AS '本月第几天';

12. 计算本年是第几天DAYOFYEAR(date)

SELECT DAYOFYEAR('2019-07-02') AS '本年第几天';

13. 计算本年是第几月份 MONTH(date)

SELECT MONTH('2019-07-02') AS '本年第几月份';

14. 计算本年是第几季度 QUARTER(date)

SELECT QUARTER('2019-07-02') AS '本年第几季度';