时间戳相关操作

时间戳相关操作

格式化

DATE_FORMAT(submit_time,'%Y%m')
# Y：完整年
# y：年份的后 2 位

抓取年月日

# Write your MySQL query statement below
select transaction_id from Transactions t, (select date_format(day, '%y%m%d') `date`, max(amount) amount from Transactions
    group by `date`) temp
    where date_format(t.day, '%y%m%d') = temp.date and t.amount = temp.amount
    order by transaction_id

日期格式（1881. 转换日期格式）

select concat(dayname(day), ', ', monthname(day), ' ', day(day), ', ', year(day)) day from Days 

+-------------------------+
| day |
+-------------------------+
| Tuesday, April 12, 2022 |
| Monday, August 9, 2021 |
| Friday, June 26, 2020 |
+-------------------------+

SELECT 
    DATE_FORMAT(day, '%W, %M %e, %Y') AS day
FROM
    days

日期差

1. TIMESTAMPDIFF

# 第二个参数 - 第一个参数
SELECT TIMESTAMPDIFF(MONTH,'2012-10-01','2013-01-13');

2. DATEDIFF

# 第一个参数 - 第二个参数
SELECT DATEDIFF('2013-01-13','2012-10-01'); # 返回相差的天数

1709. 访问日期之间的最大的空档期

# Write your MySQL query statement below
# now：2021-1-1
# 求：最大间隔

# 1. 特殊情况：最后一个，now - max
select user_id, max(date_diff) biggest_window from 
(select user_id, datediff('2021-1-1', max(visit_date)) date_diff from UserVisits 
    group by user_id 
union
select user_id, max(datediff(next, visit_date)) date_diff from
(select 
    user_id,
    visit_date,
    lead(visit_date) over(partition by user_id order by visit_date) next
    from UserVisits) temp
    where temp.next is not null
    group by user_id) temp2
    group by user_id

日期增加 DATE_ADD()

DATE_ADD() 函数向日期添加指定的时间间隔。

# 语法
# 1. date：日期表达式
# 2. expr：时间间隔
# 3. type：按照什么字段增加【SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR】
DATE_ADD(date,INTERVAL expr type)