【MYSQL时间函数用法】

目录

1.date\datetime\time三个时间类型的区别

2.获取当前时间函数

3.查询日期范围

3.1  between and语句

3.2  datediff返回两个日期之间的天数

3.3  time stampdiff函数

4.格式化日期

5.从日期减去、加上指定的时间间隔

6.时间戳函数

6.1. unix_timestamp()

6.2. from_unixtime()

7.提取时间函数

7.1 date() 函数

7.2 extract函数

8. to_days函数

9.period_diff年份/月份值之间的月数差

10. 查询当天、本周，本月等日期数据

10.1  查询当天数据

10.2 昨天数据

​ 10.3 近7天

1.date\datetime\time三个时间类型的区别

函数	描述
now()=current_timestamp()	return 当前的日期和时间	2023-12-08 14:16:57
curdate()	return 当前的日期	2023-12-08
curtime()	return 当前的时间	14:18:45
date('2023-12-08 14:16:57')	提取日期或日期/时间表达式的日期部分	2023-12-08
extract(）	return 日期/时间的单独部分
date_add()	向日期添加指定的时间间隔
date_sub()	从日期减去指定的时间间隔
datediff()	return 两个日期之间的天数
date_format()	用不同的格式显示日期/时间
to_days	返回从0000年（公元1年）至当前日期的总天数。
period_diff（）

2.获取当前时间函数

select now() =select current_timestamp() -- 年与日 时分秒

select curdate() --年与日

select curtime() --时分秒

year & month & day

–hour()时 minute()分 second()秒

select now(),hour(now()),minute(now()),second(now()) from emp ;

–year()年 month()月 day()日

select now(),year(now()),month(now()),day(now()) from emp ；

3.查询日期范围

• 大致有三种方式
• between and语句；
• datediff函数；
• Time stampdiff函数；

3.1  between and语句

select * from dat_document where commit_date between '2018-07-01' and '2018-07-04' 

 结果是1号到3号的数据，这是因为时间范围显示的实际上只是‘2018-07-01 00:00:00’到'2018-07-04 00:00:00'之间的数据，而'2018-07-04'的数据就无法显示出来,between and对边界还需要进行额外的处理.

3.2  datediff返回两个日期之间的天数

 datediff函数返回两个日期之间的天数

     语法：DATEDIFF(date1,date2)

SELECT DATEDIFF('2018-07-01','2018-07-04');

 运行结果:-3

    所以，datediff函数对时间差值的计算方式为date1-date2的差值。

3.3  time stampdiff函数

  Time stampdiff函数日期或日期时间表达式之间的整数差。

语法：TIMESTAMPDIFF(interval，datetime1，datetime2)，比较的单位interval可以为以下数值

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

select TIMESTAMPDIFF(DAY,'2018-07-01 09:00:00','2018-07-04 12:00:00');

 运行结果:3

    所以，timestampdiff函数对日期差值的计算方式为datetime2-datetime1的差值。

    请注意：DATEDIFF，TIMESTAMPDIFF对日期差值的计算方式刚好是相反的。

4.格式化日期

date_forma t(date, format)

DATE_FORMA T(date, format) 根据格式串format 格式化日期或日期和时间值date，返回结果串。

可用DATE_FORMAT( ) 来格式化DATE 或DATETIME 值，以便得到所希望的格式。

根据format字符串格式化date值:

%S, %s 两位数字形式的秒( 00,01, . . ., 59)

%i 两位数字形式的分( 00,01, . . ., 59)

%H 两位数字形式的小时，24 小时(00,01, . . ., 23)

%h, %I 两位数字形式的小时，12 小时(01,02, . . ., 12)

%k 数字形式的小时，24 小时(0,1, . . ., 23)

%l 数字形式的小时，12 小时(1, 2, . . ., 12)

%T 24 小时的时间形式(h h : m m : s s)

%r 12 小时的时间形式(hh:mm:ss AM 或hh:mm:ss PM)

%p AM 或P M

%W 一周中每一天的名称( S u n d a y, Monday, . . ., Saturday)

%a 一周中每一天名称的缩写( Sun, Mon, . . ., Sat)

%d 两位数字表示月中的天数( 00, 01, . . ., 31)

%e 数字形式表示月中的天数( 1, 2， . . ., 31)

%D 英文后缀表示月中的天数( 1st, 2nd, 3rd, . . .)

%w 以数字形式表示周中的天数( 0 = S u n d a y, 1=Monday, . . ., 6=Saturday)

%j 以三位数字表示年中的天数( 001, 002, . . ., 366)

% U 周(0, 1, 52)，其中Sunday 为周中的第一天

%u 周(0, 1, 52)，其中Monday 为周中的第一天

%M 月名(J a n u a r y, February, . . ., December)

%b 缩写的月名( J a n u a r y, February, . . ., December)

%m 两位数字表示的月份( 01, 02, . . ., 12)

%c 数字表示的月份( 1, 2, . . ., 12)

%Y 四位数字表示的年份

%y 两位数字表示的年份

%% 直接值“%”

select date_format('2023-12-07 20:26:59','%Y-%m-%d') 

5.从日期减去、加上指定的时间间隔

date_sub(date,interval expr type)，给日期减少指定的时间间隔。

date_add(date,interval expr type)，给日期添加指定的时间间隔。

select date_sub('2023-12-07 20:26:59', interval 1 day);

参数：

date 参数是合法的日期表达式；

expr 参数是您希望添加的时间间隔数量；

type 参数的值如下：

MICROSECOND：毫秒    

SECOND：秒

MINUTE：分钟

HOUR：小时

DAY：天

WEEK：周

MONTH：月

QUARTER：一刻钟

YEAR：年

6.时间戳函数

6.1. unix_timestamp()

1、unix_timestamp() 得到当前时间戳

2、如果参数date满足yyyy-MM-dd HH:mm:ss形式，则可以直接unix_timestamp(string date) 得到参数对应的时间戳

3、如果参数date不满足yyyy-MM-dd HH:mm:ss形式，则我们需要指定date的形式，在进行转换.

6.2. from_unixtime()

语法：from_unixtime(t1,’yyyy-MM-dd HH:mm:ss’)

其中t1是10位的时间戳值，即1970-1-1至今的秒，而13位的所谓毫秒的是不可以的。

对于13位时间戳，需要截取，然后转换成bigint类型，因为from_unixtime类第一个参数只接受bigint类型。例如：

7.提取时间函数

7.1 date() 函数

提取日期或日期/时间表达式的日期部分。

select  date('2023-12-08 14:16:57')

• date()：提取当前日期
• year()：提取当前年份
• month()：提取当前月份
• day()：提取当前日期
• hour()：提取当前小时
• minute()：提取当前分钟
• second()：提取当前秒

7.2 extract函数

extract(unit from date)；

extract()函数需要两个参数：unit和date。

unit是要从日期中提取的间隔。 以下是unit参数的有效间隔。

• day
• day_hour
• day_microsecond
• day_minute
• day_second
• hour
• hour_microsecond
• hour_minute
• hour_second
• microsecond
• minute
• minute_microsecond
• minute_second
• month
• quarter
• second
• second_microsecond
• week
• year
• year_month

date是date或datetime值，从中提取间隔的日期。

从日期时间提取日期：

 select extract(day from '2017-07-14 09:04:44') dayhour

8. to_days函数

to_days函数：返回从0000年（公元1年）至当前日期的总天数。就是把时间转化成天数.

 select  to_days(now()) as 从0000年（公元1年）至当前日期的总天数

9.period_diff年份/月份值之间的月数差

period_diff()返回两个年份/月份值之间的月数差

即：用于计算用于表示年份和月份的 yymm 或 yyyymm 值之间的月份差。它接受两个参数 - yyyymm 格式的两个日期.

该函数的语法如下：

period_diff(p1,p2)

p1 和 p2 是 YYYYMM 格式的两个日期值。

注意：period_diff() 函数的返回值是一个整数，表示两个日期之间的月份差。

10. 查询当天、本周，本月等日期数据

CREATE TABLE `t_product_stock_file` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `url` varchar(500) NOT NULL,
  `parent_sku` varchar(50) DEFAULT NULL COMMENT '父sku',
  `parent_sku_id` int DEFAULT NULL COMMENT '父skuid',
  `created_by` int NOT NULL,
  `created_time` datetime NOT NULL,
  `created_by_name` varchar(100) NOT NULL,
  `main` tinyint(1) DEFAULT '1' COMMENT '1.主图0.附图2.子sku3.质检图4.实拍图5父sku的实拍图',
  `main_sub` tinyint(1) DEFAULT NULL COMMENT 'main的每个细分类型（1.父sku的实拍图【资质认证】',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT COMMENT='商品库对应的图片或视频';

INSERT INTO `a-test`.`t_product_stock_file` (`id`, `url`, `parent_sku`, `parent_sku_id`, `created_by`, `created_time`, `created_by_name`, `main`, `main_sub`) VALUES (7005652, 'https://tk.longpean.com/86055certification-upload-1701679501069.jpeg', 'A9S63', 125694, 529, '2023-12-08 16:45:05', '张1', 5, 1);
INSERT INTO `a-test`.`t_product_stock_file` (`id`, `url`, `parent_sku`, `parent_sku_id`, `created_by`, `created_time`, `created_by_name`, `main`, `main_sub`) VALUES (7005858, 'https://tk.longpean.com/201561701766193520.jpeg?imageMogr2/thumbnail/1000x1000!', 'B3X7F', 146102, 1545, '2023-12-08 16:50:05', '张2', 5, 1);
INSERT INTO `a-test`.`t_product_stock_file` (`id`, `url`, `parent_sku`, `parent_sku_id`, `created_by`, `created_time`, `created_by_name`, `main`, `main_sub`) VALUES (7006119, 'https://tk.longpean.com/728971701844920956.png?imageMogr2/thumbnail/1000x1000!', 'B3A9L', 142109, 1545, '2023-12-06 14:42:10', '李华', 5, 1);
INSERT INTO `a-test`.`t_product_stock_file` (`id`, `url`, `parent_sku`, `parent_sku_id`, `created_by`, `created_time`, `created_by_name`, `main`, `main_sub`) VALUES (7006145, 'https://tk.longpean.com/332991701857432928.png?imageMogr2/thumbnail/1000x1000!', 'B3H1Z', 143250, 1545, '2023-12-06 18:10:49', '李华', 5, 1);
INSERT INTO `a-test`.`t_product_stock_file` (`id`, `url`, `parent_sku`, `parent_sku_id`, `created_by`, `created_time`, `created_by_name`, `main`, `main_sub`) VALUES (7006342, 'https://tk.longpean.com/732161701931826729.png?imageMogr2/thumbnail/1000x1000!', 'B2Z7S', 140092, 1545, '2023-12-06 14:50:33', '李华', 5, 1);
INSERT INTO `a-test`.`t_product_stock_file` (`id`, `url`, `parent_sku`, `parent_sku_id`, `created_by`, `created_time`, `created_by_name`, `main`, `main_sub`) VALUES (7006427, 'https://tk.longpean.com/93031701938793270.png?imageMogr2/thumbnail/1000x1000!', 'A5V4H', 98198, 1545, '2023-12-07 16:46:39', '李华', 5, 1);
INSERT INTO `a-test`.`t_product_stock_file` (`id`, `url`, `parent_sku`, `parent_sku_id`, `created_by`, `created_time`, `created_by_name`, `main`, `main_sub`) VALUES (7006441, 'https://tk.longpean.com/241961701938831904.png?imageMogr2/thumbnail/1000x1000!', 'B2Y5Q', 139868, 1545, '2023-12-07 16:47:18', '李华', 5, 1);
INSERT INTO `a-test`.`t_product_stock_file` (`id`, `url`, `parent_sku`, `parent_sku_id`, `created_by`, `created_time`, `created_by_name`, `main`, `main_sub`) VALUES (7006453, 'https://tk.longpean.com/271521701938907476.png?imageMogr2/thumbnail/1000x1000!', 'B1B4Z', 129304, 1545, '2023-12-07 16:48:40', '李华', 5, 1);
INSERT INTO `a-test`.`t_product_stock_file` (`id`, `url`, `parent_sku`, `parent_sku_id`, `created_by`, `created_time`, `created_by_name`, `main`, `main_sub`) VALUES (7006509, 'https://tk.longpean.com/36781701941253214.jpeg?imageMogr2/thumbnail/1000x1000!', 'B3F9Z', 143013, 1491, '2023-12-07 17:28:21', '李华', 5, 1);
INSERT INTO `a-test`.`t_product_stock_file` (`id`, `url`, `parent_sku`, `parent_sku_id`, `created_by`, `created_time`, `created_by_name`, `main`, `main_sub`) VALUES (7006543, 'https://tk.longpean.com/75931701941336994.jpeg', 'B3Z6P', 146468, 1524, '2023-12-07 17:52:09', '李华', 5, 1);
INSERT INTO `a-test`.`t_product_stock_file` (`id`, `url`, `parent_sku`, `parent_sku_id`, `created_by`, `created_time`, `created_by_name`, `main`, `main_sub`) VALUES (7006577, 'https://tk.longpean.com/120151701944173275.png?imageMogr2/thumbnail/1000x1000!', 'B2973', 141916, 1545, '2023-12-07 18:16:20', '李华', 5, 1);
INSERT INTO `a-test`.`t_product_stock_file` (`id`, `url`, `parent_sku`, `parent_sku_id`, `created_by`, `created_time`, `created_by_name`, `main`, `main_sub`) VALUES (7006595, 'https://tk.longpean.com/296231702005058453.png?imageMogr2/thumbnail/1000x1000!', 'A7E49', 108502, 1545, '2023-12-08 11:11:43', '李华', 5, 1);
INSERT INTO `a-test`.`t_product_stock_file` (`id`, `url`, `parent_sku`, `parent_sku_id`, `created_by`, `created_time`, `created_by_name`, `main`, `main_sub`) VALUES (7006650, 'https://tk.longpean.com/824351702013120016.jpeg', 'A7Q12', 147086, 204, '2023-12-08 13:25:20', '李华', 5, 1);

10.1  查询当天数据

select * from 数据库表名 where to_days(时间字段名) = to_days(now());

to_days(now())=to_days(created_time)

= to_days(now()) - to_days(created_time) <1

=to_days(now()) - to_days(created_time) =0

select 
  `id` , `parent_sku` '父sku',
	created_time,NOW(),TO_DAYS(created_time),TO_DAYS(NOW())
from t_product_stock_file 
 where main_sub =1
 and  TO_DAYS(NOW()) - TO_DAYS(created_time) <1


select 
  `id` , `parent_sku` '父sku',
	created_time,NOW(),TO_DAYS(created_time),TO_DAYS(NOW())
from t_product_stock_file 
 where main_sub =1
 and  TO_DAYS(NOW())=TO_DAYS(created_time)

10.2 昨天数据

包含当天得数据：
select * from 表名 where to_days( now( ) ) – to_days( 时间字段名) <= 1;

select 
  `id` , `parent_sku` '父sku',
	created_time,NOW(),TO_DAYS(created_time),TO_DAYS(NOW())
from t_product_stock_file 
 where main_sub =1
 and  TO_DAYS(NOW()) - TO_DAYS(created_time) <=1

不包含今天的数据：

 select * from 表名 where to_days(now()) - to_days(createtime) = 1

select 
  `id` , `parent_sku` '父sku',
	created_time,NOW(),TO_DAYS(created_time),TO_DAYS(NOW())
from t_product_stock_file 
 where main_sub =1
 and  TO_DAYS(NOW()) - TO_DAYS(created_time) =1

 10.3 近7天

select * from 表名 where date_sub(curdate(), interval 7 day) <= date(时间字段名)

近30天:
select * from 表名 where date_sub(curdate(), interval 30 day) <= date(时间字段名)

本月:
select * from 表名 where date_format( 时间字段名, ‘%y%m' ) = date_format( curdate( ) , ‘%y%m' )

上一月:
select * from 表名 where period_diff( date_format( now( ) , ‘%y%m' ) , date_format( 时间字段名, ‘%y%m' ) ) =1

period_diff

查询本季度数据:
select * from `ht_invoice_information` where quarter(create_date)=quarter(now());

查询上季度数据:
select * from `ht_invoice_information` where quarter(create_date)=quarter(date_sub(now(),interval 1 quarter));

查询本年数据:
select * from `ht_invoice_information` where year(create_date)=year(now());

查询上年数据:
select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));