您現在的位置是:首頁 > 動作武俠首頁動作武俠
Hive函式將日期轉換成星期幾
- 2022-03-01
日曆時間怎麼改
前言
我們平時按天或者按星期統計資料的時候,經常需要將日期轉換成星期幾統計,下面分享一個利用Hive已有的函式計算星級幾的方法。
方法一:datediff函式和pmod函式計算星期
datediff(endDate, startDate)函式:計算兩個日期的差。
pmod(int a, int b)函式:返回a除b的餘數的絕對值。
cast(
欄位名 as 轉換的型別
):型別轉換函式。
一週是7天,然後選擇參照日期對7取餘即可,按週日是一週開始的第一天計算,注意參照日期的選擇。
—— 2021-12-12 週日SELECT date, cast(pmod(datediff(date, ‘2021-12-12’), 7) as int) dayofweek FROM ( SELECT ‘2021-12-12’ AS date UNION ALL SELECT ‘2021-12-13’ AS date UNION ALL SELECT ‘2021-12-14’ AS date UNION ALL SELECT ‘2021-12-15’ AS date UNION ALL SELECT ‘2021-12-16’ AS date UNION ALL SELECT ‘2021-12-17’ AS date UNION ALL SELECT ‘2021-12-18’ AS date UNION ALL SELECT ‘2021-12-19’ AS date UNION ALL SELECT ‘2021-12-20’ AS date ) t
日期轉換星期結果
方法二:date_format函式
date_format
(
date
/
timestamp
/
string ts
,
string fmt
)
函式:把字串或者日期轉成指定格式的日期。
SELECT date , CASE WHEN dayofweek = 7 THEN 0 ELSE dayofweek END AS dayofweekFROM ( SELECT ‘2021-12-12’ AS date, CAST(date_format(‘2021-12-12’, ‘u’) AS int) AS dayofweek UNION ALL SELECT ‘2021-12-13’ AS date, CAST(date_format(‘2021-12-13’, ‘u’) AS int) AS dayofweek UNION ALL SELECT ‘2021-12-14’ AS date, CAST(date_format(‘2021-12-14’, ‘u’) AS int) AS dayofweek UNION ALL SELECT ‘2021-12-15’ AS date, CAST(date_format(‘2021-12-15’, ‘u’) AS int) AS dayofweek UNION ALL SELECT ‘2021-12-16’ AS date, CAST(date_format(‘2021-12-16’, ‘u’) AS int) AS dayofweek UNION ALL SELECT ‘2021-12-17’ AS date, CAST(date_format(‘2021-12-17’, ‘u’) AS int) AS dayofweek UNION ALL SELECT ‘2021-12-18’ AS date, CAST(date_format(‘2021-12-18’, ‘u’) AS int) AS dayofweek UNION ALL SELECT ‘2021-12-19’ AS date, CAST(date_format(‘2021-12-19’, ‘u’) AS int) AS dayofweek UNION ALL SELECT ‘2021-12-20’ AS date, CAST(date_format(‘2021-12-20’, ‘u’) AS int) AS dayofweek) t
date_format轉換星期
補充說明
date_format
函式如果第一個引數是字串,連線符只能是“-”,不能是斜線或者其他的。
select date_format(‘2021-12-12’, ‘y’);—— 2021select date_format(‘2021-12-12’, ‘yyyy’);—— 2021select date_format(‘2021-12-12’, ‘yyyy-MM’);—— 2021-12select date_format(‘2021-12-12 12:10:01’, ‘yyyy-MM’);—— 2021-12select date_format(‘2021/12/12’, ‘y’);—— NULL
覺得有用就分享收藏,關注我更多有價值的文章會第一時間推薦給你![玫瑰][玫瑰][玫瑰]