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, 13:24
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