Reply
New Contributor
Posts: 2
Registered: ‎03-01-2016

Impala weekofyear

I'm trying to group things by week (Sun - Sat). First, the trunc(`date_column`,'ww') is not that useful as the first day of the week is based on Jan 1 of the year. I tried to use weekofyear() but notice the following behaviour:

 

select weekofyear('2013-12-29') -- result: 52, ok

select weekofyear('2013-12-30') -- result: 1??

select weekofyear('2013-12-31') -- result: 1??

select weekofyear('2014-12-31') -- result: 1??

select weekofyear(‘2015-12-31’) -- result: 53 , ok
select weekofyear(‘2016-12-31’) -- result: 52 , ok
select weekofyear(‘2017-12-31’) -- result: 52, ok
select weekofyear(‘2018-12-31’) -- result: 1 ?

 

is this a bug?

Cloudera Employee
Posts: 737
Registered: ‎03-23-2015

Re: Impala weekofyear

Which version of CDH are you using?
Cloudera Employee
Posts: 737
Registered: ‎03-23-2015

Re: Impala weekofyear

I have tested, this is correct, the same result returned from Hive, Impala and MySQL:

 

MariaDB [(none)]> select weekofyear('2018-12-31');
+--------------------------+
| weekofyear('2018-12-31') |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.93 sec)


0: jdbc:hive2://localhost> select weekofyear('2018-12-31');
INFO  : Compiling command(queryId=hive_20180102202424_a6781c36-3b54-4a0d-a7f3-89b01d6533ef): select weekofyear('2018-12-31')
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:_c0, type:int, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=hive_20180102202424_a6781c36-3b54-4a0d-a7f3-89b01d6533ef); Time taken: 0.161 seconds
INFO  : Executing command(queryId=hive_20180102202424_a6781c36-3b54-4a0d-a7f3-89b01d6533ef): select weekofyear('2018-12-31')
INFO  : Completed executing command(queryId=hive_20180102202424_a6781c36-3b54-4a0d-a7f3-89b01d6533ef); Time taken: 0.005 seconds
INFO  : OK
+------+--+
| _c0  |
+------+--+
| 1    |
+------+--+

The reason is that 2018-12-31 falls to the same week in the new year and it is considered as the first week of the 2019. So I think it is correct result.