时间处理【Mysql】【Hive】【Oracle】【Linux】

  • 【Oracle】
  • 1、参数传入时间获取
  • 2、用sysdate获取目标时间
  • 【Linux】
  • 【Mysql】
  • 1、当前时间
  • 2、 转格式
  • 3、 昨天
  • 4、 上月
  • 5、上个月最后一天
  • 6、计算时间差(默认显示为天数)
  • 【Hive】
  • 1、hive时间格式转换 2011-12-07 13:01:03 -> yyyymmddhh24miss
  • 2、Hive中获取当前的时间:
  • 3、Hive中计算时间差(和mysql一致,显示天数)
  • 4、hive获取昨天


【Oracle】

1、参数传入时间获取

’#NOW_DAY#’ =当前时间 eg:20200526

目标时间

目标时间格式

sql

今日日期(YYMMDD)

YYMMDD

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD’),‘YYMMDD’) FROM DUAL

上月月末日期(YYMMDD)

YYMMDD

SELECT TO_CHAR(TRUNC(ADD_MONTHS(TO_DATE(’#NOW_DAY#’,‘YYYYMMDD’),0),‘MM’) -1,‘YYMMDD’) FROM DUAL

数据周期(月)

YYYYMM

SELECT CASE WHEN TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYYMMDD’),‘DD’)=‘01’ THEN TO_CHAR(ADD_MONTHS(TO_DATE(’#NOW_DAY#’,‘YYYYMMDD’),-1),‘YYYYMM’) ELSE TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYYMMDD’),‘YYYYMM’) END FROM DUAL

前天日期(YYMMDD)

YYMMDD

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD’) -2,‘YYMMDD’) FROM DUAL

30天前日期

YYYYMMDD

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD’) - 30,‘YYYYMMDD’) FROM DUAL

去年日期

YYYY

SELECT TO_CHAR(ADD_MONTHS(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD’),-12),‘YYYY’) FROM DUAL

今年日期

YYYY

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD’),‘YYYY’) FROM DUAL

去年年初日期

YYYY-MM-DD

SELECT TO_CHAR(ADD_MONTHS(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD’),-12),‘YYYY’)

今年年初日期

YYYY-MM-DD

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD’),‘YYYY’)

上月日期

YYYYMM

SELECT TO_CHAR(ADD_MONTHS(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD’),-1),‘YYYYMM’) FROM DUAL

上月时间

YYYY-MM

SELECT TO_CHAR(ADD_MONTHS(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD’),-1),‘YYYY-MM’) FROM DUAL

当月日期

YYYYMM

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD’),‘YYYYMM’) FROM DUAL

当月时间

YYYY-MM

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD’),‘YYYY-MM’) FROM DUAL

昨天日期

YYYYMMDD

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD’) -1,‘YYYYMMDD’) FROM DUAL

昨天时间

YYYY-MM-DD

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD’) -1,‘YYYY-MM-DD’) FROM DUAL

今天日期

YYYYMMDD

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD’),‘YYYYMMDD’) FROM DUAL

今天时间

YYYY-MM-DD

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD’),‘YYYY-MM-DD’) FROM DUAL

当月月初日期

YYYYMMDD

SELECT TO_CHAR(TRUNC(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD’),‘MONTH’) + 1 / 86400,‘YYYYMMDD’) FROM DUAL

当月月末日期

YYYYMMDD

SELECT TO_CHAR(LAST_DAY(TRUNC(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD’),‘MONTH’)) + 1 - 1 / 86400,‘YYYYMMDD’) FROM DUAL

当月月初时间

YYYY-MM-DD

SELECT TO_CHAR(TRUNC(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD’),‘MONTH’) + 1 / 86400,‘YYYY-MM-DD’) FROM DUAL

当月月末时间

YYYY-MM-DD

SELECT TO_CHAR(LAST_DAY(TRUNC(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD’),‘MONTH’)) + 1 - 1 / 86400,‘YYYY-MM-DD’) FROM DUAL

上月月初日期

YYYYMMDD

SELECT TO_CHAR(TRUNC(ADD_MONTHS(TO_DATE(’#NOW_DAY#’,‘YYYYMMDD’),-1),‘MM’),‘YYYYMMDD’) FROM DUAL

上月月末日期

YYYYMMDD

SELECT TO_CHAR(TRUNC(ADD_MONTHS(TO_DATE(’#NOW_DAY#’,‘YYYYMMDD’),0),‘MM’) -1,‘YYYYMMDD’) FROM DUAL

上月月初时间

YYYY-MM-DD

SELECT TO_CHAR(TRUNC(ADD_MONTHS(TO_DATE(’#NOW_DAY#’,‘YYYYMMDD’),-1),‘MM’),‘YYYY-MM-DD’) FROM DUAL

上月月末时间

YYYY-MM-DD

SELECT TO_CHAR(TRUNC(ADD_MONTHS(TO_DATE(’#NOW_DAY#’,‘YYYYMMDD’),0),‘MM’) -1,‘YYYY-MM-DD’) FROM DUAL

昨天日期(MMDD)

MMDD

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYYMMDD’) -1,‘MMDD’) FROM DUAL

上上月日期

YYYYMM

SELECT TO_CHAR(ADD_MONTHS(TO_DATE(’#NOW_DAY#’,‘YYYYMMDD’),-2),‘YYYYMM’) FROM DUAL

三月前日期

YYYYMM

SELECT TO_CHAR(ADD_MONTHS(TO_DATE(’#NOW_DAY#’,‘YYYYMMDD’),-3),‘YYYYMM’) FROM DUAL

四月前日期

YYYYMM

SELECT TO_CHAR(ADD_MONTHS(TO_DATE(’#NOW_DAY#’,‘YYYYMMDD’),-4),‘YYYYMM’) FROM DUAL

五月前日期

YYYYMM

SELECT TO_CHAR(ADD_MONTHS(TO_DATE(’#NOW_DAY#’,‘YYYYMMDD’),-5),‘YYYYMM’) FROM DUAL

上月日期(MM)

MM

SELECT TO_CHAR(ADD_MONTHS(TO_DATE(’#NOW_DAY#’,‘YYYYMMDD’),-1),‘MM’) FROM DUAL

上月日期

YYMM

SELECT TO_CHAR(ADD_MONTHS(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD’),-1),‘YYMM’) FROM DUAL

上月今天日期

YYYYMMDD

SELECT TO_CHAR(TRUNC(ADD_MONTHS(TO_DATE(’#NOW_DAY#’,‘YYYYMMDD’),-1),‘DD’),‘YYYYMMDD’) FROM DUAL

昨天日期(DD)

DD

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYYMMDD’) -1,‘DD’) FROM DUAL

当月日期(YYMM)

YYMM

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD’),‘YYMM’) FROM DUAL

六月前日期

YYYYMM

SELECT TO_CHAR(ADD_MONTHS(TO_DATE(’#NOW_DAY#’,‘YYYYMMDD’),-6),‘YYYYMM’) FROM DUAL

当月日期

MM

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD’),‘MM’) FROM DUAL

前天日期

YYYYMMDD

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD’) -2,‘YYYYMMDD’) FROM DUAL

前一小时(HH24)

YYYYMMDDHH

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD HH24’)-1/24,‘YYYYMMDDHH24’) FROM DUAL

前两小时(HH24)

YYYYMMDDHH

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD HH24’)-2/24,‘YYYYMMDDHH24’) FROM DUAL

前一小时(HH)

HH

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD HH24’)-1/24,‘HH24’) FROM DUAL

今日日期(小时接口专用)

YYMMDD

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD HH24’),‘YYMMDD’) FROM DUAL

当月日期-累计

YYYYMM

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD’)-1,‘YYYYMM’) FROM DUAL

数据周期(日)

YYYYMMDD

SELECT SUBSTR(TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD HH24’)-1/24,‘YYYYMMDDHH24’),1,8) FROM DUAL

昨天日期(与账期无关)

YYYYMMDD

SELECT TO_CHAR(SYSDATE-1,‘YYYYMMDD’) FROM DUAL

测试账期

YYYYMMDD

SELECT TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD’) FROM DUAL

分钟级专用:今日日期(DD)

DD

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYYMMDDHH24mi’),‘DD’) FROM DUAL

分钟级专用:今日日期(YYYYMMDD)

YYYYMMDD

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYYMMDDHH24mi’),‘YYYYMMDD’) FROM DUAL

分钟级专用:当月(YYYYMM)

YYYYMM

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYYMMDDHH24mi’),‘YYYYMM’) FROM DUAL

分钟级专用:当前小时(HH24)

HH24

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYYMMDDHH24mi’),‘HH24’) FROM DUAL

分钟级专用:当前分钟(mi)

mi

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYYMMDDHH24mi’),‘mi’) FROM DUAL

3天前日期(YYMMDD)

YYMMDD

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYYMMDD’) - 3,‘YYYYMMDD’) FROM DUAL

当前小时,与账期无关

YYYYMMDDHH24

SELECT TO_CHAR(sysdate,‘YYYYMMDDHH24’) FROM DUAL

昨日日期(YYMMDD)

YYMMDD

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD’) -1,‘YYMMDD’) FROM DUAL

上月昨天日期

YYYYMMDD

SELECT TO_CHAR(TRUNC(ADD_MONTHS(TO_DATE(’#NOW_DAY#’,‘YYYYMMDD’),-1),‘DD’) -1,‘YYYYMMDD’) FROM DUAL

现在小时时间

YYYYMMDDHH

SELECT TO_CHAR(TO_DATE(’#NOW_DAY#’,‘YYYY-MM-DD HH24’),‘YYYYMMDDHH24’) FROM DUAL

2、用sysdate获取目标时间

select
to_char(sysdate-1,‘yyyymmdd’) as yesterday, --昨天
to_char(add_months(trunc(sysdate),-1),‘yyyymm’) as ls_month, --上月
to_char(last_day(add_months(SYSDATE, -2)) + 1,‘yyyymmdd’) as lm_fs_day, --上月第一天
to_char(last_day(add_months(sysdate,-1)),‘yyyymmdd’) as lm_ls_day, --上月最后一天
to_char(last_day(add_months(sysdate,-1)),‘yyyymmdd’)-1 as lm_ls2_day, – 上月倒数第二天
to_char(last_day(add_months(sysdate,-1)),‘yyyymmdd’)-20 as lm_ls20_day, --上月倒数第20天
to_char(sysdate,‘yyyymm’) as now_month, --当前月
to_char(sysdate-1,‘yyyymmdd’) as Cur_Day_YYYYMMDD, --昨天
to_char(sysdate-1,‘yyyymm’)||‘01’ as nm_fs_day, --当月第一天
to_char(last_day(add_months(sysdate,0)),‘yyyymmdd’) as lm_ls_day, --当月最后一天
to_char(add_months(trunc(sysdate),-2),‘yyyymm’) as ls_2_month, --前月
to_char(sysdate-2,‘yyyymmdd’) as day_bf_yes --前天
from dual

【Linux】

#--当前时间戳
CUR_TIME=`date +%s`
#--三小时前(HH)
thr_hour=`date +%H -d -3hour`

#--今天日期(YYYYMMDD)
today=`date +"%Y%m%d" -d @$CUR_TIME`
#--昨天日期(YYYYMMDD)
YESTERDAY_YYYYMMDD=`date +%Y%m%d -d @$((CUR_TIME - 86400))`
yesterday=$YESTERDAY_YYYYMMDD
#--前天日期(YYYYMMDD)
DAY_BF_YES_YYYYMMDD=`date +%Y%m%d -d @$((CUR_TIME - 172800))`
day_bf_yes=$DAY_BF_YES_YYYYMMDD

#--现在的月份(YYYYMM)
NOW_YEAR=`date +%Y -d @$CUR_TIME`
NOW_MON=`date +%m -d @$CUR_TIME`
now_month=${NOW_YEAR}${NOW_MON}
#--现月第一天(YYYYMMDD)
nm_fs_day=${now_month}01
#--现月最后一天(YYYYMMDD)
nm_ls_day=${now_month}$(cal ${now_month:4:2} ${now_month:0:4}|xargs|awk '{print $NF}')

#--上个月月份(YYYYMM)
FIST_TIME=`date -d"${NOW_YEAR}/${NOW_MON}/01 12:12:12" +%s`
LS_MONTH_YYYYMM=`date +%Y%m -d @$((FIST_TIME - 86400))`
ls_month=$LS_MONTH_YYYYMM
#--上个月第一天(YYYYMMDD)
lm_fs_day=${ls_month}01
#--上个月最后一天(YYYYMMDD)
lm_ls_day=${ls_month}$(cal ${ls_month:4:2} ${ls_month:0:4}|xargs|awk '{print $NF}')

#--上上个月月份(YYYYMM)
FIST_TIME2=`date -d"${ls_month:0:4}/${ls_month:4:2}/01 12:12:12" +%s`
LS_2_MONTH_YYYYMM=`date +%Y%m -d @$((FIST_TIME2 - 86400))`
ls_2_month=$LS_2_MONTH_YYYYMM
#--上上个月第一天(YYYYMMDD)
l2m_fs_day=${ls_2_month}01
#--上上个月最后一天(YYYYMMDD)
l2m_ls_day=${ls_2_month}$(cal ${ls_2_month:4:2} ${ls_2_month:0:4}|xargs|awk '{print $NF}')

#--三个月前月份(YYYYMM)
FIST_TIME3=`date -d"${ls_2_month:0:4}/${ls_2_month:4:2}/01 12:12:12" +%s`
LS_3_MONTH_YYYYMM=`date +%Y%m -d @$((FIST_TIME3 - 86400))`
ls_3_month=$LS_3_MONTH_YYYYMM
#--三个月前第一天(YYYYMMDD)
l3m_fs_day=${ls_3_month}01
#--三个月前最后一天(YYYYMMDD)
l3m_ls_day=${ls_3_month}$(cal ${ls_3_month:4:2} ${ls_3_month:0:4}|xargs|awk '{print $NF}')

#--四个月前月份(YYYYMM)
FIST_TIME4=`date -d"${ls_3_month:0:4}/${ls_3_month:4:2}/01 12:12:12" +%s`
LS_4_MONTH_YYYYMM=`date +%Y%m -d @$((FIST_TIME4 - 86400))`
ls_4_month=$LS_4_MONTH_YYYYMM
#--四个月前第一天(YYYYMMDD)
l4m_fs_day=${ls_4_month}01
#--四个月前最后一天(YYYYMMDD)
l4m_ls_day=${ls_4_month}$(cal ${ls_4_month:4:2} ${ls_4_month:0:4}|xargs|awk '{print $NF}')

#--五个月前月份(YYYYMM)
FIST_TIME5=`date -d"${ls_4_month:0:4}/${ls_4_month:4:2}/01 12:12:12" +%s`
LS_5_MONTH_YYYYMM=`date +%Y%m -d @$((FIST_TIME5 - 86400))`
ls_5_month=$LS_5_MONTH_YYYYMM
#--五个月前第一天(YYYYMMDD)
l5m_fs_day=${ls_5_month}01
#--五个月前最后一天(YYYYMMDD)
l5m_ls_day=${ls_5_month}$(cal ${ls_5_month:4:2} ${ls_5_month:0:4}|xargs|awk '{print $NF}')

#--六个月前月份(YYYYMM)
FIST_TIME6=`date -d"${ls_5_month:0:4}/${ls_5_month:4:2}/01 12:12:12" +%s`
LS_6_MONTH_YYYYMM=`date +%Y%m -d @$((FIST_TIME6 - 86400))`
ls_6_month=$LS_6_MONTH_YYYYMM
#--六个月前第一天(YYYYMMDD)
l6m_fs_day=${ls_6_month}01
#--六个月前最后一天(YYYYMMDD)
l6m_ls_day=${ls_6_month}$(cal ${ls_6_month:4:2} ${ls_6_month:0:4}|xargs|awk '{print $NF}')

#--3天前
l3_day_ago=`date -d"- 3 days" +"%Y%m%d"`
#--5天前
l5_day_ago=`date -d"- 5 days" +"%Y%m%d"`
#--7天前
l7_day_ago=`date -d"- 7 days" +"%Y%m%d"`
    #--8天前
l8_day_ago=`date -d"- 8 days" +"%Y%m%d"`
    #--9天前
l9_day_ago=`date -d"- 9 days" +"%Y%m%d"`
    #--10天前
l10_day_ago=`date -d"- 10 days" +"%Y%m%d"`
#--14天前
l14_day_ago=`date -d"- 14 days" +"%Y%m%d"`
#--1个月前
l1_month_ago=`date -d"- 1 months" +"%Y%m%d"`
#--2个月前
l2_month_ago=`date -d"- 2 months" +"%Y%m%d"`
#--3个月前
l3_month_ago=`date -d"- 3 months" +"%Y%m%d"`
#--4个月前
l4_month_ago=`date -d"- 4 months" +"%Y%m%d"`
#--5个月前
l5_month_ago=`date -d"- 5 months" +"%Y%m%d"`
#--6个月前
l6_month_ago=`date -d"- 6 months" +"%Y%m%d"`
#--1年前
l1_year_ago=`date -d"- 12 months" +"%Y%m%d"`
#--2年前
l2_year_ago=`date -d"- 24 months" +"%Y%m%d"`
#--3年前
l3_year_ago=`date -d"- 36 months" +"%Y%m%d"`

【Mysql】

1、当前时间
select now();
 2020-06-16 11:39:28
2、 转格式
select date_format(now(), ‘%Y%m%d%H%i%s’)
 20200616114227
3、 昨天
select DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 DAY),’%Y%m%d’)
 20200615
4、 上月
DATE_FORMAT(date_sub(curdate(),interval 1 month),’%Y%m’)
 202006
5、上个月最后一天
select last_day(date_sub(now(),interval 1 month));
 2020-05-31
 select date_format(last_day(date_sub(now(),interval 1 month)),’%Y%m%d’);
 20200531
6、计算时间差(默认显示为天数)
select datediff(‘2018-06-04’,‘2018-06-05’);
 1

【Hive】

1、hive时间格式转换 2011-12-07 13:01:03 -> yyyymmddhh24miss
select regexp_replace(regexp_replace(regexp_replace(‘2011-12-07 13:01:03’,’-’,’’),’:’,’’),’ ‘,’’) as date – —> 20111207130103
 或者正则截取得到目标值
 select substr(regexp_replace(‘2011-12-07 13:01:03’,’-| |:’,’’),1,14)
 20111207130103
2、Hive中获取当前的时间:
select from_unixtime(unix_timestamp(),‘yyyy-MM-dd HH:mm:ss’)
 2020-06-16 15:18:28
3、Hive中计算时间差(和mysql一致,显示天数)
select datediff(‘2018-06-04’,‘2018-06-05’);
 1
4、hive获取昨天
select regexp_replace(substr(date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP()),1),1,10),’-’,’’) date
 20200615