Return-Path: X-Original-To: apmail-hive-user-archive@www.apache.org Delivered-To: apmail-hive-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id AE0E3173B7 for ; Sun, 3 May 2015 23:19:59 +0000 (UTC) Received: (qmail 56872 invoked by uid 500); 3 May 2015 23:19:57 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 56803 invoked by uid 500); 3 May 2015 23:19:57 -0000 Mailing-List: contact user-help@hive.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@hive.apache.org Delivered-To: mailing list user@hive.apache.org Received: (qmail 56793 invoked by uid 99); 3 May 2015 23:19:57 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 03 May 2015 23:19:57 +0000 X-ASF-Spam-Status: No, hits=2.4 required=5.0 tests=FREEMAIL_ENVFROM_END_DIGIT,HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: message received from 54.164.171.186 which is an MX secondary for user@hive.apache.org) Received: from [54.164.171.186] (HELO mx1-us-east.apache.org) (54.164.171.186) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 03 May 2015 23:19:52 +0000 Received: from mail-ob0-f173.google.com (mail-ob0-f173.google.com [209.85.214.173]) by mx1-us-east.apache.org (ASF Mail Server at mx1-us-east.apache.org) with ESMTPS id E668742ACD for ; Sun, 3 May 2015 23:19:31 +0000 (UTC) Received: by obcux3 with SMTP id ux3so95779075obc.2 for ; Sun, 03 May 2015 16:18:46 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; bh=RxQzBncIBn9xBz8Uac+QQdGWWCDgBxTOD5UTd8yD3+k=; b=JXHzJLeNJ2yxx8ljx3Obnr0j9/5zLkp3xpW2NA2MZsEtkMkdmyljX1TtSy1y4a9QEk JONkkqdY2z87TJj1XuiXAAToyDA0kyhU6It2mcKphhTYcz474odZ/FnHQ9Maq7PAO88K WOuNHH8+wpDs3kTUaPuGr5+sfKlH2371VUkE0ISi6+ornxJyJs8wl2iEXv3hyY3bfMmY QRciD1zdpQiS1aSgVBpX303QaXpHrHFYoG9inp5+0+jKtp8aTjzC3QHbLC4Rj+luY0hw ccUOZpq1sIhJQU0+uZUhmihE82xcynzuPbwt1uLMpnfprh+xOKna9e44oaTNbGlb7CtR uk0w== MIME-Version: 1.0 X-Received: by 10.202.206.144 with SMTP id e138mr15515585oig.132.1430695126407; Sun, 03 May 2015 16:18:46 -0700 (PDT) Received: by 10.182.65.196 with HTTP; Sun, 3 May 2015 16:18:46 -0700 (PDT) In-Reply-To: <04be01d085e7$6afefde0$40fcf9a0$@co.uk> References: <04be01d085e7$6afefde0$40fcf9a0$@co.uk> Date: Mon, 4 May 2015 04:48:46 +0530 Message-ID: Subject: Re: comparing timestamp columns in Hive From: "Bhagwan S. Soni" To: user@hive.apache.org Content-Type: multipart/alternative; boundary=001a113ad9f0caa09e051535aa66 X-Virus-Checked: Checked by ClamAV on apache.org --001a113ad9f0caa09e051535aa66 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable I Tried it, and it seems it is working for me. In your case t and tmp are two tables, but what are the tmp.object_id ? can you provide t and tmp sample data! On Mon, May 4, 2015 at 2:53 AM, Mich Talebzadeh wrote= : > Hi, > > > > Just wanted to raise this one. > > > > Sounds like equating two columns of timestamp does not work in Hive. In > the following both *t.op_time* and *tmp* *maxDDLTime* columns are defined > as timestamp. When I do the following > > > > select t.object_id, t.op_type, t.op_time, tmp.maxDDLTime > > from t, tmp > > where t.object_id =3D tmp.object_id and t.op_time =3D tmp.maxDDLTime; > > > > it returns zero rows and does not work! > > > > However, when I cast timestamp columns to bigint it works > > > > select t.object_id, t.op_type, t.op_time, cast(t.op_time as bigint), > tmp.maxDDLTime, cast(tmp.maxDDLTime as bigint) > > from t, tmp > > where t.object_id =3D tmp.object_id and cast(t.op_time as bigint) =3D > cast(tmp.maxDDLTime as bigint); > > > > > +--------------+------------+------------------------+-------------+-----= -------------------+-------------+--+ > > | t.object_id | t.op_type | t.op_time | _c3 | > tmp.maxddltime | _c5 | > > > +--------------+------------+------------------------+-------------+-----= -------------------+-------------+--+ > > | 3644834 | 2 | 2015-05-01 12:42:51.0 | 1430480571 | > 2015-05-01 12:42:51.0 | 1430480571 | > > | 3636987 | 2 | 2015-05-01 12:42:51.0 | 1430480571 | > 2015-05-01 12:42:51.0 | 1430480571 | > > > +--------------+------------+------------------------+-------------+-----= -------------------+-------------+--+ > > > > Is this expected? In other words to equate timestamp columns do we need t= o > cast them to bigint or numeric? > > > > Thanks, > > > > Mich Talebzadeh > > > > http://talebzadehmich.wordpress.com > > > > Author of the books* "A Practitioner=E2=80=99s Guide to Upgrading to Syba= se** ASE > 15", **ISBN 978-0-9563693-0-7*. > > co-author *"Sybase Transact SQL Guidelines Best Practices", ISBN > 978-0-9759693-0-4* > > *Publications due shortly:* > > *Creating in-memory Data Grid for Trading Systems with Oracle TimesTen an= d > Coherence Cache* > > *Oracle and Sybase, Concepts and Contrasts*, ISBN: 978-0-9563693-1-4, vol= ume > one out shortly > > > > NOTE: The information in this email is proprietary and confidential. This > message is for the designated recipient only, if you are not the intended > recipient, you should destroy it immediately. Any information in this > message shall not be understood as given or endorsed by Peridale Ltd, its > subsidiaries or their employees, unless expressly so stated. It is the > responsibility of the recipient to ensure that this email is virus free, > therefore neither Peridale Ltd, its subsidiaries nor their employees acce= pt > any responsibility. > > > --001a113ad9f0caa09e051535aa66 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
I Tried it, and it seems it is working for me.
In your case t and tmp are two tables, but what are the tmp.obj= ect_id ?

can you provide t and tmp sample data!

On Mon, May 4, 2015 at= 2:53 AM, Mich Talebzadeh <mich@peridale.co.uk> wrote:
=

Hi,

<= p class=3D"MsoNormal">=C2=A0

Just wanted to raise this one.<= /p>

=C2=A0

Sounds like equating two columns of timestamp do= es not work in Hive. In the following both t.op_time and tmp<= /span> maxDDLTime columns are de= fined as timestamp. When I do the following

=C2=A0

select t.object_id, t.op_type, t.op_time, tmp.maxDDLTime<= u>

from t, tmp

where t.object_id =3D tmp.object_id and <= /span>t.op_time =3D tmp.maxDDLTime;

=C2=A0

it returns zero rows and does not work!=

=C2=A0

However, when I cast timestamp colum= ns to bigint it works

=C2=A0

select t.ob= ject_id, t.op_type, t.op_time, cast(t.op_time as bigint), tmp.maxDDLTime, c= ast(tmp.maxDDLTime as bigint)

from t, tmp

wher= e t.object_id =3D tmp.object_id and cast(t.op_time as bigint) = =3D cast(tmp.maxDDLTime as bigint);

=C2=A0

+--= ------------+------------+------------------------+-------------+----------= --------------+-------------+--+

| t.object_id=C2=A0 | t.op_type=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 t.op_time=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2= =A0=C2=A0=C2=A0 _c3=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0 tmp.m= axddltime=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0 _c5=C2=A0=C2=A0= =C2=A0=C2=A0|

+-----= ---------+------------+------------------------+-------------+-------------= -----------+-------------+--+

| 3644834=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | 2=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | 2015-05-01 12:42:51.0=C2=A0 | 1430480571= =C2=A0 | 2015-05-01 12:42:51.0=C2=A0 | 1430480571=C2=A0 |

| 3636987=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = | 2=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | 2015-05-01 12:4= 2:51.0=C2=A0 | 1430480571=C2=A0 | 2015-05-01 12:42:51.0=C2=A0 | 1430480571= =C2=A0 |

+-------------= -+------------+------------------------+-------------+---------------------= ---+-------------+--+

=C2=A0

Is this expected? In other words to equate timestamp columns d= o we need to cast them to bigint or numeric?

=C2=A0

Thanks,

=C2=A0

Mich = Talebzadeh

=C2= =A0

http://talebzadehmich.wordpress.com

= =C2=A0

Author of the books "A Practitioner=E2=80=99= s Guide to Upgrading to Sybase ASE 15", ISBN 978-0-9563693-0-7.

co-author "Sybase Transact SQL Guidelines Best Pr= actices", ISBN 978-0-9759693-0-4

Publications due shortly:

Creating in-mem= ory Data Grid for Trading Systems with Oracle TimesTen and Coherence Cache<= u>

Oracle and Sybase, Concepts and Contrasts, ISBN: 978-0-9563693-1-4, volume one out shortly

=C2=A0

NOTE: The informat= ion in this email is proprietary and confidential. This message is for the = designated recipient only, if you are not the intended recipient, you shoul= d destroy it immediately. Any information in this message shall not be unde= rstood as given or endorsed by Peridale Ltd, its subsidiaries or their empl= oyees, unless expressly so stated. It is the responsibility of the recipien= t to ensure that this email is virus free, therefore neither Peridale Ltd, = its subsidiaries nor their employees accept any responsibility.

<= /u>=C2=A0


--001a113ad9f0caa09e051535aa66--