Hi,

How do I get the current date in Hive? Specifically, I’m
looking for the equivalent of following SQL where clause:

 

where LOCAL_DT >= current date - 3 day

 

I tried using

 

where local_dt >= date_sub(to_date(unix_timestamp()), 3)

 

but this method seems to be many times slower than hardcoding the date. local_dt is the local date and also the partition key for the table.

     

Hi Saurabh,
You might want to consider populating the right hand side of this expression
local_dt >= date_sub(to_date(unix_timestamp()), 3)

in the Hive client code that is issuing the Hive query.

Mark

Mark Grover, Business Intelligence Analyst
OANDA Corporation

www: oanda.com www: fxtrade.com
e: [EMAIL PROTECTED]

"Best Trading Platform" - World Finance's Forex Awards 2009.
"The One to Watch" - Treasury Today's Adam Smith Awards 2009.
----- Original Message -----
From: "Saurabh S" <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Sent: Wednesday, April 25, 2012 8:48:46 PM
Subject: Get current date in hive
Hi,

How do I get the current date in Hive? Specifically, I’m looking for the equivalent of following SQL where clause:

where LOCAL_DT >= current date - 3 day

I tried using

where local_dt >= date_sub(to_date(unix_timestamp()), 3)

but this method seems to be many times slower than hardcoding the date. local_dt is the local date and also the partition key for the table.

How about passing it outside of hive? (calculating it in bash etc.)

Note, unix_timestamp is non-deterministic udf and hence would not help you
in partition pruning.

Alternatively, you can develop your own current_date (trivial) udf
(deterministic).

Thanks,
Aniket

On Wed, Apr 25, 2012 at 5:54 PM, Mark Grover <[EMAIL PROTECTED]> wrote:

> Hi Saurabh,
> You might want to consider populating the right hand side of this
> expression
> local_dt >= date_sub(to_date(unix_timestamp()), 3)
>
> in the Hive client code that is issuing the Hive query.
>
> Mark
>
> Mark Grover, Business Intelligence Analyst
> OANDA Corporation
>
> www: oanda.com www: fxtrade.com
> e: [EMAIL PROTECTED]
>
> "Best Trading Platform" - World Finance's Forex Awards 2009.
> "The One to Watch" - Treasury Today's Adam Smith Awards 2009.
>
>
> ----- Original Message -----
> From: "Saurabh S" <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> Sent: Wednesday, April 25, 2012 8:48:46 PM
> Subject: Get current date in hive
>
>
> Hi,
>
>
>
> How do I get the current date in Hive? Specifically, I’m looking for the
> equivalent of following SQL where clause:
>
>
>
> where LOCAL_DT >= current date - 3 day
>
>
>
> I tried using
>
>
>
> where local_dt >= date_sub(to_date(unix_timestamp()), 3)
>
>
>
> but this method seems to be many times slower than hardcoding the date.
> local_dt is the local date and also the partition key for the table.
>

--
"...:::Aniket:::... Quetzalco@tl"