hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Zoltán Tóth-Czifra <zoltan.tothczi...@softonic.com>
Subject RE: Date format - any easier way
Date Tue, 15 May 2012 13:54:01 GMT
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 [Matt.Tucker@disney.com]
Sent: Tuesday, May 15, 2012 3:33 PM
To: user@hive.apache.org
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:zoltan.tothczifra@softonic.com]
Sent: Tuesday, May 15, 2012 9:27 AM
To: user@hive.apache.org
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 [nitinpawar432@gmail.com]
Sent: Tuesday, May 15, 2012 3:24 PM
To: user@hive.apache.org<mailto: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.tothczifra@softonic.com<mailto:zoltan.tothczifra@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

Mime
View raw message