日常应用程序不使用日期和时间格式存储数据,但往往会存在使用日期读取、统计和处理的场景。日期的处理和时间的换算是本次主要议题。
一 常用MySQL的日期时间处理函数
函数 | 说明 |
---|---|
AddDate() | 增加一个日期(天,周等) |
AddTime() | 增加一个时间(时,分等) |
CurDate() | 返回当前日期 |
CurTime() | 返回当前时间 |
Date() | 返回时间的日期部分 |
DateDiff() | 计算日期只差 |
Date_Add() | 日期运算函数 |
Date_Formate() | 返回格式化的日期或时间字符串 |
Day() | 返回日期的天数部分 |
DayOfWeek() | 一周对应的星期几 |
Hour() | 返回时间的小时部分 |
Minute() | 返回时间的分钟部分 |
Month() | 返回时间的月份 |
Now() | 返回当前日期和时间 |
Second() | 返回时间的秒部分 |
Time() | 返回时间的时间部分 |
Year() | 返回日期的年部分 |
举例:
select u_id,order_id
from orders
where
Date(order_date)='2020-01-01'
二 日期周别的判断
现常使用的日期所在周别判断标准,是国际标准ISO 8601,即以该年的第一个星期四的周为该年的第 1 周,通常称作欧洲周编号机制。详细的规则,可参考:国际标准计算时间方法
2.1 判断第一个日历星期的方法
判断第一个日历星期四种等效说法:
1,本年度第一个星期四所在的星期;
2,1月4日所在的星期;
3,本年度第一个至少有4天在同一星期内的星期;
4,星期一在去年12月29日至今年1月4日以内的星期;
推理可得:
如果1月1日是星期一、星期二、星期三或者星期四,它所在的星期就是第一个日历星期;
如果1月1日是星期五、星期六或者星期日,它所在的星期就是上一年第52或者53个日历星期;
12月28日总是在一年最后一个日历星期
2.2 利用Excel判断当前日历所在周别
在Excel里,有两个函数可辅助判断
1. weeknum(A1,n)
描述:用来判断本周是本年的第几周,有两种判断机制:
机制1:当参数n=1,以1 月 1 日起为该年的第 1 周
机制2:当参数n=2,以该年的第一个星期四的周为该年的第 1 周
2.weekday(A1,n)
描述:用来判断今天是这周的周几,取值结果,和参数n的取值有关,n的取值包括如下:
注意:
- 截图使用参考软件:WPS
- Excel和WPS可能会有一些差异,未进行实际比对验证
在Excel中weeknum函数得出的周别,理应按照机制2算出来,但是若1月1日是星期五、星期六或者星期日,它所在的星期就是上一年第52或者53个日历星期,在Excel中计算就会出现问题,例如:
日期 | Excel周别(机制1) | Excel 周别(机制2) | 周几 | 实际IOS标准周别 |
---|---|---|---|---|
2021-01-01 | 1 | 1 | 5 | 53(上一年) |
2021-01-02 | 1 | 1 | 6 | 53(上一年) |
2021-01-03 | 2 | 1 | 7 | 53(上一年) |
2021-01-04 | 2 | 2 | 1 | 1 |
2021-01-05 | 2 | 2 | 2 | 1 |
2021-01-06 | 2 | 2 | 3 | 1 |
2021-01-07 | 2 | 2 | 4 | 1 |
2021-01-08 | 2 | 2 | 5 | 1 |
2021-01-09 | 2 | 2 | 6 | 1 |
2021-01-10 | 3 | 2 | 7 | 1 |
2021-01-11 | 3 | 3 | 1 | 2 |
2021-01-12 | 3 | 3 | 2 | 2 |
2021-01-13 | 3 | 3 | 3 | 2 |
2021-01-14 | 3 | 3 | 4 | 2 |
2021-01-15 | 3 | 3 | 5 | 2 |
2021-01-16 | 3 | 3 | 6 | 2 |
2021-01-17 | 4 | 3 | 7 | 2 |
2021-01-18 | 4 | 4 | 1 | 3 |
2021-01-19 | 4 | 4 | 2 | 3 |
如果机制2算出的周别不符合第52/53个日历星期,把该周别给减1