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 [mailto:kwi...@keithwiley.com] Sent: Tuesday, March 13, 2012 1:01 PM To: user@hive.apache.org 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 [mailto:kwi...@keithwiley.com] > Sent: Tuesday, March 13, 2012 12:45 PM > To: user@hive.apache.org > 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 kwi...@keithwiley.com 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 ________________________________________________________________________________