Mysql 获取日期和时间

Mysql 获取日期和时间

获取日期

select current_date from table;

获取时间

select current_time from table;

获取时间戳（日期+时间）

select current_timestamp from table;

或者（获取现在时间）

select now() from table;

获取从时间列中年份

select extract(year from '列名') from table;

当前日期加上 1 天

select current_date + interval 1 day from table;

计算日期差

select datediff(day, current_date, date '2019-01-01') from table;

简单 case

select '性别',
case '性别'
	when '男' then 1
	when '女' then 0
	else null
end
from table

搜索 case

select salary,
case 
	when salary < 5000 then 'Low'
	when salary < 10000 then 'Middle'
	else 'Top'
end as salary_level
from table

coalesce(x, y)

若x为null，则返回y，否则返回 x

select salary,
	coalesce(salary, 0)
from table

连续登录

可以看这位大佬的视频:

首先筛选得到新表，然后去重（一天内有多次登录，只保留一次）

select distinct uid, date(login_time) as ymd
from t_login
where login_time between timestamp '2022-01-01 00:00:00' and timestamp '2022-01-31 23:59"59';

连续两天登录(简单暴力解法，连续多少天就后面继续接)

SELECT  t1.uid, t1.ymd, t2.ymd
FROM  (SELECT DISTINCT uid, date(login_time) as ymd
FROM t_login
WHERE login_time BETWEEN timestamp '2022-01-01 00:00:00' AND timestamp '2022-01-31 23:59"59') t1
JOIN  (SELECT DISTINCT uid, date(login_time) as ymd
FROM t_login
WHERE  login_time BETWEEN timestamp '2022-01-01 00:00:00' AND timestamp '2022-01-31 23:59"59') t2
ON (t1.uid = t2.uid AND datediff(t2.ymd, t1.ymd)=1);

使用窗口函数可以得到排序，日期减去天数（num），若得到日期一样，则为连续

WITH t1 AS (SELECT DISTINCT uid, date(login_time) as ymd
FROM t_login
WHERE login_time BETWEEN timestamp '2022-01-01 00:00:00' AND timestamp '2022-01-31 23:59"59') 
SELECT uid, ymd, ROWNUMBER() OVER (PARTITION BY uid ORDER BY ymd) num,
				date_sub(ymd,  INTERVAL  ROW_NUMBER()  OVER  (PARTITION  BY  uid  ORDER  BY  ymd)  day)  sub_date
FROM t1;

改进1

WITH t1 AS (SELECT DISTINCT uid, date(login_time) as ymd
FROM t_login
WHERE login_time BETWEEN timestamp '2022-01-01 00:00:00' AND timestamp '2022-01-31 23:59"59') 
t2 AS (
SELECT uid, ymd, 
				date_sub(ymd,  INTERVAL  ROW_NUMBER()  OVER  (PARTITION  BY  uid  ORDER  BY  ymd)  day)  sub_date
FROM t1)
SELECT  uid, min(ymd),  max(ymd), count($*$)
FROM  t2
GROUP BY  uid,  sub_date
HAVING count($*$)>=3;  (连续登录三天以上)

改进2

WITH t1 AS (SELECT DISTINCT uid, date(login_time) as ymd
FROM t_login
WHERE login_time BETWEEN timestamp '2022-01-01 00:00:00' AND timestamp '2022-01-31 23:59"59') 
t2 AS(
SELECT uid, ymd, 
				datediff(ymd, lag(ymd, 2)  OVER  (PARTITION  BY  uid  ORDER  BY  ymd))  diff
FROM t1)
SELECT uid,  date_sub(ymd, INTERVAL  2  DAY)  min_date,  ymd
FROM  t2
WHERE  diff = 2;