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 [nitinpawar...@gmail.com]
Sent: Tuesday, May 15, 2012 3:24 PM
To: user@hive.apache.org
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
<zoltan.tothczi...@softonic.com<mailto:zoltan.tothczi...@softonic.com>> 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
