Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Plain View
Hive >> mail # user >> Dealing with differents date format


+
Jérôme Verdier 2013-07-02, 09:42
+
Stephen Sprague 2013-07-02, 16:28
+
Jérôme Verdier 2013-07-03, 07:25
+
Nitin Pawar 2013-07-03, 07:29
+
Paul COURTOIS 2013-07-03, 07:41
+
Jérôme Verdier 2013-07-03, 09:00
Copy link to this message
-
Re: Dealing with differents date format
instead of into we have as in hive

so your query will be select min(dt_jour) as d_debut_semaine from table
where col = value
also remember this as is valid only till the query is being executed, it
wont be preserved once query execution is over
On Wed, Jul 3, 2013 at 2:30 PM, Jérôme Verdier
<[EMAIL PROTECTED]>wrote:

> 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 <[EMAIL PROTECTED]>
>
>> Hi jerome,
>>
>>
>>
>> What about the from_unixtime and unix_timestamp  Udf ?
>>
>>
>>
>>
>>
>> from_unixtime() which accept bigint
>>
>>
>>
>> my 2 cents
>>
>>
>>
>> Paul
>>
>>
>>
>> *De :* Nitin Pawar [mailto:[EMAIL PROTECTED]]
>> *Envoyé :* mercredi 3 juillet 2013 09:29
>> *À :* [EMAIL PROTECTED]
>> *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 <
>> [EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
>>
>> 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 <
>> [EMAIL PROTECTED]> 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
> [EMAIL PROTECTED]
>
>
--
Nitin Pawar
+
Stephen Sprague 2013-07-03, 17:37