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


Copy link to this message
-
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
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