Summary of date functions in--hive:
--1. Time stamp function
--Date-time stamp: number of seconds from 1970-01-01 00:00:00 UTC to a specified time
Select Unix_timestamp (); --Get the current time zone's Unix timestamp
Select Unix_timestamp (' 2017-09-15 14:23:00 ');
Select Unix_timestamp (' 2017-09-15 14:23:00 ', ' yyyy-mm-dd HH:mm:ss ');
Select Unix_timestamp (' 20170915 14:23:00 ', ' yyyyMMdd HH:mm:ss ');
--time stamp turn date
Select From_unixtime (1505456567);
Select From_unixtime (1505456567, ' yyyyMMdd ');
Select From_unixtime (1505456567, ' yyyy-mm-dd HH:mm:ss ');
Select From_unixtime (Unix_timestamp (), ' yyyy-mm-dd HH:mm:ss '); --Get the system current time
--2. Get Current Date: current_date
Hive> Select Current_date from dual
2017-09-15
--3. Date time to date: To_date (string timestamp)
Hive> Select To_date (' 2017-09-15 11:12:00 ') from dual;
2017-09-15
--3. Get year/month/day/hour/minute/sec/week in date
With Dtime as (select From_unixtime (Unix_timestamp (), ' Yyyy-mm-dd HH:mm:ss ') as DT)
Select year (DT), month (DT), day (DT), hour (DT), Minute (DT), second (DT), weekofyear (DT)
From Dtime
--4. Calculate the number of days between two dates: DateDiff
Hive> Select DateDiff (' 2017-09-15 ', ' 2017-09-01 ') from dual;
14
--5. Date increase and Decrease: date_add/date_sub (string Startdate,int days)
Hive> Select Date_add (' 2017-09-15 ', 1) from dual;
2017-09-16
Hive> Select Date_sub (' 2017-09-15 ', 1) from dual;
2017-09-14
--Other date functions
Query current system time (including milliseconds): Current_timestamp;
Check the day of the Month: DayOfMonth (current_date);
Month End: Last_day (current_date)
1th Day of the Month: Date_sub (Current_date,dayofmonth (current_date)-1)
1th Day of next month: Add_months (date_sub (Current_date,dayofmonth (current_date)-1), 1)
