Hive DateTime Truncators (QUARTER, WEEK, DAYOFWEEK)?

Question

Is there any expressions to get QUARTER, WEEK and DAYOFWEEK of a DateTime field in Hive (v0.14.1) which do the same thing as these buildin functions in MySql?

here is the specification of what I want (from MySql doc):

QUARTER WEEK DAYOFWEEK

NOTE: 1. the function quarter() was introduced in Hive 1.3, but I need a expr to support lower version. 2. the function weekofyear() is supported instead of week(), but there is a little difference. but it's okay, so just ignore this one.

Answer 1

Suppose the DateTime field is order_time

• DAYOFWEEK: PMOD(DATEDIFF(order_time, '2012-01-01'), 7)
• WEEK: WEEKOFYEAR(order_time)
• QUARTER (hive <1.3): (INT((MONTH(order_time)-1)/3)+1)
• QUARTER (hive >=1.3): QUARTER(order_time)