MySQL中的日期操作
作为一名数据分析人员，在工作中，经常会使用到用sql查询语言来进行取数据。由于作者在一家汽车金融公司供职，接触到海量的数据，为了提高取数效率，往往需要使用到时间函数。
今天作者就总结一下一些常用的获取时间的操作：
1、今天，即系统当前日期
CURDATE() 譬如今天是 2020-09-09
或者
DATE_SUB(CURDATE(), INTERVAL 0 DAY)
2、当前日期+时间
NOW() 譬如现在是 2020-09-09 10:39:52
SYSDATE() 譬如现在是 2020-09-09 10:39:52
3、昨天，即系统日期的前一天
DATE_SUB(CURDATE(), INTERVAL 1 DAY) 2020-09-09
DATE_SUB(NOW(), INTERVAL 1 DAY) 2020-09-08 10:39:52
4、近一周
DATE_SUB(CURDATE(), INTERVAL 7 DAY)
示例：
select *
from tablename
where
(time_ziduan)< DATE_SUB(CURDATE(), INTERVAL 7 DAY)
5、近30天
DATE_SUB(CURDATE(), INTERVAL 30 DAY)
示例：
类比4
6、本月
MONTH(CURDATE())
示例：
select *
from tablename
where
MONTH(time_ziduan)= MONTH(CURDATE())
7、上一月
MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
示例：
select *
from tablename
where
MONTH(time_ziduan)= MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
下面来一段示例：
#TIMESTAMPADD(month,1,'2020-01-01')指定日期加一个月
SELECT
#以下是a查询结果
a.`渠道`
,a.`1月放款`
,a.`2月放款`
,a.`3月放款`
,a.`4月放款`
,a.`5月放款`
,a.`6月放款`
,a.`7月放款`
,a.`8月放款`
,a.`9月放款`
,a.`10月放款`
,a.`11月放款`
,a.`12月放款`
,round((COALESCE(a.`累计放款金额`,0)-COALESCE(b.`累计应还款金额（本金）`,0)-COALESCE(a.`提前结清金额(本金)`,0)),2) as '在贷余额'
FROM
(
SELECT
STORE_NAME as '渠道'
,sum(case when YEAR(LEND_DATE) = 2020 and MONTH(LEND_DATE) = 1 then LEND_AMOUNT else 0 end ) as '1月放款'
,sum(case when YEAR(LEND_DATE) = 2020 and MONTH(LEND_DATE) = 2 then LEND_AMOUNT else 0 end ) as '2月放款'
,sum(case when YEAR(LEND_DATE) = 2020 and MONTH(LEND_DATE) = 3 then LEND_AMOUNT else 0 end ) as '3月放款'
,sum(case when YEAR(LEND_DATE) = 2020 and MONTH(LEND_DATE) = 4 then LEND_AMOUNT else 0 end ) as '4月放款'
,sum(case when YEAR(LEND_DATE) = 2020 and MONTH(LEND_DATE) = 5 then LEND_AMOUNT else 0 end ) as '5月放款'
,sum(case when YEAR(LEND_DATE) = 2020 and MONTH(LEND_DATE) = 6 then LEND_AMOUNT else 0 end ) as '6月放款'
,sum(case when YEAR(LEND_DATE) = 2020 and MONTH(LEND_DATE) = 7 then LEND_AMOUNT else 0 end ) as '7月放款'
,sum(case when YEAR(LEND_DATE) = 2020 and MONTH(LEND_DATE) = 8 then LEND_AMOUNT else 0 end ) as '8月放款'
,sum(case when YEAR(LEND_DATE) = 2020 and MONTH(LEND_DATE) = 9 then LEND_AMOUNT else 0 end ) as '9月放款'
,sum(case when YEAR(LEND_DATE) = 2020 and MONTH(LEND_DATE) = 10 then LEND_AMOUNT else 0 end ) as '10月放款'
,sum(case when YEAR(LEND_DATE) = 2020 and MONTH(LEND_DATE) = 11 then LEND_AMOUNT else 0 end ) as '11月放款'
,sum(case when YEAR(LEND_DATE) = 2020 and MONTH(LEND_DATE) = 12 then LEND_AMOUNT else 0 end ) as '12月放款'
,COUNT(APPLY_NO) as '累计放款量'
,COALESCE(sum(LEND_AMOUNT),0) as '累计放款金额'
,COALESCE(sum(case when SETTLEMENT = '提前结清' and TRANSDATE < date(concat(year(curdate()),'-',month(curdate()),'-','1'))
then PAYACTNORMAMT else 0 end),0) as '提前结清金额(本金)'
,count(case when SETTLEMENT='提前结清' and TRANSDATE < date(concat(year(curdate()),'-',month(curdate()),'-','1'))
then APPLY_NO else null end ) as '提前结清量'
,count(case when SETTLEMENT='正常结清' and TRANSDATE < date(concat(year(curdate()),'-',month(curdate()),'-','1'))
then APPLY_NO else null end ) as '正常结清量'
FROM
loaddetails
WHERE
LEND_DATE < date(concat(year(curdate()),'-',month(curdate()),'-','1')) # 获取本月的第1天
#date_sub(CURRENT_DATE,interval -1 day)
and
STORE_NAME not in('条件1','条件2')
GROUP BY `渠道`
ORDER BY `累计放款金额` DESC
)AS a
# 以上是a子查询 查询放款、结清数据
LEFT JOIN
# 以下是b子查询 查询代偿数据
(
SELECT
t.CHANNEL_NAME as '渠道',
COUNT(t.SQBH) AS '累计应还款量',
COUNT(CASE WHEN (t.COMPENSATORY= 1) THEN t.SQBH ELSE NULL END ) AS '累计代偿量',
COUNT(CASE WHEN (t.COMPENSATORY= 0) THEN t.SQBH ELSE NULL END ) AS '累计正常还款量',
SUM(t.CUR_PRINCIPAL) AS '累计应还款金额（本金）',
COALESCE(SUM(t.CUR_PRINCIPAL+t.CUR_INTEREST),0) AS '累计应还款金额（本息）',
COALESCE(SUM(CASE WHEN (t.COMPENSATORY= 1) THEN (t.CUR_PRINCIPAL+t.CUR_INTEREST) ELSE 0 END ),0) AS '累计代偿金额（本息）',
COALESCE(SUM(CASE WHEN (t.COMPENSATORY= 0) THEN (t.CUR_PRINCIPAL+t.CUR_INTEREST) ELSE 0 END ),0) AS '累计正常还款金额'
FROM
(
SELECT
distinct SERIAL_ID
,SQBH
,KHXM
,CHANNEL_NAME
,HTHM
,LOAN_ID
,REPAY_TIME
,REPAY_DATE
,CUR_PRINCIPAL
,CUR_INTEREST
,CUR_PENALTY
,CUR_PAYINTPENAMT
,ACTUAL_DATE
,ACT_PRINCIPAL
,ACT_INTEREST
,ACT_PENALTY
,ACT_PAYINTPENAMT
,COM_PRINCIPAL
,COM_INTEREST
,COM_PENALTY
,COM_PAYINTPENAMT
,COMPENSATORY
,ROFFERDATE
,CHARGE_STATUS
,REGISTEREDCITY
,REGISTEREDPROVINCE
FROM
detuctionsdetails
WHERE
REPAY_DATE < date(concat(year(curdate()),'-',month(curdate()),'-','1'))
#date_sub(CURRENT_DATE,interval 0 day)
AND
CHARGE_STATUS = 1
and
CHANNEL_NAME not in ('条件1','条件2')
)as t
GROUP BY `渠道`
) AS b
ON a.`渠道`=b.`渠道`
UNION
SELECT
concat('应回款本息金额')
,sum(case when YEAR(t.REPAY_DATE) = 2020 and MONTH(t.REPAY_DATE) = 1 then t.CUR_PRINCIPAL+t.CUR_INTEREST else 0 end ) as '1月还款'
,sum(case when YEAR(t.REPAY_DATE) = 2020 and MONTH(t.REPAY_DATE) = 2 then t.CUR_PRINCIPAL+t.CUR_INTEREST else 0 end ) as '2月还款'
,sum(case when YEAR(t.REPAY_DATE) = 2020 and MONTH(t.REPAY_DATE) = 3 then t.CUR_PRINCIPAL+t.CUR_INTEREST else 0 end ) as '3月还款'
,sum(case when YEAR(t.REPAY_DATE) = 2020 and MONTH(t.REPAY_DATE) = 4 then t.CUR_PRINCIPAL+t.CUR_INTEREST else 0 end ) as '4月还款'
,sum(case when YEAR(t.REPAY_DATE) = 2020 and MONTH(t.REPAY_DATE) = 5 then t.CUR_PRINCIPAL+t.CUR_INTEREST else 0 end ) as '5月还款'
,sum(case when YEAR(t.REPAY_DATE) = 2020 and MONTH(t.REPAY_DATE) = 6 then t.CUR_PRINCIPAL+t.CUR_INTEREST else 0 end ) as '6月还款'
,sum(case when YEAR(t.REPAY_DATE) = 2020 and MONTH(t.REPAY_DATE) = 7 then t.CUR_PRINCIPAL+t.CUR_INTEREST else 0 end ) as '7月还款'
,sum(case when YEAR(t.REPAY_DATE) = 2020 and MONTH(t.REPAY_DATE) = 8 then t.CUR_PRINCIPAL+t.CUR_INTEREST else 0 end ) as '8月还款'
,sum(case when YEAR(t.REPAY_DATE) = 2020 and MONTH(t.REPAY_DATE) = 9 then t.CUR_PRINCIPAL+t.CUR_INTEREST else 0 end ) as '9月还款'
,sum(case when YEAR(t.REPAY_DATE) = 2020 and MONTH(t.REPAY_DATE) = 10 then t.CUR_PRINCIPAL+t.CUR_INTEREST else 0 end ) as '10月还款'
,sum(case when YEAR(t.REPAY_DATE) = 2020 and MONTH(t.REPAY_DATE) = 11 then t.CUR_PRINCIPAL+t.CUR_INTEREST else 0 end ) as '11月还款'
,sum(case when YEAR(t.REPAY_DATE) = 2020 and MONTH(t.REPAY_DATE) = 12 then t.CUR_PRINCIPAL+t.CUR_INTEREST else 0 end ) as '12月还款'
,COUNT(distinct t.SQBH) as '还款量'
FROM
(
SELECT
distinct SERIAL_ID
,SQBH
,KHXM
,CHANNEL_NAME
,HTHM
,LOAN_ID
,REPAY_TIME
,REPAY_DATE
,CUR_PRINCIPAL
,CUR_INTEREST
,CUR_PENALTY
,CUR_PAYINTPENAMT
,ACTUAL_DATE
,ACT_PRINCIPAL
,ACT_INTEREST
,ACT_PENALTY
,ACT_PAYINTPENAMT
,COM_PRINCIPAL
,COM_INTEREST
,COM_PENALTY
,COM_PAYINTPENAMT
,COMPENSATORY
,ROFFERDATE
,CHARGE_STATUS
,REGISTEREDCITY
,REGISTEREDPROVINCE
FROM
detuctionsdetails
WHERE
REPAY_DATE < date(concat(year(curdate()),'-',month(curdate()),'-','1'))
#date_sub(CURRENT_DATE,interval 0 day)
AND
CHARGE_STATUS = 1
and
CHANNEL_NAME not in ('条件1','条件2')
)as t