hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tucker, Matt" <>
Subject RE: order by date
Date Tue, 13 Mar 2012 17:35:19 GMT
I'm a bit confused.  It sounds like you're already storing your dates as strings in a 'yyyy-mm-dd'
format.  In that case, you can just sort by dateColName.  There's no issue with using UNIX_TIMESTAMP()
in the order by clause, as it outputs integer values.

Most of the date functions in hive take arguments in string format, with a few functions that
will translate between unix timestamps and datetime strings.

Matt Tucker

-----Original Message-----
From: Keith Wiley [] 
Sent: Tuesday, March 13, 2012 1:01 PM
Subject: Re: order by date

Is see, you store the date-time as a lexicographically sortable string.  That's fine, but
I'm operating on existing csv tables.  I guess I could whip up a hadoop job to convert all
the date-time columns to lexicographic strings and then wrap hive around the resulting converted
tables.  I was just wondering if there was a more direct approach, whether I could apply hive
to the original csv tables.

*Would* the date functions work in the way I suggested in my first post or do you think a
lexicographic conversion is practically required to perform date-ordering on query results?

On Mar 13, 2012, at 09:49 , Tucker, Matt wrote:

> Hi Keith,
> We generally store date columns as a string in a similar format to ISO 8601 (yyyy-mm-dd
hh:MM:ss).  This way, when we put the date column in the ORDER BY clause, it will be sorted
chronologically.  It also saves us the trouble of whipping out a unix timestamp calculator
to figure out what we're looking at.
> There is supposed to be a TIMESTAMP data type in Hive 0.8, but I haven't found any documentation
on it yet.
> Matt Tucker
> -----Original Message-----
> From: Keith Wiley []
> Sent: Tuesday, March 13, 2012 12:45 PM
> To:
> Subject: order by date
> I realize that hive doesn't have a date type for the columns and I realize that hive
*does* have various date functions.  I just haven't found a concrete example of how these
two issues are brought together.  Ordering the results of a sql query is done by adding "order
by dateColName" to the query.  Are the date functions supposed to be used in the form "order
by UNIX_TIMESTAMP(dateColName, 'yyyy-MM-dd')"?  Does the function just go right into the "order
by" clause like that or is that totally wrong?
> How does one order query results by a date column?  Am I on the right track?
> Thanks.

Keith Wiley

"Yet mark his perfect self-contentment, and hence learn his lesson, that to be self-contented
is to be vile and ignorant, and that to aspire is better than to be blindly and impotently
                                           --  Edwin A. Abbott, Flatland ________________________________________________________________________________

View raw message