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
