当前位置：首页 > news >正文

mysql对于上期同期的时间的处理

news 来源：原创 2024/9/19 14:58:21

文章目录

1、常用mysql函数
- 1.1、对日期标准处理
- 1.2、日期的加减
- 1.3、日期标准化输出
- 1.4、字符串
- 1.5、通过日计算周几
- 1.6、通过天计算该年的第几周
2、根据固定字符串格式计算出上期、同期相同格式的时间
- 2.1题目一
- - 上期求取
  - 同期求取
其他
- 闰年规则
- 周探讨（其一）
- 周探讨（其二）
- 周探讨（其三）

1、常用mysql函数

1.1、对日期标准处理

STR_TO_DATE('20240720','%Y%m%d')

1.2、日期的加减

负数就是减
DAY（日）、MONTH（月）、YEAR（年）
能识别 “20240720”、“2024-07-20”，两种日期字符串
```
DATE_ADD('20240720', INTERVAL 1 DAY) as a;
```

1.3、日期标准化输出

#变成20240801
SELECT DATE_FORMAT('2024-08-01', '%Y%m%d') as a;
#获取年2024
SELECT DATE_FORMAT('2024-05-01','%Y') as a;

1.4、字符串

截取年

#得到2024
SELECT SUBSTR('20240615',1,4) as a

1.5、通过日计算周几

DAYOFWEEK() 函数会返回一个介于 1 到 7 之间的整数，其中 1 表示星期日，2 表示星期一，依此类推，直到 7 表示星期六
```
SELECT DAYOFWEEK('20240615')
```

1.6、通过天计算该年的第几周

mysql中week()函数的用法

WEEK() 函数是 MySQL 中的一个日期和时间函数，它用来计算一个给定日期是一年中的第几周。WEEK() 函数有两种模式，可以通过第二个可选参数来指定：

#模式 0（默认）：一年中的第一周是包含一年中第一个星期日的那一周。
#模式 1：一年中的第一周是包含一年中第一个星期一的那一周。
SELECT WEEK('20240615',0)

2、根据固定字符串格式计算出上期、同期相同格式的时间

2.1题目一

时间格式总览

日20240615：表示2024年6月15日
周202401：表示2024年第一周
月202406：表示2024年6月
年2024：表示2024年

同期都是年维度

日20240615同期时间为：20230615
周202401同期时间为：202301

周计算规则

周一为一周的开始
每年的第一天必定为第一周：如果不是周一，则往前推到上一年的周一拼凑成一周
每年的最后一天：一定是这年的最后一周
假设每年的第一天不是周一：每年的最后一周和第二年的第一周其实是相同的时间

上期求取

日

#结果20240719
SELECT DATE_FORMAT(DATE_ADD('20240720', INTERVAL -1 DAY), '%Y%m%d') as a;

周

#结果202406
#对每年的第一周的上期计算方式：逻辑为判定上年的第一天是否为周日，如果是周日则为53周，如果为周六又同时是闰年则为53周，其余情况都是52周。具体解释看周探讨（其一）的详细解释
SELECT
IF(SUBSTR( '202407', 5 )= '01',IF(DAYOFWEEK(CONCAT( SUBSTR( '202407', 1, 4 )- 1, '0101' ))= 1,CONCAT( SUBSTR( '202407', 1, 4 )- 1, '53' ),IF(DAYOFWEEK(CONCAT( SUBSTR( '202407', 1, 4 )- 1, '0101' ))= 7 AND ((SUBSTR( '202407', 1, 4 )- 1 ) % 4 = 0 AND ( SUBSTR( '202407', 1, 4 )- 1 ) % 100 != 0 ) OR ( ( SUBSTR( '202407', 1, 4 )- 1 ) % 400 = 0 ),CONCAT( SUBSTR( '202407', 1, 4 )- 1, '53' ),CONCAT( SUBSTR( '202407', 1, 4 )- 1, '52' ))),'202407' - 1)  as a

月

#结果202406，如果传入202401，结果则为202312
SELECT if(SUBSTR('202407',5)='01',CONCAT(SUBSTR('202407',1,4)-1,'12'),'202407'-1)

年
```
#结果2023
SELECT '2024'-1 as a;
```

同期求取

日

#结果20230720
SELECT DATE_FORMAT(DATE_ADD('20240720', INTERVAL -1 YEAR), '%Y%m%d') as a;

周

#结果202327
SELECT '202427'-100 as a;

月

#结果202305
SELECT '202405'-100 as a;

年
```
#结果2023
SELECT '2024'-1 as a;
```

其他

闰年规则

普通闰年：如果一个年份能够被4整除，但不能被100整除，则它是闰年。
世纪闰年：如果一个年份能够被400整除，则它也是闰年，即使它可以被100整除。

(year % 4 = 0 AND year % 100 != 0) OR (year % 400 = 0)

周探讨（其一）

周一为一周的开始
每年的第一天必定为第一周：如果不是周一，则往前推到上一年的周一拼凑成一周
每年的最后一天必定为最后一周：如果不是周日，则向前推到明年的周日拼凑成一周
假设每年的第一天不是周一：去年的最后一周和今年的第一周其实是相同的时间

去年非闰年

去年的第一天	今年第一周的上期	说明
周一	52	因为去年最后一天为周一，虽然有53周，但是去年的第53周和今年的第一周是同一周
周二	52	最后一天为周二
周三	52	最后一天为周三
周四	52	最后一天为周四
周五	52	最后一天为周五
周六	52	最后一天为周六
周日	53	去年最后一天为周日，这一年有53周，但是去年最后一周不是今年的第一周

去年闰年

去年的第一天	今年第一周的上期	说明
周一	52	去年最后一天为周二，有53周，但是去年最后一周和今年的第一周重叠了
周二	52	最后一天为周三
周三	52	最后一天为周四
周四	52	最后一天为周五
周五	52	最后一天为周六
周六	53	周日，有53周，最后一周没有和第一周重叠
周日	53	周一，有54周，去年最后一周和今年的第一周重叠了

计算今年第一周的上期的公式为

#结果202353
#对每年的第一周的上期计算方式：逻辑为判定上年的第一天是否为周日，如果是周日则为53周，如果为周六又同时是闰年则为53周，其余情况都是52周	
SELECT
IF(SUBSTR( '202401', 5 )= '01',IF(DAYOFWEEK(CONCAT( SUBSTR( '202401', 1, 4 )- 1, '0101' ))= 1,CONCAT( SUBSTR( '202401', 1, 4 )- 1, '53' ),IF(DAYOFWEEK(CONCAT( SUBSTR( '202401', 1, 4 )- 1, '0101' ))= 7 AND ((SUBSTR( '202401', 1, 4 )- 1 ) % 4 = 0 AND ( SUBSTR( '202401', 1, 4 )- 1 ) % 100 != 0 ) OR ( ( SUBSTR( '202401', 1, 4 )- 1 ) % 400 = 0 ),CONCAT( SUBSTR( '202401', 1, 4 )- 1, '53' ),CONCAT( SUBSTR( '202401', 1, 4 )- 1, '52' ))),'202401' - 1)  as a

第二个公式（这个是凑巧和其三完全相同了）

#结果202353
#对每年的第一周的上期计算方式：使用WEEK函数取去年最后一天为第几周（函数模式为0，默认模式）	
SELECT IF(SUBSTR( '202401', 5 )= '01',CONCAT(SUBSTR( '202401', 1, 4 )- 1,WEEK(CONCAT(SUBSTR( '202401', 1, 4 )- 1,'1231'),0)),'202401'-1)

周探讨（其二）

周一为一周的开始
每年的第一天如果不是周一，则为第0周
每年的最后一天必定为最后一周：如果不是周日，则向前推到明年的周日拼凑成一周
假设每年的第一天不是周一：去年的最后一周和今年的第0周其实是相同的时间
因为每年未必有第0周，这里作者直接使用本年的第一周求上期

去年非闰年

去年的第一天	今年第一周的上期	说明
周一	53	去年最后一天为周一，所以有53周
周二	52	第一周为0周，所以只有52周
周三	52	第一周为0周，所以只有52周
周四	52	第一周为0周，所以只有52周
周五	52	第一周为0周，所以只有52周
周六	52	第一周为0周，所以只有52周
周日	52	第一周为0周，所以只有52周

去年闰年

去年的第一天	今年第一周的上期	说明
周一	53	第一天为周一，最后一天为周二，有53周
周二	52	第一周为0周，最后一天为周三，所以只有52周
周三	52	第一周为0周，最后一天为周四，所以只有52周
周四	52	第一周为0周，最后一天为周五，所以只有52周
周五	52	第一周为0周，最后一天为周六，所以只有52周
周六	52	第一周为0周，最后一天为周日，所以只有52周
周日	53	第一周为0周，最后一天为周一，有53周

计算今年第一周的上期的公式为

#结果202352
#对每年的第一周的上期计算方式：使用WEEK函数取去年最后一天为第几周（函数模式为1）	
SELECT IF(SUBSTR( '202401', 5 )= '01',CONCAT(SUBSTR( '202401', 1, 4 )- 1,WEEK(CONCAT(SUBSTR( '202401', 1, 4 )- 1,'1231'),1)),'202401'-1)

周探讨（其三）

周日为一周的开始
每年的第一天如果不是周日，则为第0周
每年的最后一天必定为最后一周：如果不是周六，则向前推到明年的周六拼凑成一周
假设每年的第一天不是周日：去年的最后一周和今年的第0周其实是相同的时间
因为每年未必有第0周，这里作者直接使用本年的第一周求上期

去年非闰年

去年的第一天	今年第一周的上期	说明
周一	52	第一周为0周，所以只有52周
周二	52	第一周为0周，所以只有52周
周三	52	第一周为0周，所以只有52周
周四	52	第一周为0周，所以只有52周
周五	52	第一周为0周，所以只有52周
周六	52	第一周为0周，所以只有52周
周日	53	第一周为1周，去年最后一天为周日，所以有53周

去年闰年

去年的第一天	今年第一周的上期	说明
周一	52	第一周为0周，最后一天为周二，所以只有52周
周二	52	第一周为0周，最后一天为周三，所以只有52周
周三	52	第一周为0周，最后一天为周四，所以只有52周
周四	52	第一周为0周，最后一天为周五，所以只有52周
周五	52	第一周为0周，最后一天为周六，所以只有52周
周六	53	第一周为0周，最后一天为周日，所以有53周
周日	53	第一周为1周，最后一天为周一，所以有53周

计算今年第一周的上期的公式为

#结果202353
#对每年的第一周的上期计算方式：使用WEEK函数取去年最后一天为第几周（函数模式为0，默认模式）	
SELECT IF(SUBSTR( '202401', 5 )= '01',CONCAT(SUBSTR( '202401', 1, 4 )- 1,WEEK(CONCAT(SUBSTR( '202401', 1, 4 )- 1,'1231'),0)),'202401'-1)