1.CURRENT_DATE

CURDATE()、CURRENT_DATE()、CURRENT_DATE

SELECT CURDATE(),CURRENT_DATE(),CURRENT_DATE;

结果：

image.png

2.CURRENT_TIME

SELECT CURRENT_TIME,CURRENT_TIME();

result

3.DATETIME 、TIMESTAMP、LOCALTIMESTAMP

NOW()、SYSDATE()、CURRENT_TIMESTAMP、CURRENT_TIMESTAMP()、LOCALTIME、LOCALTIME()、LOCALTIMESTAMP、LOCALTIMESTAMP()

SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP(),LOCALTIME,LOCALTIME(),LOCALTIMESTAMP,LOCALTIMESTAMP();

result

4.获得当前 UTC 日期时间函数：utc_date(), utc_time(), utc_timestamp()

SELECT UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP(),UTC_DATE,UTC_TIME,UTC_TIMESTAMP;

result

5. NOW()与SYSDATE() 区别

SELECT NOW(),SLEEP(1),NOW();

result

SELECT SYSDATE(),SLEEP(1),SYSDATE();

result

总结： now() 在执行开始时值就得到了， sysdate() 在函数执行时动态得到值。真正的系统时间，不受mysql 的SLEEP()等函数的影响。

6. 格式化日期 （日期转字符串） DATE_FORMAT(date, format)

SELECT DATE_FORMAT(NOW(),"%Y年%m月%d日 %H:%i:%s or %T");

result

format字符串格式化date值如下:

• %S, %s 两位数字形式的秒（ 00,01, …, 59）
• %I, %i 两位数字形式的分（ 00,01, …, 59）
• %H 两位数字形式的小时，24 小时（00,01, …, 23）
• %h 两位数字形式的小时，12 小时（01,02, …, 12）
• %k 数字形式的小时，24 小时（0,1, …, 23）
• %l 数字形式的小时，12 小时（1, 2, …, 12）
• %T 24 小时的时间形式（hh:mm:ss）
• %r 12 小时的时间形式（hh:mm:ss AM 或hh:mm:ss PM）
• %p AM或PM
• %W 一周中每一天的名称（Sunday, Monday, …, Saturday）
• %a 一周中每一天名称的缩写（Sun, Mon, …, Sat）
• %d 两位数字表示月中的天数（00, 01,…, 31）
• %e 数字形式表示月中的天数（1, 2， …, 31）
• %D 英文后缀表示月中的天数（1st, 2nd, 3rd,…）
• %w 以数字形式表示周中的天数（ 0 = Sunday, 1=Monday, …, 6=Saturday）
• %j 以三位数字表示年中的天数（ 001, 002, …, 366）
• %U 周（0, 1, 52），其中Sunday 为周中的第一天
• %u 周（0, 1, 52），其中Monday 为周中的第一天
• %M 月名（January, February, …, December）
• %b 缩写的月名（ January, February,…., December）
• %m 两位数字表示的月份（01, 02, …, 12）
• %c 数字表示的月份（1, 2, …., 12）
• %Y 四位数字表示的年份
• %y 两位数字表示的年份
• %% 直接值“%”

6.2 STR_TO_DATE（字符串转日期）

SELECT STR_TO_DATE("5月30号 2019年","%m月%d号 %Y年")

result

7.选取日期时间的各个部分

SELECT NOW(),date(NOW()),time(NOW()),year(NOW()),quarter(NOW()),month(NOW()),week(NOW()),day(NOW()),hour(NOW()),minute(NOW()),second(NOW());

result

SELECT YEAR('2019/09/10 12:13:14'),MONTH('2019-09-10 12:13:14'),DAY('2019-09-10 12:13:14'),HOUR('2019-09-10 12:13:14')

result

选取日期函数如下：

• date(date);
• time(date);
• year(date);
• quarter(date);
• month(date);
• week(date);
• day(date);
• hour(date);
• minute(date);
• second(date);
• microsecond(date);

8.MySQL Extract() 函数

• extract(year from date);
• extract(quarter from date);
• extract(month from date);
• extract(week from date);
• extract(day from date);
• extract(hour from date);
• extract(minute from date);
• extract(second from date);
• extract(microsecond from date);
• extract(year_month from date);
• extract(day_hour from date);
• extract(day_minute from date);
• extract(day_second from date);
• extract(day_microsecond from date);
• extract(hour_minute from date);
• extract(hour_second from date);
• extract(hour_microsecond from date);
• extract(minute_second from date);
• extract(minute_microsecond from date);
• extract(second_microsecond from date);

注意：MySQL Extract() 函数除了没有date(),time()的功能外，其他功能一应具全。并且还具有选取‘day_microsecond’等功能。注意这里不是只选取 day 和 microsecond，而是从日期的 day 部分一直选取到 microsecond 部分

9.dayofweek(), dayofmonth(), dayofyear()...

SELECT dayofweek(NOW()),dayofmonth(NOW()), dayofyear(NOW());

image.png

9.2 MySQL week… 函数：week(), weekofyear(), dayofweek(), weekday(), yearweek()

SELECT WEEK(NOW()),WEEKOFYEAR(NOW()) ,weekday(NOW()),dayofweek(NOW()), yearweek(NOW());

result

MySQL week() 函数，可以有两个参数，具体可看手册。 weekofyear() 和 week() 一样，都是计算“某天”是位于一年中的第几周。 weekofyear(@dt) 等价于 week(@dt,3)。
MySQL weekday()函数和dayofweek()类似，都是返回“某天”在一周中的位置。不同点在于参考的标准， weekday：(0 =Monday, 1 = Tuesday, …, 6 = Sunday)； dayofweek：（1 = Sunday, 2 = Monday,…, 7 = Saturday）

WEEK(date[,mode])函数:

如：

SELECT weekofyear(NOW()),week(NOW(),3),week(NOW())

result

9.3 MySQL 返回星期和月份名称函数：dayname(), monthname()

SELECT dayname(NOW()),monthname(NOW())

result

10 msyql增加日期date_add() ，减少日期date_sub()

date_add() 可以替换adddate(),addtime()
date_sub()可以替换subdate(), subtime()
建议使用date_add() 和date_sub()

增加1天1小时1分1秒

SELECT date_add("2000-2-2",interval "1 1:1:1" DAY_SECOND)

result

对照表data(date,interval expr unit)

mysql.png

SELECT date_add("2000-2-2",interval "-1 -1:-1:-1" DAY_SECOND)
相同
SELECT date_sub("2000-2-2",interval "1 1:1:1" DAY_SECOND)

date_sub()与date_add()类似，不再赘述

11.MySQL 日期函数：period_add(P,N), period_diff(P1,P2)

函数参数“P” 的格式为“YYYYMM” 或者 “YYMM”，第二个参数“N” 表示增加或减去 N month（月）。

日期加/减去N月

select period_add(20190202,1), period_add(20190202,-1);

result

日期 P1-P2，返回 N 个月

select period_diff(201901, 201905);

result

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

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

两个日期相减 date1 - date2返回天数

select datediff('20190101', '20190201');
select datediff('2019-01-01', '2019-02-01')

result

11.3 转换函数：time_to_sec(time), sec_to_time(seconds)

select time_to_sec('01:01:01'),sec_to_time(3661);

result

11.4 转换函数：to_days(date), from_days(days)

select to_days('2019-01-01'),from_days(737425);

result

11.5 MySQL 获得国家地区时间格式函数：get_format()

MySQL get_format() 语法：

get_format(date|time|datetime, 'eur'|'usa'|'jis'|'iso'|'internal')

示例：

• get_format(date,'usa') ; -- '%m.%d.%Y'
• get_format(date,'jis') ; -- '%Y-%m-%d'
• get_format(date,'iso') ; -- '%Y-%m-%d'
• get_format(date,'eur') ; -- '%d.%m.%Y'
• get_format(date,'internal') ; -- '%Y%m%d'
• get_format(datetime,'usa') ; -- '%Y-%m-%d %H.%i.%s'
• get_format(datetime,'jis') ; -- '%Y-%m-%d %H:%i:%s'
• get_format(datetime,'iso') ; -- '%Y-%m-%d %H:%i:%s'
• get_format(datetime,'eur') ; -- '%Y-%m-%d %H.%i.%s'
• get_format(datetime,'internal') ; -- '%Y%m%d%H%i%s'
• get_format(time,'usa') ; -- '%h:%i:%s %p'
• get_format(time,'jis') ; -- '%H:%i:%s'
• get_format(time,'iso') ; -- '%H:%i:%s'
• get_format(time,'eur') ; -- '%H.%i.%s'
• get_format(time,'internal') ; -- '%H%i%s'

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

select makedate(2019,33),maketime(1,1,1)

result

11.7 MySQL （Unix 时间戳、日期）转换函数：

• unix_timestamp(),
• unix_timestamp(date),
• from_unixtime(unix_timestamp),
• from_unixtime(unix_timestamp,format)

select unix_timestamp(),unix_timestamp('2019-01-01'),from_unixtime(1570080832),from_unixtime(1570080832, '%Y %D %M %h:%i:%s %x');

result

11.8 MySQL 时间戳（timestamp）转换、增、减函数

• timestamp(date) – date to timestamp
• timestamp(datetime,time) – datetime + time
• timestampadd(interval,int_expr,datetime_expr)
• timestampdiff(interval,datetime_expr1,datetime_expr2)

interval 参数:

• FRAC_SECOND：表示间隔是毫秒
• SECOND：秒
• MINUTE：分钟
• HOUR：小时
• DAY：天
• WEEK：星期
• MONTH：月
• QUARTER：季度
• YEAR：年

select timestamp('2000-01-01'),timestamp('2000-01-01 01:00:00', '01:01:01'),timestamp('2000-01-01 01:00:00', '01 01:01:01'),timestampadd(day, 1, '2000-01-01 01:00:00');

result

MySQL timestampadd() 函数类似于 date_add()

select timestampadd(day, 1, '2000-01-01 01:00:00'), date_add('2000-01-01 01:00:00', interval 1 day);

result

select timestampdiff(year,'2000-01-01','2001-01-11'),datediff('2000-02-01','2000-01-01');

result

MySQL timestampdiff() 函数就比 datediff() 功能强多了，datediff() 只能计算两个日期（date）之间相差的天数

12. 生产示例

12.1

t_order.sql

CREATE TABLE `t_order`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单id',
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '订单名称',
  `createTime` datetime(0) DEFAULT NULL COMMENT '创建时间',
  `count` int(11) DEFAULT NULL COMMENT '订单数量',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_order
-- ----------------------------
INSERT INTO `t_order` VALUES (1, '订单1', '2016-02-02 14:02:59', 10);
INSERT INTO `t_order` VALUES (2, '订单2', '2016-04-01 14:02:59', 100);
INSERT INTO `t_order` VALUES (3, '订单3', '2016-07-08 14:02:59', 200);
INSERT INTO `t_order` VALUES (4, '订单4', '2018-01-01 14:06:06', 100);
INSERT INTO `t_order` VALUES (5, '订单5', '2019-01-01 14:02:59', 123);
INSERT INTO `t_order` VALUES (6, '订单6', '2019-04-30 14:02:59', 45);
INSERT INTO `t_order` VALUES (7, '订单7', '2019-09-25 14:02:59', 66);
INSERT INTO `t_order` VALUES (8, '订单8', '2019-11-11 14:02:59', 33);
INSERT INTO `t_order` VALUES (9, '订单9', '2019-12-18 14:02:59', 26);
INSERT INTO `t_order` VALUES (10, '订单10', '2019-10-24 14:04:44', 26);

1. 查询2018-01-01后的订单包括2018-01-01
SELECT * FROM t_order where createTime>="2018-01-01"

2.查询2018-01-01后的订单包括2018-01-01总数量
SELECT SUM(count) FROM t_order where createTime>="2018-01-01" 

3.查询每年的订单
- MySQL: SUBSTR( ), SUBSTRING( )
- Oracle: SUBSTR( )
- SQL Server: SUBSTRING( )

SELECT SUBSTR(createTime,1,4),SUM(count) FROM t_order  GROUP  BY SUBSTR(createTime,1,4)
SELECT YEAR(createTime),SUM(count) FROM t_order  GROUP  BY YEAR(createTime)

4.统计2019年每个月的订单数
SELECT MONTH(createTime),SUM(count) FROM t_order  where YEAR(createTime)="2019" GROUP BY MONTH(createTime)
SELECT SUBSTR(createTime,1,7),SUM(count) FROM t_order  where YEAR(createTime)="2019" GROUP BY SUBSTR(createTime,1,7)
SELECT MONTH(createTime),SUM(count) FROM t_order  where YEAR(createTime)="2019" GROUP BY MONTH(createTime)
SELECT SUBSTR(createTime,6,2),SUM(count) FROM t_order  where YEAR(createTime)="2019" GROUP BY SUBSTR(createTime,6,2)
SELECT CONCAT( YEAR ( createTime ), "年", MONTH ( createTime ), "月" ),SUM( count ) FROM t_order WHERE YEAR ( createTime ) = "2019" GROUP BY CONCAT( YEAR ( createTime ), "年", MONTH ( createTime ), "月" )

5.统计2019年每个季度的订单数
SELECT QUARTER(createTime),SUM(count) FROM t_order  where YEAR(createTime)="2019" GROUP BY QUARTER(createTime)

6.按天统计
SELECT CONCAT(YEAR(createTime),"-",MONTH(createTime),"-",DAY(createTime)),SUM(count) FROM t_order GROUP BY CONCAT(YEAR(createTime),"-",MONTH(createTime),"-",DAY(createTime))
SELECT SUBSTR(createTime,1,10),SUM(count) FROM t_order GROUP BY SUBSTR(createTime,1,10)

参考博客MySQL 有关当前日期及日期格式的操作(大全)