hive-Time manipulation function

Date function Unix timestamp to date function: From_unixtime syntax: From_unixtime (bigint unixtime[, string format])
return value: String
Description: Convert Unix timestamp (from 1970-01-01 00:00:00 UTC to the number of seconds in the specified time) to the time format of the current time zone
Example:
Hive> Select From_unixtime (1323308943, ' YyyyMMdd ') from dual;
20111208


Get current UNIX timestamp function: Unix_timestamp syntax: Unix_timestamp ()
return value: bigint
Description: Gets the UNIX timestamp for the current time zone
Example:
Hive> Select Unix_timestamp () from dual;
1323309615


Date to UNIX timestamp function: Unix_timestamp syntax: Unix_timestamp (string date)
return value: bigint
Description: Converts the date of the format to "Yyyy-mm-dd HH:mm:ss" to a UNIX timestamp. If the conversion fails, 0 is returned.
Example:
Hive> Select Unix_timestamp (' 2011-12-07 13:01:03′) from dual;
1323234063


Specify format date to UNIX timestamp function: Unix_timestamp syntax: Unix_timestamp (string date, string pattern)
return value: bigint
Description: Converts the date of the pattern format to the Unix timestamp. If the conversion fails, 0 is returned.
Example:

Hive> Select Unix_timestamp (' 20111207 13:01:03′, ' YyyyMMdd HH:mm:ss ') from dual; 1323234063


Date time to date function: To_date syntax: to_date (string timestamp)
return value: String
Description: Returns the date part in a DateTime field.
Example:
Hive> Select To_date (' 2011-12-08 10:03:01′) from dual;
2011-12-08


Date following year function: Year Syntax: Year (string date)
return value: int
Description: Returns the year of the date.
Example:
Hive> Select year (' 2011-12-08 10:03:01′) from dual;
2011
Hive> Select year (' 2012-12-08′) from dual;

2012

Date-to-month function: Month Syntax: month (string date)
return value: int
Description: Returns the month of the date.
Example:
Hive> Select month (' 2011-12-08 10:03:01′) from dual;
12
Hive> Select month (' 2011-08-08′) from dual;
8


Date the following day function: Time Syntax: Days (string date)
return value: int
Description: Returns days in the date.
Example:
Hive> Select Day (' 2011-12-08 10:03:01′) from dual;
8
Hive> Select Day (' 2011-12-24′) from dual;
24


Date-to-hour function: Hour Syntax: Hour (string date)
return value: int
Description: Returns the hour in the date.
Example:
Hive> Select Hour (' 2011-12-08 10:03:01′) from dual;
10


Date-to-minute function: Minute Syntax: Minute (string date)
return value: int
Description: Returns the minute of the date.
Example:
hive> Select minute (' 2011-12-08 10:03:01′) from dual;
3


Date to seconds function: Second syntax: second (string date)
return value: int
Description: Returns the seconds in the date.
Example:
Hive> Select Second (' 2011-12-08 10:03:01′) from dual;
1


Date-to-week function: WeekOfYear syntax: WeekOfYear (string date)
return value: int
Description: Returns the number of days in the current week.
Example:
Hive> Select WeekOfYear (' 2011-12-08 10:03:01′) from dual;
49


Date comparison function: DateDiff syntax: DateDiff (String enddate, String startdate)
return value: int
Description: Returns the number of days from the end date minus the start date.
Example:
Hive> Select DateDiff (' 2012-12-08′, ' 2012-05-09′ ') from dual;
213


Date increment function: Date_add syntax: Date_add (string startdate, int days)
return value: String
Description: Returns the date that the start date startdate increased days.
Example:
Hive> Select Date_add (' 2012-12-08′,10) from dual;
2012-12-18

Date reduction function: Date_sub syntax: Date_sub (string startdate, int days)
return value: String
Description: Returns the date that the start date startdate reduced days.
Example:
Hive> Select Date_sub (' 2012-12-08′,10) from dual;

2012-11-28

Get current date Unix_timestamp ()

Select From_unixtime (Unix_timestamp (), ' Yyyy-mm-dd HH:mm:ss ') from dual;

From_unixtime (Unix_timestamp (), ' Yyyy-mm-dd HH:MM:SS ') as UpdateTime

A year ago

Select Date_sub (From_unixtime (Unix_timestamp (), ' Yyyy-mm-dd '), 365);

A year later

Select Date_add (From_unixtime (Unix_timestamp (), ' Yyyy-mm-dd '), 365);