-2

I want to basically translate the code below from Vertica to Hive Syntax:

AND     
DATE_TRUNC('WEEK',d.signup_timestamp) 
BETWEEN 
        DATE_TRUNC('WEEK',now()-INTERVAL '8 WEEKS') 
AND 
        DATE_TRUNC('WEEK',now()-INTERVAL '1 WEEK')
Gen Wan
  • 1,979
  • 2
  • 12
  • 19
Ahmed ElRaggal
  • 1

1 Answers1

0

Date_format function will be a good option here:

WHERE 
        DATE_FORMAT(d.signup_timestamp, '%V')
BETWEEN 
        DATE_FORMAT(CURRENT_DATE - INTERVAL 8 WEEK, '%V') 
AND 
        DATE_FORMAT(CURRENT_DATE - INTERVAL 1 WEEK, '%V')

Please note:

(Upper case)%V: Week where Sunday is the first day of the week (01 to 53).

(Lower case)%v: Week where Monday is the first day of the week (01 to 53).

Change it accordingly.

Update:

Current_date is available starting from Hive 1.2.0.

If you use the older version, please use this to replace the current_date.

TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP()))
Gen Wan
  • 1,979
  • 2
  • 12
  • 19
  • Thanks Gen for your input, still not working though. Giving me the following error... "java.lang.RuntimeException: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line 29:19 cannot recognize input near 'DATE_FORMAT' '(' 'CURRENT_DATE' in expression specification" – Ahmed ElRaggal Jan 06 '20 at 19:36
  • Current_Date is available in Hive 1.2.0. You can use TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP())) in lower version. – Gen Wan Jan 06 '20 at 19:38