How hive returns to the day of the week

People familiar with Hive know that in the native version, there is currently no function that returns the day of the week. In order to solve this problem, in addition to using Java to write their own UDF, you can also use the existing hive function implementation, the author compiled the following for your reference:

Hive Returns the day of the Week: Pmod (DateDiff (' #date # ', ' 2012 any Sunday '), 7). 2012-01-01 is just Sunday, you can remember as:

Method: Pmod (DateDiff (' #date # ', ' 2012-01-01 '), 7)  return value: int 0-6 ("0-6" for "Sunday-Saturday") Description: Returns the day of the week. Notice how the Pmod and DATEDIFF functions are used.

Analytical:

1, Dateiff is a function of two date subtraction, thehive Date function can be seen in Appendix:

? Date subtraction Function: Dateiff
Syntax: DateDiff (String enddate, String startdate)
return value: int
Description: Returns the number of days between two time parameters.

2, Pmod is the positive to take the remainder function:

? Positive withdraw function: Pmod
Syntax: pmod (int a, int b), Pmod (double A, double b)
return value: int double
Description: Returns the remainder of a positive a divided by B

Special Instructions:

This method is not the only way, but it uses the the features of the DATEDIFF and PMOD functions are used in combination. The same approach can be achieved for the same purpose in the internal cattle sharing method:

Pmod (DateDiff (#date #, ' 1920-01-01 ')-3, 7)

Appendix: Hive Date Function

Original: Https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions

return type	Function	Description
String	From_unixtime (bigint unixtime[, string format])	The Unix_timestamp parameter indicates that a value of ' yyyy-mm–dd HH:MM:SS ' or yyyymmddhhmmss.uuuuuu format is returned, depending on whether the function is used in a string or a numeric context. The value is expressed in the current time zone.
bigint	Unix_timestamp ()	If called without parameters, returns a Unix timestamp (from ' 1970-01–0100:00:00′ to now UTC seconds) as an unsigned integer.
bigint	Unix_timestamp (string date)	The specified date parameter calls Unix_timestamp (), which returns the number of seconds that the parameter value ' 1970-01–0100:00:00′ to the specified date.
bigint	Unix_timestamp (string date, string pattern)	Specify time input format, return to 1970 seconds: Unix_timestamp (' 2009-03-20′, ' yyyy-mm-dd ') = 1237532400 reference: Http://java.sun.com/j2se/1.4.2/docs /api/java/text/simpledateformat.html
String	To_date (string timestamp)	Month Day of Return time: To_date ("1970-01-01 00:00:00″) =" 1970-01-01″
String	To_dates (string date)	Returns a number of days (0 years since) given a date.
Int	Year (string date)	Returns the year of the specified time, in the range 1000 to 9999, or 0 for the "0" date.
Int	Month (string date)	Returns the month of the specified time, in the range of January-December, or a portion of 01 months, such as the date of ' 0000-00-00′ or ' 2008-00-00′.
Int	Day (string date) DayOfMonth (date)	Returns the date of the specified time
Int	Hour (string date)	Returns the hour of the specified time, ranging from 0 to 23.
Int	Minute (string date)	Returns the minute of the specified time, ranging from 0 to 59.
Int	Second (string date)	Returns the second of a specified time, ranging from 0 to 59.
Int	WeekOfYear (string date)	Returns the number of days of the year in which the specified date is, ranging from 0 to 53.
Int	DateDiff (String enddate, String startdate)	The difference between the dates of the two time parameters.
Int	Date_add (string startdate, int days)	Given time, this is based on the specified time period.
Int	Date_sub (string startdate, int days)	For a given time, subtract the specified time period on this basis.

How hive returns to the day of the week