Mailing-List: contact user-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@hive.apache.org
Received-SPF: pass (athena.apache.org: domain of kwiley@keithwiley.com
 designates 69.93.164.27 as permitted sender)
Content-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Apple Message framework v1084)
Subject: Re: order by date
From: Keith Wiley <kwiley@keithwiley.com>
In-Reply-To: 
 <4CED3A5A427FF34BAEE89C98D64D336D1F89D7FADB@SM-FLOR-VXMB06B.wdw.disney.com>
Date: Tue, 13 Mar 2012 10:47:12 -0700
Content-Transfer-Encoding: quoted-printable
Message-Id: <092C1FB3-CC43-48D6-9ADB-5A238A039DA5@keithwiley.com>
References: <3DA234E7-77C8-493C-B663-66960C88EC68@keithwiley.com>
 <4CED3A5A427FF34BAEE89C98D64D336D1F89D7FA65@SM-FLOR-VXMB06B.wdw.disney.com>
 <8174D6C6-A502-4F56-A093-7F352756EAD8@keithwiley.com>
 <4CED3A5A427FF34BAEE89C98D64D336D1F89D7FADB@SM-FLOR-VXMB06B.wdw.disney.com>
To: user@hive.apache.org

I see how I misled you, sorry.  I wasn't implying that my csv data is =
cleanly represented in yyyy-MM-dd format.  I was just asking =
syntactically how to use date functions in HiveQL because I hadn't found =
any examples and I used yyyy-MM-dd in my example.  The dates in my csv =
tables are often in "American" format, month first without leading =
zeroes, e.g., "3/31/2012 7:00".  The lack of leading zeroes and the =
unsortabled date format make the dates difficult to work with.  I was =
thinking I could use the date functions with some other format to sort =
them (I guess it would be "M/d/yyyy h:mm" or something like that).

I admit, I didn't actually go to the trouble of providing the correct =
pattern string in my earlier post, I was focused on the HiveQL syntax in =
that post, not the precise date pattern given to the date function.

So yeah, I'm still trying to determine the best way to sort queries =
against the date-time columns.  One option is to read/write the entire =
tables with a date conversion to a lexicographic format.  Another option =
-- my original question in this thread -- was how I might use hive's =
date functions at the time a query is performed.

What do you think is the best way to deal with this?

Thanks.

On Mar 13, 2012, at 10:35 , Tucker, Matt wrote:

> 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.
>=20
> 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.
>=20
> Matt Tucker


=
__________________________________________________________________________=
______
Keith Wiley     kwiley@keithwiley.com     keithwiley.com    =
music.keithwiley.com

"I do not feel obliged to believe that the same God who has endowed us =
with
sense, reason, and intellect has intended us to forgo their use."
                                           --  Galileo Galilei
=
__________________________________________________________________________=
______