Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

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


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
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB