MySQL常用时间处理函数
1、当前时间的年月日时分秒
select now()
2、当前时间戳
select unix_timestamp(now())
3、字符串截取
select substr(now(),1,10)
4、时间戳转年月日
select from_unixtime(create_time)
5、年月日转时间戳
select unix_timestamp('2023-12-04 09:53:59')
6、查询当前时间与指定时间相差天数
select datediff(substr(now(),1,10),'2023-12-01')
7、当前时间加2天/减2天
select date_add(now(),interval 2 day)
select date_add(now(),interval -2 day)
8、当前时间加2年/减2年
select date_add(now(),interval 2 year)
select date_add(now(),interval -2 year)
9、当前时间加2小时/减2小时
select date_add(now(),interval 2 hour)
select date_add(now(),interval -2 hour)
10、会话变量定义与使用,每句话要加分号
set @phone = '18820992515';
select * from `user` where phone = @phone;
11、查询赋值
set @phone = '18820992515';
select id into @u_id from `user` where phone = @phone;
select @u_id;
12、从多条记录中拿最新的记录
with demo as(
select
member_id,
balance_money,
row_number() over(partition by member_id order by create_time desc) as row_num,
from_unixtime(create_time)
from `tbl_balance_logs`
)
select
member_id,
sum(balance_money) as total_balance
from demo
where row_num = 1
group by member_id