Hive日期操作

create table vn09jj5.order_date
(
 paid_date date,
 paid_time timestamp
)
row format delimited 
fields terminated by ',' 
lines terminated by '\n' 
stored as parquet ; 

 

select 
 paid_date,paid_time
 ,year(paid_time)       as `year_年`
 ,lpad(ceil(month(paid_date)/3),2,0) as `season_季度`
 ,month(paid_time)      as `month_月`
 ,day(paid_time)        as `day_日`
 ,hour(paid_time)       as `hour_时`
 ,minute(paid_time)     as `minute_分`
 ,second(paid_time)     as `second_秒`
 ,pmod(datediff(paid_date,'1920-01-01')-3,7) as dayofweek
from vn09jj5.order_date
order by paid_date 
;

 

select 
 *
 ,case 
   when dayofweek=0 then '星期日' 
   when dayofweek=1 then '星期一' 
   when dayofweek=2 then '星期二' 
   when dayofweek=3 then '星期三' 
   when dayofweek=4 then '星期四' 
   when dayofweek=5 then '星期五' 
   when dayofweek=6 then '星期六'
   else 'NULL'
  end as `week_星期` 
 ,case when dayofweek>=1 and dayofweek<=5 then '周间' else '周末'
  end as week_2 
from (
--1
select 
 paid_date,paid_time
 ,year(paid_time)       as `year_年`
 ,lpad(ceil(month(paid_date)/3),2,0) as `season_季度`
 ,month(paid_time)      as `month_月`
 ,day(paid_time)        as `day_日`
 ,hour(paid_time)       as `hour_时`
 ,minute(paid_time)     as `minute_分`
 ,second(paid_time)     as `second_秒`
 ,pmod(datediff(paid_date,'1920-01-01')-3,7) as dayofweek
 --,pmod(datediff(paid_time,'1920-01-01')-3,7) as dayofweek_2
from vn09jj5.order_date
--1end
) as a
order by a.paid_date 
; 

 

select 
 current_date() as checkday ,
 from_unixtime(unix_timestamp(),'yyyy-MM-dd') as paid_date ,
 from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:dd') as paid_time
;
+-------------+-------------+----------------------+
|  checkday   |  paid_date  |      paid_time       |
+-------------+-------------+----------------------+
| 2020-07-31  | 2020-07-31  | 2020-07-31 07:03:31  |
+-------------+-------------+----------------------+

 

select 
 current_date() as checkday
 ,year(current_date())       as year
 ,lpad(ceil(month(current_date())/3),2,0) as season
 ,month(current_date())      as month
 ,day(current_date())        as day
 ,hour(current_date())       as hour
 ,minute(current_date())     as minute
 ,second(current_date())     as second
 ,weekofyear(current_date()) as weekofyear
; 
+-------------+-------+---------+--------+------+-------+---------+---------+-------------+
|  checkday   | year  | season  | month  | day  | hour  | minute  | second  | weekofyear  |
+-------------+-------+---------+--------+------+-------+---------+---------+-------------+
| 2020-07-31  | 2020  | 03      | 7      | 31   | 0     | 0       | 0       | 31          |
+-------------+-------+---------+--------+------+-------+---------+---------+-------------+
 

 

select 
 from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:dd') as checkdatetime
 ,current_date() as checkday
 ,year(from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:dd'))       as year
 ,lpad(ceil(month(from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:dd'))/3),2,0) as season
 ,month(from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:dd'))      as month
 ,day(from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:dd'))        as day
 ,hour(from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:dd'))       as hour
 ,minute(from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:dd'))     as minute
 ,second(from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:dd'))     as second
 ,weekofyear(from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:dd')) as weekofyear
; 
+----------------------+-------------+-------+---------+--------+------+-------+---------+---------+-------------+
|    checkdatetime     |  checkday   | year  | season  | month  | day  | hour  | minute  | second  | weekofyear  |
+----------------------+-------------+-------+---------+--------+------+-------+---------+---------+-------------+
| 2020-07-31 07:05:31  | 2020-07-31  | 2020  | 03      | 7      | 31   | 7     | 5       | 31      | 31          |
+----------------------+-------------+-------+---------+--------+------+-------+---------+---------+-------------+