一、获取以后工夫current_date获取以后日期2018-04-09current_timestamp/now()获取以后工夫2018-04-09 15:20:49.247二、从日期工夫中提取字段year,month,day/dayofmonth,hour,minute,secondExamples: > SELECT day('2009-07-30'); 30` * 1* 2dayofweek (1 = Sunday, 2 = Monday, …, 7 = Saturday),dayofyearExamples: > SELECT dayofweek('2009-07-30'); 5` * 1* 2weekofyearweekofyear(date) - Returns the week of the year of the given date. A week is considered to start on a Monday and week 1 is the first week with >3 days.Examples: > SELECT weekofyear('2008-02-20'); 8` * 1* 2trunc截取某局部的日期,其余局部默认为01第二个参数 [“year”, “yyyy”, “yy”, “mon”, “month”, “mm”]Examples: > SELECT trunc('2009-02-12', 'MM'); 2009-02-01 > SELECT trunc('2015-10-27', 'YEAR'); 2015-01-01` * 1* 2* 3* 4date_trunc [“YEAR”, “YYYY”, “YY”, “MON”, “MONTH”, “MM”, “DAY”, “DD”, “HOUR”, “MINUTE”, “SECOND”, “WEEK”, “QUARTER”]Examples: > SELECT date_trunc('2015-03-05T09:32:05.359', 'HOUR'); 2015-03-05T09:00:00` * 1* 2date_format将工夫转化为某种格局的字符串Examples: > SELECT date_format('2016-04-08', 'y'); 2016` * 1* 2三、日期工夫转换unix_timestamp返回以后工夫的unix工夫戳Examples: > SELECT unix_timestamp(); 1476884637 > SELECT unix_timestamp('2016-04-08', 'yyyy-MM-dd'); 1460041200` * 1* 2from_unixtime将工夫戳换算成以后工夫,to_unix_timestamp将工夫转化为工夫戳Examples: > SELECT from_unixtime(0, 'yyyy-MM-dd HH:mm:ss'); 1970-01-01 00:00:00 > SELECT to_unix_timestamp('2016-04-08', 'yyyy-MM-dd'); 1460041200` * 1* 2to_date/date将字符串转化为日期格局,to_timestamp(Since: 2.2.0) > SELECT to_date('2009-07-30 04:17:52'); 2009-07-30 > SELECT to_date('2016-12-31', 'yyyy-MM-dd'); 2016-12-31 > SELECT to_timestamp('2016-12-31 00:12:00'); 2016-12-31 00:12:00` * 1* 2* 3quarter 将1年4等分(range 1 to 4)Examples: > SELECT quarter('2016-08-31'); 3` * 1* 2四、日期、工夫计算months_between两个日期之间的月数months_between(timestamp1, timestamp2) - Returns number of months between timestamp1 and timestamp2.Examples: > SELECT months_between('1997-02-28 10:30:00', '1996-10-30'); 3.94959677` * 1* 2add_months返回日期后n个月后的日期Examples: > SELECT add_months('2016-08-31', 1); 2016-09-30` * 1* 2* 3last_day(date),next_day(start_date, day_of_week)Examples: > SELECT last_day('2009-01-12'); 2009-01-31 > SELECT next_day('2015-01-14', 'TU'); 2015-01-20` * 1* 2date_add,date_sub(减)date_add(start_date, num_days) - Returns the date that is num_days after start_date.Examples: > SELECT date_add('2016-07-30', 1); 2016-07-31` * 1datediff(两个日期间的天数)datediff(endDate, startDate) - Returns the number of days from startDate to endDate.Examples: > SELECT datediff('2009-07-31', '2009-07-30'); 1` * 1* 2对于UTC工夫to_utc_timestampto_utc_timestamp(timestamp, timezone) - Given a timestamp like ‘2017-07-14 02:40:00.0’, interprets it as a time in the given time zone, and renders that time as a timestamp in UTC. For example, ‘GMT+1’ would yield ‘2017-07-14 01:40:00.0’.Examples: > SELECT to_utc_timestamp('2016-08-31', 'Asia/Seoul'); 2016-08-30 15:00:00` * 1* 2from_utc_timestampfrom_utc_timestamp(timestamp, timezone) - Given a timestamp like ‘2017-07-14 02:40:00.0’, interprets it as a time in UTC, and renders that time as a timestamp in the given time zone. For example, ‘GMT+1’ would yield ‘2017-07-14 03:40:00.0’.Examples: > SELECT from_utc_timestamp('2016-08-31', 'Asia/Seoul'); 2016-08-31 09:00:00五、Hive罕用函数1.数学函数
...