You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Raihan Jamal <ja...@gmail.com> on 2012/07/19 01:48:34 UTC
Difference between timestamp is 15 minutes
This is the CREATED_TIME *`2012-07-17 00:00:22`* and this is the
Corresponding Timestamp *`1342508427000`*. Here timestamp is *5
seconds*more than the
*`CREATED_TIME`*. I need to see below scenario
Currently I have a query, in which I am joining on created_time and
timestamp like this-
ON (UNIX_TIMESTAMP(testingtable1.created_time) = (prod_and_ts.timestamps /
1000))
So in above case, it will not match as timestamp is 5 seconds more than
created_time. But I need if the difference between either of the two is
within 15 minutes then I need to match it.
So I need something like this-
ON (UNIX_TIMESTAMP(testingtable1.created_time) - (prod_and_ts.timestamps /
1000) = 15 minutes)
How I can do the above case if difference between timestamps is within 15
minutes then data will get matched by the above `ON clause`
*Raihan Jamal*
Re: Difference between timestamp is 15 minutes
Posted by Nitin Pawar <ni...@gmail.com>.
joins are on columns , what you are trying to do is where condition
On Thu, Jul 19, 2012 at 5:25 AM, Raihan Jamal <ja...@gmail.com> wrote:
> Something like this will work in Hive?
>
> *ON ((UNIX_TIMESTAMP(testingtable1.created_time) -
> (prod_and_ts.timestamps / 1000)) / 60* 1000 <= 15 minutes)*
>
>
>
>
> *Raihan Jamal*
>
>
>
> On Wed, Jul 18, 2012 at 4:48 PM, Raihan Jamal <ja...@gmail.com>wrote:
>
>> This is the CREATED_TIME *`2012-07-17 00:00:22`* and this is the
>> Corresponding Timestamp *`1342508427000`*. Here timestamp is *5 seconds*more than the
>> *`CREATED_TIME`*. I need to see below scenario
>>
>> Currently I have a query, in which I am joining on created_time and
>> timestamp like this-
>>
>> ON (UNIX_TIMESTAMP(testingtable1.created_time) = (prod_and_ts.timestamps
>> / 1000))
>>
>>
>> So in above case, it will not match as timestamp is 5 seconds more than
>> created_time. But I need if the difference between either of the two is
>> within 15 minutes then I need to match it.
>>
>> So I need something like this-
>>
>> ON (UNIX_TIMESTAMP(testingtable1.created_time) - (prod_and_ts.timestamps
>> / 1000) = 15 minutes)
>>
>>
>> How I can do the above case if difference between timestamps is within
>> 15 minutes then data will get matched by the above `ON clause`
>>
>>
>>
>> *Raihan Jamal*
>>
>>
>
--
Nitin Pawar
Re: Difference between timestamp is 15 minutes
Posted by Raihan Jamal <ja...@gmail.com>.
Something like this will work in Hive?
*ON ((UNIX_TIMESTAMP(testingtable1.created_time) - (prod_and_ts.timestamps
/ 1000)) / 60* 1000 <= 15 minutes)*
*Raihan Jamal*
On Wed, Jul 18, 2012 at 4:48 PM, Raihan Jamal <ja...@gmail.com> wrote:
> This is the CREATED_TIME *`2012-07-17 00:00:22`* and this is the
> Corresponding Timestamp *`1342508427000`*. Here timestamp is *5 seconds*more than the
> *`CREATED_TIME`*. I need to see below scenario
>
> Currently I have a query, in which I am joining on created_time and
> timestamp like this-
>
> ON (UNIX_TIMESTAMP(testingtable1.created_time) = (prod_and_ts.timestamps /
> 1000))
>
>
> So in above case, it will not match as timestamp is 5 seconds more than
> created_time. But I need if the difference between either of the two is
> within 15 minutes then I need to match it.
>
> So I need something like this-
>
> ON (UNIX_TIMESTAMP(testingtable1.created_time) - (prod_and_ts.timestamps /
> 1000) = 15 minutes)
>
>
> How I can do the above case if difference between timestamps is within
> 15 minutes then data will get matched by the above `ON clause`
>
>
>
> *Raihan Jamal*
>
>