hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jérôme Verdier <verdier.jerom...@gmail.com>
Subject Re: Dealing with differents date format
Date Wed, 03 Jul 2013 09:00:13 GMT
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 = 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 <paul@pole-conseils.com>

> 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é :* mercredi 3 juillet 2013 09:29
> *À :* 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érôme 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 <spragues@gmail.com>
>
> 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#LanguageManualUDF-DateFunctions
>
> seems to me you could pull the year and month from a date.  or if you have
> an int then do some arithmetic to get the year and month.  eg. year =
> floor( <your int>/10000) and month = cast( <your int> % 100 as int)  [% ==
> modulus operator]
>
> or am i not even answering your question?
>
>
>
>
>
> On Tue, Jul 2, 2013 at 2:42 AM, Jérôme Verdier <verdier.jerome66@gmail.com>
> 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érôme*
>
>
>
>
>
>
>
>
>
>
>
>
>
> --
> Nitin Pawar
>



-- 
*Jérôme VERDIER*
06.72.19.17.31
verdier.jerome66@gmail.com

Mime
View raw message