一、随机示例(想到哪里写哪里)
1.系统时间函数
查询
select
current_timestamp --当前格式化时间
,current_date --当前格式化日期
,unix_timestamp() --当前unix时间戳
结果:
2.时间函数转换
查询
--将时间戳转化为格式化时间
select
from_unixtime(1725865044)
,from_unixtime(1725865044,'yyyy-MM-dd')
,from_unixtime(1725865044,'yyyy-MM')
,from_unixtime(1725865044,'dd')
结果
查询
--时间格式化为日期
select to_date('2024-09-09 14:57:24')
结果
查询
--日期格式化
select
date_format('2024-09-09 14:57:24','yyyy-MM-dd')
,date_format('2024-09-09','yyyy-MM')
,date_format('2024-09-09','yyyy')
结果
3.时间函数
查询
select
current_date as curr_day --当日
,year(current_date) as curr_year --年
,quarter(current_date) as curr_quarter --季度
,weekofyear(current_date) as curr_week --年周
,month(current_date) as curr_month --月
,day(current_date) as curr_day1 --日
,hour(current_timestamp) as curr_hour --时
,minute(current_timestamp) as curr_minute --分
,second(current_timestamp) as curr_second --秒
结果
4.计算类函数
查询
select
current_date
,date_sub(current_date,1) as yesterday
,date_add(current_date,1) as nextday
,datediff('2024-09-11','2024-09-10') as diff_days
,add_months(current_date,-1) as lastmonth_day
,add_months(current_date,1) as nextmonth_day
,next_day(current_date,'Mon') as nextmonday
,last_day(current_date) as lastday
结果
5.常用日期
查询
--统计周期内第一天
select
current_date as curr_day --当日
,trunc(current_date,'YY') as curr_year --当年第一天
,trunc(current_date,'Q') as curr_quarter --当季度第一天
,trunc(current_date,'MM') as curr_month --当月第一天
,date_sub(next_day(current_date, 'MO'), 7) as curr_week --当周第一天
结果