【Hive】日期從整形轉為Date型別

c-xuan發表於2018-07-28
Hive型別

在建表的時候我們常將日期欄位設定為INT型別，將諸如20180601這樣的數字值來表示日期，這樣在做日期比較等操作時沒有問題，但是要進行某些日期計算，就要先轉成日期型別才能進行計算了，怎麼轉換呢？

資料準備

下面在Hive中先建一個表，含有一個INT型別的日期欄位，插入兩行資料。

create table tb (dt INT);
insert into tb values (20180701);
insert into tb values (20180715);

轉換型別

這裡第一種方法是將INT型別的日期值轉成STRING型別，用Hive內建的unix_timestamp函式轉成時間戳型別，最後將時間戳用from_unixtime轉成yyyy-MM-dd的日期型別。

第二種就比較直接，將INT型別的日期值轉成STRING型別，再對字串進行擷取處理，用-拼接起來。

select dt,
from_unixtime(unix_timestamp(cast(dt as string),'yyyyMMdd'),'yyyy-MM-dd') as a,
concat(substr(cast(dt as string),1,4), '-',substr(cast(dt as string),5,2), '-',substr(cast(dt as string),7,2)) as b
from tb;

執行結果

dt a b
20180701 2018-07-01 2018-07-01
20180715 2018-07-15 2018-07-15

當然，每次都這樣寫有些費勁，可以在Hive中建立UDF或者巨集，轉換時進行呼叫就好了。

建立巨集命令

巨集命令相對於UDF要簡單方便些，但是巨集只能是臨時巨集，只在本次會話中可見、有效。因此你需要將巨集指令碼放在SQL指令碼的頭部。

DROP TEMPORARY MACRO IF EXISTS date_trans;
CREATE TEMPORARY MACRO date_trans(dt int)
    if(dt is not null and length(dt)=8,
       concat(substr(cast(dt as string),1,4), '-',substr(cast(dt as string),5,2), '-',substr(cast(dt as string),7,2)), 
       null);

--呼叫
select dt,date_trans(dt) as a from tb;

如果同一個功能的函式或巨集命令被多次呼叫，那維護起來就很方便，語句也簡潔很多。

更多

Hive內建日期函式一覽

Return TypeName(Signature)Description說明
stringfrom_unixtime(bigint unixtime[, string format])Converts the number of seconds from unix epoch (1970-01-01 00:00:00 UTC) to a string representing the timestamp of that moment in the current system time zone in the format of “1970-01-01 00:00:00”.將時間的秒值轉換成format格式（format可為“yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh”,“yyyy-MM-dd hh:mm”等等）如from_unixtime(1250111000,”yyyy-MM-dd”) 得到2009-03-12
bigintunix_timestamp()Gets current Unix timestamp in seconds.獲取本地時區下的時間戳
bigintunix_timestamp(string date)Converts time string in format?yyyy-MM-dd HH:mm:ss?to Unix timestamp (in seconds), using the default timezone and the default locale, return 0 if fail: unix_timestamp(‘2009-03-20 11:30:01’) = 1237573801將格式為yyyy-MM-dd HH:mm:ss的時間字串轉換成時間戳 ?如unix_timestamp(‘2009-03-20 11:30:01’) = 1237573801
bigintunix_timestamp(string date, string pattern)Convert time string with given pattern (see [http://docs.oracle.com/javase/tutorial/i18n/format/simpleDateFormat.html]) to Unix time stamp (in seconds), return 0 if fail: unix_timestamp(‘2009-03-20’, ‘yyyy-MM-dd’) = 1237532400.將指定時間字串格式字串轉換成Unix時間戳，如果格式不對返回0 如：unix_timestamp(‘2009-03-20’, ‘yyyy-MM-dd’) = 1237532400
stringto_date(string timestamp)Returns the date part of a timestamp string: to_date(“1970-01-01 00:00:00”) = “1970-01-01”.返回時間字串的日期部分
intyear(string date)Returns the year part of a date or a timestamp string: year(“1970-01-01 00:00:00”) = 1970, year(“1970-01-01”) = 1970.返回時間字串的年份部分
intquarter(date/timestamp/string)Returns the quarter of the year for a date, timestamp, or string in the range 1 to 4 (as of Hive?1.3.0). Example: quarter(‘2015-04-08’) = 2.返回當前時間屬性哪個季度 如quarter(‘2015-04-08’) = 2
intmonth(string date)Returns the month part of a date or a timestamp string: month(“1970-11-01 00:00:00”) = 11, month(“1970-11-01”) = 11.返回時間字串的月份部分
intday(string date) dayofmonth(date)Returns the day part of a date or a timestamp string: day(“1970-11-01 00:00:00”) = 1, day(“1970-11-01”) = 1.返回時間字串的天
inthour(string date)Returns the hour of the timestamp: hour(‘2009-07-30 12:58:59’) = 12, hour(‘12:58:59’) = 12.返回時間字串的小時
intminute(string date)Returns the minute of the timestamp.返回時間字串的分鐘
intsecond(string date)Returns the second of the timestamp.返回時間字串的秒
intweekofyear(string date)Returns the week number of a timestamp string: weekofyear(“1970-11-01 00:00:00”) = 44, weekofyear(“1970-11-01”) = 44.返回時間字串位於一年中的第幾個周內 ?如weekofyear(“1970-11-01 00:00:00”) = 44, weekofyear(“1970-11-01”) = 44
intdatediff(string enddate, string startdate)Returns the number of days from startdate to enddate: datediff(‘2009-03-01’, ‘2009-02-27’) = 2.計算開始時間startdate到結束時間enddate相差的天數
stringdate_add(string startdate, int days)Adds a number of days to startdate: date_add(‘2008-12-31’, 1) = ‘2009-01-01’.從開始時間startdate加上days
stringdate_sub(string startdate, int days)Subtracts a number of days to startdate: date_sub(‘2008-12-31’, 1) = ‘2008-12-30’.從開始時間startdate減去days
timestampfrom_utc_timestamp(timestamp, string timezone)Assumes given timestamp is UTC and converts to given timezone (as of Hive?0.8.0). For example, from_utc_timestamp(‘1970-01-01 08:00:00’,’PST’) returns 1970-01-01 00:00:00.如果給定的時間戳並非UTC，則將其轉化成指定的時區下時間戳
timestampto_utc_timestamp(timestamp, string timezone)Assumes given timestamp is in given timezone and converts to UTC (as of Hive?0.8.0). For example, to_utc_timestamp(‘1970-01-01 00:00:00’,’PST’) returns 1970-01-01 08:00:00.如果給定的時間戳指定的時區下時間戳，則將其轉化成UTC下的時間戳
datecurrent_dateReturns the current date at the start of query evaluation (as of Hive?1.2.0). All calls of current_date within the same query return the same value.返回當前時間日期
timestampcurrent_timestampReturns the current timestamp at the start of query evaluation?(as of Hive?1.2.0). All calls of current_timestamp within the same query return the same value.返回當前時間戳
stringadd_months(string start_date, int num_months)Returns the date that is num_months after start_date?(as of Hive?1.1.0). start_date is a string, date or timestamp. num_months is an integer. The time part of start_date is ignored.?If start_date is the last day of the month or if the resulting month has fewer days than the day component of start_date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as start_date.返回當前時間下再增加num_months個月的日期
stringlast_day(string date)Returns the last day of the month which the date belongs to?(as of Hive?1.1.0). date is a string in the format ‘yyyy-MM-dd HH:mm:ss’ or ‘yyyy-MM-dd’.?The time part of date is ignored.返回這個月的最後一天的日期，忽略時分秒部分（HH:mm:ss）
stringnext_day(string start_date, string day_of_week)Returns the first date which is later than start_date and named as day_of_week?(as of Hive1.2.0).?start_date is a string/date/timestamp. day_of_week is 2 letters, 3 letters or full name of the day of the week (e.g. Mo, tue, FRIDAY). The time part of start_date is ignored. Example: next_day(‘2015-01-14’, ‘TU’) = 2015-01-20.返回當前時間的下一個星期X所對應的日期 如：next_day(‘2015-01-14’, ‘TU’) = 2015-01-20 ?以2015-01-14為開始時間，其下一個星期二所對應的日期為2015-01-20
stringtrunc(string date, string format)Returns date truncated to the unit specified by the format?(as of Hive?1.2.0). Supported formats: MONTH/MON/MM, YEAR/YYYY/YY. Example: trunc(‘2015-03-17’, ‘MM’) = 2015-03-01.返回時間的最開始年份或月份 ?如trunc(“2016-06-26”,“MM”)=2016-06-01 ?trunc(“2016-06-26”,“YY”)=2016-01-01 ? 注意所支援的格式為MONTH/MON/MM, YEAR/YYYY/YY
doublemonths_between(date1, date2)Returns number of months between dates date1 and date2 (as of Hive?1.2.0). If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise the UDF calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2. date1 and date2 type can be date, timestamp or string in the format ‘yyyy-MM-dd’ or ‘yyyy-MM-dd HH:mm:ss’. The result is rounded to 8 decimal places. Example: months_between(‘1997-02-28 10:30:00’, ‘1996-10-30’) = 3.94959677返回date1與date2之間相差的月份，如date1>date2，則返回正，如果date1

相關文章