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
+
Nitin Pawar 2012-05-15, 13:24
+
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
Copy link to this message
-
Re: Date format - any easier way
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
+
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