1.CURRENT_DATE

CURDATE()、CURRENT_DATE()漱牵、CURRENT_DATE

SELECT CURDATE(),CURRENT_DATE(),CURRENT_DATE;

結(jié)果：

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.獲得當(dāng)前 UTC 日期時(shí)間函數(shù)：utc_date(), utc_time(), utc_timestamp()

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

result

5. NOW()與SYSDATE() 區(qū)別

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

result

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

result

總結(jié)： now() 在執(zhí)行開始時(shí)值就得到了， sysdate() 在函數(shù)執(zhí)行時(shí)動(dòng)態(tài)得到值发钝。真正的系統(tǒng)時(shí)間顿涣，不受mysql 的SLEEP()等函數(shù)的影響。

6. 格式化日期 （日期轉(zhuǎn)字符串） DATE_FORMAT(date, format)

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

result

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

• %S, %s 兩位數(shù)字形式的秒（ 00,01, …, 59）
• %I, %i 兩位數(shù)字形式的分（ 00,01, …, 59）
• %H 兩位數(shù)字形式的小時(shí)酝豪，24 小時(shí)（00,01, …, 23）
• %h 兩位數(shù)字形式的小時(shí)涛碑，12 小時(shí)（01,02, …, 12）
• %k 數(shù)字形式的小時(shí)，24 小時(shí)（0,1, …, 23）
• %l 數(shù)字形式的小時(shí)孵淘，12 小時(shí)（1, 2, …, 12）
• %T 24 小時(shí)的時(shí)間形式（hh:mm:ss）
• %r 12 小時(shí)的時(shí)間形式（hh:mm:ss AM 或hh:mm:ss PM）
• %p AM或PM
• %W 一周中每一天的名稱（Sunday, Monday, …, Saturday）
• %a 一周中每一天名稱的縮寫（Sun, Mon, …, Sat）
• %d 兩位數(shù)字表示月中的天數(shù)（00, 01,…, 31）
• %e 數(shù)字形式表示月中的天數(shù)（1, 2蒲障， …, 31）
• %D 英文后綴表示月中的天數(shù)（1st, 2nd, 3rd,…）
• %w 以數(shù)字形式表示周中的天數(shù)（ 0 = Sunday, 1=Monday, …, 6=Saturday）
• %j 以三位數(shù)字表示年中的天數(shù)（ 001, 002, …, 366）
• %U 周（0, 1, 52），其中Sunday 為周中的第一天
• %u 周（0, 1, 52）瘫证，其中Monday 為周中的第一天
• %M 月名（January, February, …, December）
• %b 縮寫的月名（ January, February,…., December）
• %m 兩位數(shù)字表示的月份（01, 02, …, 12）
• %c 數(shù)字表示的月份（1, 2, …., 12）
• %Y 四位數(shù)字表示的年份
• %y 兩位數(shù)字表示的年份
• %% 直接值“%”

6.2 STR_TO_DATE（字符串轉(zhuǎn)日期）

SELECT STR_TO_DATE("5月30號(hào) 2019年","%m月%d號(hào) %Y年")

result

7.選取日期時(shí)間的各個(gè)部分

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

選取日期函數(shù)如下：

• 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() 函數(shù)

• 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() 函數(shù)除了沒有date(),time()的功能外揉阎，其他功能一應(yīng)具全。并且還具有選取‘day_microsecond’等功能背捌。注意這里不是只選取 day 和 microsecond毙籽，而是從日期的 day 部分一直選取到 microsecond 部分

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

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

image.png

9.2 MySQL week… 函數(shù)：week(), weekofyear(), dayofweek(), weekday(), yearweek()

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

result

MySQL week() 函數(shù)，可以有兩個(gè)參數(shù)毡庆，具體可看手冊(cè)坑赡。 weekofyear() 和 week() 一樣，都是計(jì)算“某天”是位于一年中的第幾周么抗。 weekofyear(@dt) 等價(jià)于 week(@dt,3)毅否。
MySQL weekday()函數(shù)和dayofweek()類似，都是返回“某天”在一周中的位置蝇刀。不同點(diǎn)在于參考的標(biāo)準(zhǔn)螟加， weekday：(0 =Monday, 1 = Tuesday, …, 6 = Sunday)； dayofweek：（1 = Sunday, 2 = Monday,…, 7 = Saturday）

WEEK(date[,mode])函數(shù):

如：

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

result

9.3 MySQL 返回星期和月份名稱函數(shù)：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小時(shí)1分1秒

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

result

對(duì)照表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 日期函數(shù)：period_add(P,N), period_diff(P1,P2)

函數(shù)參數(shù)“P” 的格式為“YYYYMM” 或者 “YYMM”甸昏，第二個(gè)參數(shù)“N” 表示增加或減去 N month（月）。

日期加/減去N月

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

result

日期 P1-P2徐许，返回 N 個(gè)月

select period_diff(201901, 201905);

result

MySQL 日期施蜜、時(shí)間相減函數(shù)：datediff(date1,date2), timediff(time1,time2)

11.2 .MySQL 日期、時(shí)間相減函數(shù)：datediff(date1,date2), timediff(time1,time2)

兩個(gè)日期相減 date1 - date2返回天數(shù)

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

result

11.3 轉(zhuǎn)換函數(shù)：time_to_sec(time), sec_to_time(seconds)

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

result

11.4 轉(zhuǎn)換函數(shù)：to_days(date), from_days(days)

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

result

11.5 MySQL 獲得國(guó)家地區(qū)時(shí)間格式函數(shù)：get_format()

MySQL get_format() 語(yǔ)法：

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 拼湊日期雌隅、時(shí)間函數(shù)：makdedate(year,dayofyear), maketime(hour,minute,second)

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

result

11.7 MySQL （Unix 時(shí)間戳翻默、日期）轉(zhuǎn)換函數(shù)：

• 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 時(shí)間戳（timestamp）轉(zhuǎn)換、增恰起、減函數(shù)

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

interval 參數(shù):

• FRAC_SECOND：表示間隔是毫秒
• SECOND：秒
• MINUTE：分鐘
• HOUR：小時(shí)
• 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() 函數(shù)類似于 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() 函數(shù)就比 datediff() 功能強(qiáng)多了修械，datediff() 只能計(jì)算兩個(gè)日期（date）之間相差的天數(shù)

12. 生產(chǎn)示例

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 '創(chuàng)建時(shí)間',
  `count` int(11) DEFAULT NULL COMMENT '訂單數(shù)量',
  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總數(shù)量
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.統(tǒng)計(jì)2019年每個(gè)月的訂單數(shù)
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.統(tǒng)計(jì)2019年每個(gè)季度的訂單數(shù)
SELECT QUARTER(createTime),SUM(count) FROM t_order  where YEAR(createTime)="2019" GROUP BY QUARTER(createTime)

6.按天統(tǒng)計(jì)
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 有關(guān)當(dāng)前日期及日期格式的操作(大全)