赞
踩
- -- 代码 --
- select current_date() -- 当前日期
- ,current_timestamp() -- 当前默认时间
- ,from_utc_timestamp(current_timestamp(),'GMT+8') -- 转为东八区时间
- ,unix_timestamp() -- 时间戳
- ,from_unixtime(unix_timestamp()) -- 时间戳对应时间(东八区)
- ,to_utc_timestamp(from_unixtime(unix_timestamp()),'GMT') -- 当前时间戳转为时间(默认时区)
- -- 结果 --
- 2023-02-01
- 2023-02-01 09:57:43 -- (时间差8h)
- 2023-02-01 17:57:43
- 1675245463
- 2023-02-01 17:57:43
- 2023-02-01 09:57:43
注意:
current_timestamp() 获取的时UTC默认时区。
给定一个时间戳可基于from_utc_timestamp/to_utc_timestamp进行转换。
- 代码:select unix_timestamp('2023-01-20 10:25:30') as dt
- 结果:1674181530
- -- 代码 --
- select
- from_unixtime(1674181530) as dt1
- ,from_unixtime(1674181530, 'yyyy-MM-dd') as dt2
- ,from_unixtime(1674181530, 'yyyyMMdd') as dt3
-
- -- 结果 --
- 2023-01-20 10:25:30
- 2023-01-20
- 20230120
- -- 代码 --
- select date_format('2023-01-20 10:25:30','yyyy-MM-dd') as dt1
- ,date_format('2023-01-20 10:25:30','yyyyMMdd') as dt2
- -- 结果 --
- 2023-01-20
- 20230120
- -- 代码 --
- select year('2023-01-20 10:25:30') as year
- ,month('2023-01-20 10:25:30') as month
- ,day('2023-01-20 10:25:30') as day
- ,hour('2023-01-20 10:25:30') as hour
- ,minute('2023-01-20 10:25:30') as minute
- ,second('2023-01-20 10:25:30') as second
- ,weekofyear('2023-01-20 10:25:30') as weekofyear
- -- ,dayofweek('2023-01-20 10:25:30') as dayofweek
- ,date_format('2023-01-20 10:25:30' ,'u') as dayofweek2
- ,ceil(month('2023-01-20 10:25:30')/3) as season
-
- -- 结果 --
- 2023
- 1
- 20
- 10
- 25
- 30
- 3 -- 此处为3,而非4
- 5
- 1

注意:
如果当前年的第一个周,天数超过3天,那就是当前年的第一周;
如果当前年的第一个周,天数小于等于3天,那就是上一年的最后一周。
weekofyear('2023-01-01') 结果为52;
weekofyear('2023-01-02') 结果为1.
- -- 代码 --
- select date_add('2023-01-20',1) as dt_add1
- ,date_sub('2023-01-20',1) as dt_sub1
- ,add_months('2023-01-20',1) as dt_add_month1
- ,add_months('2023-01-20',-1) as dt_sub_month1
- -- 结果 --
- 2023-01-21
- 2023-01-19
- 2023-02-20
- 2022-12-20
- -- 代码 --
- select from_unixtime(unix_timestamp('2023-01-20 10:25:30')- 1*3600) as sub_hour1 -- 减1小时
- ,from_unixtime(unix_timestamp('2023-01-20 10:25:30') + 1*3600) as add_hour1 -- 加1小时
- ,from_unixtime(unix_timestamp('2023-01-20 10:25:30') - 10*60) as sub_minute10 --减10分钟
- ,from_unixtime(unix_timestamp('2023-01-20 10:25:30') + 10*60) as sub_minute10 --加10分钟
- -- 结果 --
- 2023-01-20 09:25:30
- 2023-01-20 11:25:30
- 2023-01-20 10:15:30
- 2023-01-20 10:35:30
- -- 代码1 -- 跨月数据
- select day_diff1,month_diff1,day_diff2,month_diff2,day_diff2/day_diff1 as month_diff
- from (
- select datediff('2023-01-20', '2022-12-20') as day_diff1 -- 结束日期在前
- ,months_between('2023-01-20', '2022-12-20') as month_diff1
- ,datediff('2023-01-20', '2022-12-21') as day_diff2 -- 结束日期在前
- ,months_between('2023-01-20', '2022-12-21') as month_diff2
- )
- -- 结果 --
- 31
- 1.0
- 30
- 0.9677
- 0.9677
-
- -- 代码2 -- 当月数据
- select datediff('2023-01-20', '2023-01-10') as day_diff1 -- 结束日期在前
- ,months_between('2023-01-20', '2023-01-10') as month_diff1
- ,10/31 as c1
- ,datediff('2023-02-20', '2023-02-10') as day_diff2 -- 结束日期在前
- ,months_between('2023-02-20', '2023-02-10') as month_diff2
- ,10/28 as c2
- ,10/31 as c3
- -- 结果 --
- 10
- 0.32258
- 0.32258
- 10
- 0.32258
- 0.35714
- 0.32258

注意:
months_between使用时:
若是跨月日期,则分母按照跨月整月天数(31/30/28)计算;
若是同月日期,则分母统一使用31天进行计算。
- -- 代码 --
- select
- trunc('2023-01-20', 'YY') as year_first -- 年初
- ,date_sub(add_months(trunc('2023-01-20', 'YY'),12),1) as year_end -- 年末
- ,trunc('2023-01-20','MM') as month_first -- 月初
- ,last_day('2023-01-20') as month_end -- 月末
- ,date_sub(next_day('2023-01-20','Mon'),7) as monday -- 周一
- ,date_sub(next_day('2023-01-20','Mon'),1) as sunday -- 周日
-
- -- 结果 --
- 2023-01-01
- 2023-12-31
- 2023-01-01
- 2023-01-31
- 2023-01-16
- 2023-01-22

- -- 代码 --
- select
- next_day('2023-01-20', 'MO') as next_mo
- ,next_day('2023-01-20', 'TU') as next_tu
- ,next_day('2023-01-20', 'WE') as next_we
- ,next_day('2023-01-20', 'TH') as next_th
- ,next_day('2023-01-20', 'FR') as next_fr
- ,next_day('2023-01-20', 'SA') as next_sa
- ,next_day('2023-01-20', 'SU') as next_su
- -- 结果 --
- 2023-01-23
- 2023-01-24
- 2023-01-25
- 2023-01-26
- 2023-01-27
- 2023-01-21 -- 本周六
- 2023-01-22 -- 本周日

Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。