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


+
Zoltán Tóth-Czifra 2012-05-15, 13:11
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
+
Zoltán Tóth-Czifra 2012-05-15, 13:26
+
Tucker, Matt 2012-05-15, 13:33
+
Zoltán Tóth-Czifra 2012-05-15, 13:54
+
Nitin Pawar 2012-05-15, 14:33
+
Philip Tromans 2012-05-15, 15:23
+
Nitin Pawar 2012-05-15, 13:32
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