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 >> order by date


Copy link to this message
-
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 [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, March 13, 2012 12:45 PM
> To: [EMAIL PROTECTED]
> 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     [EMAIL PROTECTED]     keithwiley.com    music.keithwiley.com

"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 happy."
                                           --  Edwin A. Abbott, Flatland
________________________________________________________________________________
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