hive内嵌时间日期函数：所有关于时间日期的函数

水深无声 2022-05-30 05:52 354阅读 0赞

**日期函数在hive中很常用，甚至有时候要自定义时间函数。首先hive中存放的数据经常是按时间分区进行存储的，有时候按时间分区的时间就是从日志中国解析出来的时间，或者有时候截取时间进行区间划分。比如我们自定义的时间函数DAYOFWEEK(日期)，返回当前日期是星期几。其次因为所有的数据都与时间有关，所有的数据都具有时效性。**

### **1.如下Hive内嵌的所有关于日期时间的相关函数** ###

<table> 
 <thead> 
 <tr> 
 <td style="vertical-align:middle;"> Return Type </td> 
 <td style="vertical-align:middle;"> Name(Signature) </td> 
 <th> Description </th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td> string </td> 
 <td> from_unixtime(bigint unixtime[, string format]) </td> 
 <td> 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 </td> 
 </tr> 
 <tr> 
 <td> bigint </td> 
 <td> unix_timestamp() </td> 
 <td> Gets current Unix timestamp in seconds. 获取本地时区下的时间戳 </td> 
 </tr> 
 <tr> 
 <td> bigint </td> 
 <td> unix_timestamp(string date) </td> 
 <td> Converts time string in format&nbsp;<code>yyyy-MM-dd HH:mm:ss</code>&nbsp;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的时间字符串转换成时间戳 &nbsp;如unix_timestamp('2009-03-20 11:30:01') = 1237573801 </td> 
 </tr> 
 <tr> 
 <td> bigint </td> 
 <td> unix_timestamp(string date, string pattern) </td> 
 <td> Convert time string with given pattern (see [<a href="http://docs.oracle.com/javase/tutorial/i18n/format/simpleDateFormat.html" rel="nofollow">http://docs.oracle.com/javase/tutorial/i18n/format/simpleDateFormat.html</a>]) 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 </td> 
 </tr> 
 <tr> 
 <td> string </td> 
 <td> to_date(string timestamp) </td> 
 <td> Returns the date part of a timestamp string: to_date("1970-01-01 00:00:00") = "1970-01-01". 返回时间字符串的日期部分 </td> 
 </tr> 
 <tr> 
 <td> int </td> 
 <td> year(string date) </td> 
 <td> Returns the year part of a date or a timestamp string: year("1970-01-01 00:00:00") = 1970, year("1970-01-01") = 1970. 返回时间字符串的年份部分 </td> 
 </tr> 
 <tr> 
 <td colspan="1">int</td> 
 <td colspan="1">quarter(date/timestamp/string)</td> 
 <td colspan="1">Returns the quarter of the year for a date, timestamp, or string in the range 1 to 4 (as of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-3404" rel="nofollow">1.3.0</a>). Example: quarter('2015-04-08') = 2. 返回当前时间属性哪个季度 如quarter('2015-04-08') = 2 </td> 
 </tr> 
 <tr> 
 <td> int </td> 
 <td> month(string date) </td> 
 <td> Returns the month part of a date or a timestamp string: month("1970-11-01 00:00:00") = 11, month("1970-11-01") = 11. 返回时间字符串的月份部分 </td> 
 </tr> 
 <tr> 
 <td> int </td> 
 <td> day(string date) dayofmonth(date) </td> 
 <td> Returns the day part of a date or a timestamp string: day("1970-11-01 00:00:00") = 1, day("1970-11-01") = 1. 返回时间字符串的天 </td> 
 </tr> 
 <tr> 
 <td> int </td> 
 <td> hour(string date) </td> 
 <td> Returns the hour of the timestamp: hour('2009-07-30 12:58:59') = 12, hour('12:58:59') = 12. 返回时间字符串的小时 </td> 
 </tr> 
 <tr> 
 <td> int </td> 
 <td> minute(string date) </td> 
 <td> Returns the minute of the timestamp. 返回时间字符串的分钟 </td> 
 </tr> 
 <tr> 
 <td> int </td> 
 <td> second(string date) </td> 
 <td> Returns the second of the timestamp. 返回时间字符串的秒 </td> 
 </tr> 
 <tr> 
 <td> int </td> 
 <td> weekofyear(string date) </td> 
 <td> Returns the week number of a timestamp string: weekofyear("1970-11-01 00:00:00") = 44, weekofyear("1970-11-01") = 44. 返回时间字符串位于一年中的第几个周内 &nbsp;如weekofyear("1970-11-01 00:00:00") = 44, weekofyear("1970-11-01") = 44 </td> 
 </tr> 
 <tr> 
 <td> int </td> 
 <td> datediff(string enddate, string startdate) </td> 
 <td> Returns the number of days from startdate to enddate: datediff('2009-03-01', '2009-02-27') = 2. 计算开始时间startdate到结束时间enddate相差的天数 </td> 
 </tr> 
 <tr> 
 <td> string </td> 
 <td> date_add(string startdate, int days) </td> 
 <td> Adds a number of days to startdate: date_add('2008-12-31', 1) = '2009-01-01'. 从开始时间startdate加上days </td> 
 </tr> 
 <tr> 
 <td> string </td> 
 <td> date_sub(string startdate, int days) </td> 
 <td> Subtracts a number of days to startdate: date_sub('2008-12-31', 1) = '2008-12-30'. 从开始时间startdate减去days </td> 
 </tr> 
 <tr> 
 <td> timestamp </td> 
 <td> from_utc_timestamp(timestamp, string timezone) </td> 
 <td> Assumes given timestamp is UTC and converts to given timezone (as of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-2272" rel="nofollow">0.8.0</a>). For example, from_utc_timestamp('1970-01-01 08:00:00','PST') returns 1970-01-01 00:00:00. 如果给定的时间戳并非UTC，则将其转化成指定的时区下时间戳 </td> 
 </tr> 
 <tr> 
 <td> timestamp </td> 
 <td> to_utc_timestamp(timestamp, string timezone) </td> 
 <td> Assumes given timestamp is in given timezone and converts to UTC (as of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-2272" rel="nofollow">0.8.0</a>). For example, to_utc_timestamp('1970-01-01 00:00:00','PST') returns 1970-01-01 08:00:00. 如果给定的时间戳指定的时区下时间戳，则将其转化成UTC下的时间戳 </td> 
 </tr> 
 <tr> 
 <td colspan="1">date</td> 
 <td colspan="1">current_date</td> 
 <td colspan="1"> Returns the current date at the start of query evaluation (as of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-5472" rel="nofollow">1.2.0</a>). All calls of current_date within the same query return the same value. 返回当前时间日期 </td> 
 </tr> 
 <tr> 
 <td colspan="1">timestamp</td> 
 <td colspan="1">current_timestamp</td> 
 <td colspan="1"> Returns the current timestamp at the start of query evaluation&nbsp;(as of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-5472" rel="nofollow">1.2.0</a>). All calls of current_timestamp within the same query return the same value. 返回当前时间戳 </td> 
 </tr> 
 <tr> 
 <td colspan="1">string</td> 
 <td colspan="1">add_months(string start_date, int num_months)</td> 
 <td colspan="1"> Returns the date that is num_months after start_date&nbsp;(as of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-9357" rel="nofollow">1.1.0</a>). start_date is a string, date or timestamp. num_months is an integer. The time part of start_date is ignored.&nbsp;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个月的日期 </td> 
 </tr> 
 <tr> 
 <td colspan="1">string</td> 
 <td colspan="1">last_day(string date)</td> 
 <td colspan="1">Returns the last day of the month which the date belongs to&nbsp;(as of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-9358" rel="nofollow">1.1.0</a>). date is a string in the format 'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'.&nbsp;The time part of date is ignored. 返回这个月的最后一天的日期，忽略时分秒部分（HH:mm:ss） </td> 
 </tr> 
 <tr> 
 <td colspan="1">string</td> 
 <td colspan="1">next_day(string start_date, string day_of_week)</td> 
 <td colspan="1">Returns the first date which is later than start_date and named as day_of_week&nbsp;(as of Hive<a href="https://issues.apache.org/jira/browse/HIVE-9520" rel="nofollow">1.2.0</a>).&nbsp;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 &nbsp;以2015-01-14为开始时间，其下一个星期二所对应的日期为2015-01-20 </td> 
 </tr> 
 <tr> 
 <td colspan="1">string</td> 
 <td colspan="1">trunc(string date, string format)</td> 
 <td colspan="1">Returns date truncated to the unit specified by the format&nbsp;(as of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-9480" rel="nofollow">1.2.0</a>). Supported formats: MONTH/MON/MM, YEAR/YYYY/YY. Example: trunc('2015-03-17', 'MM') = 2015-03-01. 返回时间的最开始年份或月份 &nbsp;如trunc("2016-06-26",“MM”)=2016-06-01 &nbsp;trunc("2016-06-26",“YY”)=2016-01-01 &nbsp; 注意所支持的格式为MONTH/MON/MM, YEAR/YYYY/YY </td> 
 </tr> 
 <tr> 
 <td colspan="1">double</td> 
 <td colspan="1">months_between(date1, date2)</td> 
 <td colspan="1">Returns number of months between dates date1 and date2 (as of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-9518" rel="nofollow">1.2.0</a>). 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&gt;date2，则返回正，如果date1&lt;date2,则返回负，否则返回0.0 &nbsp;如：months_between('1997-02-28 10:30:00', '1996-10-30') = 3.94959677 &nbsp;1997-02-28 10:30:00与1996-10-30相差3.94959677个月 </td> 
 </tr> 
 <tr> 
 <td colspan="1">string</td> 
 <td colspan="1">date_format(date/timestamp/string ts, string fmt)</td> 
 <td colspan="1"> Converts a date/timestamp/string to a value of string in the format specified by the date format fmt (as of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-10276" rel="nofollow">1.2.0</a>). Supported formats are Java SimpleDateFormat formats&nbsp;–<a href="https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html" rel="nofollow">https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html</a>. The second argument fmt should be constant. Example: date_format('2015-04-08', 'y') = '2015'. date_format can be used to implement other UDFs, e.g.: 
 <ul> 
 <li>dayname(date) is date_format(date, 'EEEE')</li> 
 <li>dayofyear(date) is date_format(date, 'D') 按指定格式返回时间date 如：date_format("2016-06-22","MM-dd")=06-22 </li> 
 </ul></td> 
 </tr> 
 </tbody> 
</table>

### 2.常用的函数演示 ###

**1. UNIX时间戳转日期函数: from\_unixtime  
语法: from\_unixtime(bigint unixtime\[, string format\]) ---格式可选择  
返回值: string  
说明: 转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式  
举例：  
hive> select from\_unixtime(1520299121 'yyyyMMdd') from lxw\_dual;  
      20180306**

**hive> select FROM\_UNIXTIME(UNIX\_TIMESTAMP())**

**          2019-03-01 03:30:10  
2. 获取当前UNIX时间戳函数: unix\_timestamp  
语法: unix\_timestamp()  
返回值: bigint  
说明: 获得当前时区的UNIX时间戳  
举例：  
hive> select unix\_timestamp() from lxw\_dual;  
      1323309615**

**总结：hive中一般使用****from\_unixtime和unix\_timestamp设置etl时间戳，非常有用的函数。**

**格式比如：from\_unixtime(unix\_timestamp(),'yyyy-MM-dd HH:mm:ss') AS etl\_create\_time  ---时间格式可选可不选**

hive > select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss') AS etl_create_time 
           from fdm_sor.tmp_aaaaa limit 1;
    2019-04-16 21:41:24
    
    hive> select from_unixtime(unix_timestamp(),'yyyy-MM-dd') AS etl_create_time 
         from fdm_sor.tmp_aaaaa limit 1;
    2019-04-16

**3. 日期转UNIX时间戳函数: unix\_timestamp  
语法: unix\_timestamp(string date)  
返回值: bigint  
说明: 转换格式为"yyyy-MM-dd HH:mm:ss"的日期到UNIX时间戳。如果转化失败，则返回0。  
举例：  
hive> select unix\_timestamp('2011-12-07 13:01:03') from lxw\_dual;  
      1323234063  
4. 指定格式日期转UNIX时间戳函数: unix\_timestamp  
语法: unix\_timestamp(string date, string pattern)  
返回值: bigint  
说明: 转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。  
举例：  
hive> select unix\_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss') from lxw\_dual;  
      1323234063  
5. 日期时间转日期函数: to\_date  
语法: to\_date(string timestamp)  
返回值: string  
说明: 返回日期时间字段中的日期部分。  
举例：  
hive> select to\_date('2011-12-08 10:03:01') from lxw\_dual;  
      2011-12-08  
6. 日期转年函数: year  
语法: year(string date)  
返回值: int  
说明: 返回日期中的年。  
举例：  
hive> select year('2011-12-08 10:03:01') from lxw\_dual;  
        2011  
hive> select year('2012-12-08') from lxw\_dual;  
         2012  
7. 日期转月函数: month  
语法: month (string date)  
返回值: int  
说明: 返回日期中的月份。  
举例：  
hive> select month('2011-12-08 10:03:01') from lxw\_dual;  
          12  
hive> select month('2011-08-08') from lxw\_dual;  
           8  
8. 日期转天函数: day  
语法: day (string date)  
返回值: int  
说明: 返回日期中的天。  
举例：  
hive> select day('2011-12-08 10:03:01') from lxw\_dual;  
       8  
hive> select day('2011-12-24') from lxw\_dual;  
      24  
9. 日期转小时函数: hour  
语法: hour (string date)  
返回值: int  
说明: 返回日期中的小时。  
举例：  
hive> select hour('2011-12-08 10:03:01') from lxw\_dual;  
10  
10. 日期转分钟函数: minute  
语法: minute (string date)  
返回值: int  
说明: 返回日期中的分钟。  
举例：  
hive> select minute('2011-12-08 10:03:01') from lxw\_dual;  
          3  
11. 日期转秒函数: second  
语法: second (string date)  
返回值: int  
说明: 返回日期中的秒。  
举例：  
hive> select second('2011-12-08 10:03:01') from lxw\_dual;  
           1  
12. 日期转周函数: weekofyear  
语法: weekofyear (string date)  
返回值: int  
说明: 返回日期在当前的周数。  
举例：  
hive> select weekofyear('2011-12-08 10:03:01') from lxw\_dual;  
          49  
13. 日期比较函数: datediff，非常有用的函数。  
语法: datediff(string enddate, string startdate)  
返回值: int  
说明: 返回结束日期减去开始日期的天数。  
举例：  
hive> select datediff('2012-12-08','2012-05-09') from lxw\_dual;  
          213  
14. 日期增加函数: date\_add  
语法: date\_add(string startdate, int days)  
返回值: string  
说明: 返回开始日期startdate增加days天后的日期。  
举例：  
hive> select date\_add('2012-12-08',10) from lxw\_dual;  
         2012-12-18  
15. 日期减少函数: date\_sub  
语法: date\_sub (string startdate, int days)  
返回值: string  
说明: 返回开始日期startdate减少days天后的日期。  
举例：  
hive> select date\_sub('2012-12-08',10) from lxw\_dual;  
         2012-11-28**