sql - to_char(date_trunc in HIVE - Stack Overflow

In snowflake SQL I use

select
to_char(date_trunc('week',column_date),'yyyy-mm-dd') as week_date
,column_name
,count (column_name) as CN
from table_name

what is the Hive SQL version to count values per week?

In snowflake SQL I use

select
to_char(date_trunc('week',column_date),'yyyy-mm-dd') as week_date
,column_name
,count (column_name) as CN
from table_name

what is the Hive SQL version to count values per week?

• sql
• hive

Share Follow asked Jan 31 at 12:09 Jose AJose A 155 bronze badges 1

• If all else fails, run a datediff in days between your column_date and some fixed date Sunday in the past, and do an integer (truncating) divide by 7, and use that as your Group By. – Chris Maurer Commented Jan 31 at 13:47

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Assuming Monday is the first day of your week (which I'm pretty sure Oracle does too), you can use from_unixtime and unix_timestamp if needed.

(You can replace current_date with any date column (assuming it's yyyy-MM-dd))

cast(from_unixtime(unix_timestamp(current_date,'yyyy-MM-dd'), 'u') as int) will get you the day of the week as a number (Monday = 1,..., Sunday = 7)

Then we can subtract that from the current date to get the first day of the week:

date_sub(from_unixtime(unix_timestamp(current_date,'yyyy-MM-dd')), cast(from_unixtime(unix_timestamp(current_date,'yyyy-MM-dd'), 'u') AS int))

Then you can group by the result of that and count.