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 3D9AE100BD for ; Wed, 3 Jul 2013 09:00:41 +0000 (UTC) Received: (qmail 94562 invoked by uid 500); 3 Jul 2013 09:00:39 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 94511 invoked by uid 500); 3 Jul 2013 09:00:39 -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 94498 invoked by uid 99); 3 Jul 2013 09:00:38 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 03 Jul 2013 09:00:38 +0000 X-ASF-Spam-Status: No, hits=2.7 required=5.0 tests=FREEMAIL_ENVFROM_END_DIGIT,FREEMAIL_REPLY,HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of verdier.jerome66@gmail.com designates 209.85.216.178 as permitted sender) Received: from [209.85.216.178] (HELO mail-qc0-f178.google.com) (209.85.216.178) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 03 Jul 2013 09:00:34 +0000 Received: by mail-qc0-f178.google.com with SMTP id c11so4279158qcv.37 for ; Wed, 03 Jul 2013 02:00:13 -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=3G9uVNoIefUnUGkQJ/irz/Lgx7/znGACAILZPSe5j+Q=; b=JIsDmIXsx6NE/T/FAfIC3iN6Fk2L3P5BfS4Jv6NOW919qcAJVOXHSfQ4BBQXfIXpPB GeqB4yf5o8Qj0gkGjsRtYg0FQcHyMQyICyiysHK40NnhRvdB+SstiY98a/mp4kyLbYj+ bFYhX64Il1Cf4UKiQx8YF9YEHVkfgHnsxMVG89o9PbBP3vCP/ex7wVz4aZs8dfqtd64S NDHbOGZmlTYOtnEGjfawfE1Ww7FI1KjBy7VNw7m1MadshIJVOrMj0YB9hrrDRbfDYzjB u+6gnWdqX/ce/bFiNWAF6QIANkrrcPQTFILt3clEBhDISLT8tnnx6p/rhOaj6xRFJc4A JqkA== MIME-Version: 1.0 X-Received: by 10.229.168.132 with SMTP id u4mr1347269qcy.73.1372842013348; Wed, 03 Jul 2013 02:00:13 -0700 (PDT) Received: by 10.49.48.52 with HTTP; Wed, 3 Jul 2013 02:00:13 -0700 (PDT) In-Reply-To: <04ba9ce63b124698734814b3e727577c@mail.gmail.com> References: <04ba9ce63b124698734814b3e727577c@mail.gmail.com> Date: Wed, 3 Jul 2013 11:00:13 +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=e89a8f6475b189dc0004e097b049 X-Virus-Checked: Checked by ClamAV on apache.org --e89a8f6475b189dc0004e097b049 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi, Thanks for your help. I resolve the problem by changing my variable in_co_an_mois into a normal date format, and extract month and year by using apporopriate functions : year() and month(). But, i have a new question : the PL/SQL script i have to translate in hive is written like this : SELECT min(dt_jour) INTO D_debut_semaine FROM ods.calendrier WHERE co_an_semaine =3D in_co_an_sem; I have to record a value in a variable (here : D_debut_semaine) to use this later. Is there a way to do this in Hive ? 2013/7/3 Paul COURTOIS > Hi jerome, > > > > What about the from_unixtime and unix_timestamp Udf ? > > > > > > from_unixtime() which accept bigint > > > > my 2 cents > > > > Paul > > > > *De :* Nitin Pawar [mailto:nitinpawar432@gmail.com] > *Envoy=E9 :* mercredi 3 juillet 2013 09:29 > *=C0 :* user@hive.apache.org > *Objet :* Re: Dealing with differents date format > > > > easiest way in this kind would be write up a small udf. > > As Stephen suggested, its just a number so you can do maths to extract > year and month out of the number and then do the comparison. > > > > also 201307 is not a supported date format anywhere as per my knowledge > > > > On Wed, Jul 3, 2013 at 12:55 PM, J=E9r=F4me Verdier < > verdier.jerome66@gmail.com> wrote: > > 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 incremen= t > this (add one month/one year). > > Is there a way to do this in hive ? > > Thanks. > > > > > -- > *J=E9r=F4me* > > > > > > > > > > > > > > -- > Nitin Pawar > --=20 *J=E9r=F4me VERDIER* 06.72.19.17.31 verdier.jerome66@gmail.com --e89a8f6475b189dc0004e097b049 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
Hi,

Thanks for = your help.

I resolve the problem by changing my variable in_c= o_an_mois into a normal date format, and extract month and year by using ap= poropriate functions : year() and month().

But, i=A0 have a new question :

the PL/SQL script i = have to translate in hive is written like this :

SELECT min(dt_jour)
=A0=A0=A0 INTO D_debu= t_semaine
=A0=A0=A0 FROM ods.calendrier
=A0=A0=A0 WHERE co_an_semaine =3D in_co_an= _sem;

I have to record a value in a variable (here : D_= debut_semaine) to use this later.

Is there a way to do this in= Hive ?



2013/7/3 Paul COURTOIS <pa= ul@pole-conseils.com>

= Hi jerome,

=A0

What= about the from_unixtime and unix_timestamp =A0Udf ?

=A0

=A0<= /span>

from_unix= time() which accept bigint

=A0

my 2 cent= s

=A0

Paul

=A0<= /span>

De=A0: Nitin P= awar [mailto:n= itinpawar432@gmail.com]
Envoy=E9=A0: mercredi 3 juillet 2013 09:29
=C0=A0: user@hive.apache.org=
Objet=A0: Re: Dealing with differents date format

=A0

easiest way in this kind would be write up a small u= df.=A0

As Stephen suggested, its just a numb= er so you can do maths to extract year and month out of the number and then= do the comparison.=A0

=A0

= also 201307 is not a supported date format anywhere as per my knowledge=A0<= /p>

= =A0

On Wed, Jul 3, 2013 at 12:55 PM, J=E9r=F4me Verdier = <verdier= .jerome66@gmail.com> wrote:

Hi Stephen,

Thanks for your reply.

=

=A0

The problem is that my input date is this : in_co= _an_mois (format : YYYYMM, integer), for example, this month, we have 20130= 7

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 : 20= 09-03-20, but not with this unusual format : 201307.

Thanks.

=A0

=A0

=A0

2013/7/2 Stephen Sprague <spragues@gmail.com>

not su= re i fully understand your dilemma.=A0=A0=A0 have you investigated any of t= he date functions listed here?

https://cwiki.apache.org/confluence/display/Hive/Language= Manual+UDF#LanguageManualUDF-DateFunctions

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

or am i not even answering your question?

=

=A0

<= div>

=A0

=

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

Hi,

i tryin= g to translate some PL/SQL script in HiveQL, and dealing with unusual date = format.

i ad= ded a variable in my hive script : '${hiveconf:in_co_an_mois}' whic= h 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 m= onth/one year).

Is there a way to do this in hive ?

Thanks.=




--
J= =E9r=F4me

=A0

=

=A0

=A0

=A0



=A0

--
Nitin Pawar




--
J=E9r=F4me VERDIER
06.72.19.17.31

--e89a8f6475b189dc0004e097b049--