Converting hive Timestamp functions to Redshift syntax

user_15948815 0
Viewed 99

I need to convert a script written in hive-SQL to Redshift's SQL.
I am stuck with the following piece:

select date_format(date_add(date_sub(current_date,cast(from_unixtime(unix_timestamp(current_date, 'yyyy-MM-dd'),'u') as int)),7), 'dd MMM')

Having no experience with timestamp functions of hive-sql, I am unable to re-write this line in redshift.
`current_date` is actually a timestamp column upon which some computation is done.
Please provide with some insight/suggestion. It will be really helpful.
amazon-redshift-spectrum amazon-redshift hiveql hive unix-timestamp
1 Answers

Your Hive query returns next Sunday, or current date if it is Sunday if I got it right. You can use date_part to check which is the day of week and next_day to get next Sunday. Something like this (not tested, do not have Redshift):

to_char(case when date_part(dow,current_date) = 0 then current_date 
              else next_day(current_date ,'Sunday')
         end, 'DD MON')
user_27003440
Related