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 Plain View
Hive >> mail # user >> Date format - any easier way


+
Zoltán Tóth-Czifra 2012-05-15, 13:11
+
Nitin Pawar 2012-05-15, 13:24
+
Zoltán Tóth-Czifra 2012-05-15, 13:26
+
Tucker, Matt 2012-05-15, 13:33
+
Zoltán Tóth-Czifra 2012-05-15, 13:54
+
Nitin Pawar 2012-05-15, 14:33
+
Philip Tromans 2012-05-15, 15:23
Copy link to this message
-
Re: Date format - any easier way
may be something like this will work

can you try using concat(split(date_sub(),"-")))

split returns the array and then you can concat them as you want

if this does not work for you, writing a simple UDF is easy as well

Thanks,
nitin
On Tue, May 15, 2012 at 6:56 PM, Zoltán Tóth-Czifra <
[EMAIL PROTECTED]> wrote:

>  Nitin,
>
>  Thank you. As you see below I know and use this function. My problem is
> that it doesn't give YYYYMMDD format, but YYYY-MM-DD instead, and
> formatting is not trivial as you can see it too.
>
>  ------------------------------
> *From:* Nitin Pawar [[EMAIL PROTECTED]]
> *Sent:* Tuesday, May 15, 2012 3:24 PM
> *To:* [EMAIL PROTECTED]
> *Subject:* Re: Date format - any easier way
>
>  you may want to have a look at this function
>
>    date_sub(string startdate, int days) Subtract a number of days to
> startdate: date_sub('2008-12-31', 1) = '2008-12-30'
> On Tue, May 15, 2012 at 6:41 PM, Zoltán Tóth-Czifra <
> [EMAIL PROTECTED]> wrote:
>
>>  Hi guys,
>>
>>  Thanks you very much in advance for your help.
>>
>>  My problem in short is getting the date for yesterday in a YYYYMMDD
>> format. As I use this format for partitions, I need this format in quite
>> some queries.
>>
>>  So far I have this:
>>
>>  concat(
>> year( date_sub( to_date( from_unixtime( unix_timestamp() ) ), 1 ) ),
>> CASE
>> WHEN month( date_sub( to_date( from_unixtime( unix_timestamp() ) ), 1 ) )
>> < 10
>> THEN concat( '0', month( date_sub( to_date( from_unixtime(
>> unix_timestamp() ) ), 1 ) ) )
>> ELSE trim( month( date_sub( to_date( from_unixtime( unix_timestamp() ) ),
>> 1 ) ) )
>> END,
>> CASE
>> WHEN day( date_sub( to_date( from_unixtime( unix_timestamp() ) ), 1 ) ) <
>> 10
>> THEN concat( '0', day( date_sub( to_date( from_unixtime( unix_timestamp()
>> ) ), 1 ) ) )
>> ELSE trim(day( date_sub( to_date( from_unixtime( unix_timestamp() ) ), 1
>> ) ) )
>> END
>> );
>>
>>
>>  ...but it seems to be a bit crazy, especially if you have to repeat it
>> in hundreds of queries. Is there any other (better) way to get this format
>> from yesterday? - there has to be. As I can't use local user variables nor
>> macros whatsoever, I need to repeat myself a lot here. If there is no other
>> way, probably I need to change my partitions.
>>
>>  Any ideas are appreciated. Thank you!
>>
>>  Zoltan
>>
>
>
>
>  --
> Nitin Pawar
>
>
--
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