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

Switch to Threaded View
Hive, mail # user - Date format - any easier way


Copy link to this message
-
Re: Date format - any easier way
Nitin Pawar 2012-05-15, 14:33
I will write an UDF for array concatenation and upload on GIT if anyone
does not have it already

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

>  Matt, thanks!
>
>  Luckily the order of the parts of the date is correct (reordering them
> would bet he same craziness).
>
>  Finally it is:
>
>  regexp_replace(
> date_sub(
> to_date(
> from_unixtime(
> unix_timestamp()
> )
> ), 1
> ), "[-]", ""
> )
>
>  Nitin, concat apparently doesn't take arrays, and I did not find any
> other way to join arrays in HQL. However, it would be very handy.
>
>  Thanks guys!
>
>  ------------------------------
> *From:* Tucker, Matt [[EMAIL PROTECTED]]
> *Sent:* Tuesday, May 15, 2012 3:33 PM
>
> *To:* [EMAIL PROTECTED]
> *Subject:* RE: Date format - any easier way
>
>   What about wrapping it in regexp_replace(…, “[-]”, “”) ?  It may not be
> the cleanest, but I’d recommend passing variables from the shell :)
>
>
>
> Matt Tucker
>
>
>
> *From:* Zoltán Tóth-Czifra [mailto:[EMAIL PROTECTED]]
> *Sent:* Tuesday, May 15, 2012 9:27 AM
> *To:* [EMAIL PROTECTED]
> *Subject:* RE: Date format - any easier way
>
>
>
> 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