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 E2CB7CD99 for ; Wed, 3 Jul 2013 07:26:25 +0000 (UTC) Received: (qmail 42000 invoked by uid 500); 3 Jul 2013 07:26:23 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 41903 invoked by uid 500); 3 Jul 2013 07:26:23 -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 41892 invoked by uid 99); 3 Jul 2013 07:26:22 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 03 Jul 2013 07:26:22 +0000 X-ASF-Spam-Status: No, hits=1.7 required=5.0 tests=FREEMAIL_ENVFROM_END_DIGIT,HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of verdier.jerome66@gmail.com designates 209.85.128.45 as permitted sender) Received: from [209.85.128.45] (HELO mail-qe0-f45.google.com) (209.85.128.45) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 03 Jul 2013 07:26:15 +0000 Received: by mail-qe0-f45.google.com with SMTP id w7so2916671qeb.18 for ; Wed, 03 Jul 2013 00:25:54 -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=YN1YhwYCMf6y3g/vEUCSUGUWoQ6M3foWDSe8owIqFVY=; b=RBXXIeI+iiFc+MV1BSuet5iQHKbs8BO0EzLQbmGwDtW8HLqaqQbT+UEvlyKygsBxHm yyG9ICHLBlGrSQyoiPf2TOZodk20USpkJMBnZEWaAocyqMfEFSLmvxrw7SE9zT84ur3S 57G9g1DYA5URtCtt2oSbDR4C/Ehk4gb5xSxpDyrkGHk9L4uP2a//T3RnBuxMkDvVAXQd aS4v5ZRr6KgM55maRDROVfcO4/JsWBmIH5NQBjJkIa2bqfvBHm4/aeUF/Rpjo/qm+GAi 09KKQq0LfJezRvC3PtqBvqHEk1N/opo9D82ESIvTX34QympJQ6hZD//Wp7XN09l/I6i3 WZzA== MIME-Version: 1.0 X-Received: by 10.224.63.7 with SMTP id z7mr2091115qah.51.1372836354620; Wed, 03 Jul 2013 00:25:54 -0700 (PDT) Received: by 10.49.48.52 with HTTP; Wed, 3 Jul 2013 00:25:54 -0700 (PDT) In-Reply-To: References: Date: Wed, 3 Jul 2013 09:25:54 +0200 Message-ID: Subject: Re: Dealing with differents date format From: =?ISO-8859-1?Q?J=E9r=F4me_Verdier?= To: user@hive.apache.org Content-Type: multipart/alternative; boundary=047d7bdc77aa4081d104e0965f0e X-Virus-Checked: Checked by ClamAV on apache.org --047d7bdc77aa4081d104e0965f0e Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi Stephen, Thanks for your reply. The problem is that my input date is this : in_co_an_mois (format : YYYYMM, integer), for example, this month, we have 201307 and i have to deal with this date : add one month, compare to over date, etc... The problem is that apparently, there is no way to do this, because Hive can't deal with this type of data because it's not a date format. For hive, this is just a number. Hive can deal with this : 1970-01-01 00:00:00, or this : 2009-03-20, but not with this unusual format : 201307. Thanks. 2013/7/2 Stephen Sprague > not sure i fully understand your dilemma. have you investigated any of > the date functions listed here? > > > https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#Langu= ageManualUDF-DateFunctions > > seems to me you could pull the year and month from a date. or if you hav= e > an int then do some arithmetic to get the year and month. eg. year =3D > floor( /10000) and month =3D cast( % 100 as int) [%= =3D=3D > modulus operator] > > or am i not even answering your question? > > > > On Tue, Jul 2, 2013 at 2:42 AM, J=E9r=F4me Verdier > wrote: > >> Hi, >> >> i trying to translate some PL/SQL script in HiveQL, and dealing with >> unusual date format. >> >> i added a variable in my hive script : '${hiveconf:in_co_an_mois}' which >> is a year/month date format, like this : 201307 (INT format). >> >> I would like to transform this in date format, because i have to >> increment this (add one month/one year). >> >> Is there a way to do this in hive ? >> >> Thanks. >> >> >> >> -- >> *J=E9r=F4me* >> >> > --047d7bdc77aa4081d104e0965f0e Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
Hi Stephen,

Thanks for your re= ply.

The problem is that my input date is this : in= _co_an_mois (format : YYYYMM, integer), for example, this month, we have 20= 1307

and i have to deal with this date : add one month, compare t= o over date, etc...

The problem is that apparently, there= is no way to do this, because Hive can't deal with this type of data b= ecause it's not a date format.

For hive, this is just a number.

Hive can = deal with this : 1970-01-01 00:00:00, or this : 2009-03-20, but not with th= is unusual format : 201307.

Thanks.




201= 3/7/2 Stephen Sprague <spragues@gmail.com>
not sure i fully understand your dilemma.=A0=A0= =A0 have you investigated any of the date functions listed here?

https://cwiki.apache.or= g/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunction= s

seems to me you could pull the year and month from a date.=A0 or = if you have an int then do some arithmetic to get the year and month.=A0 eg= . year =3D floor( <your int>/10000) and month =3D cast( <your int&= gt; % 100 as int)=A0 [% =3D=3D modulus operator]

or am i not even answering your question?

=


On Tue, Jul 2, 2013 at = 2:42 AM, J=E9r=F4me Verdier <verdier.jerome66@gmail.com> wrote:
Hi= ,

i trying to translate some PL/SQL script in HiveQL, and dea= ling with unusual date format.

i added a variable in my hive script : '${hiveconf:in_co_an_m= ois}' which is a year/month date format, like this : 201307 (INT format= ).

I would like to transform this in date format, because i have to = increment this (add one month/one year).

Is there a way to do = this in hive ?

Thanks.



--
J=E9r=F4me




--047d7bdc77aa4081d104e0965f0e--