Return-Path: X-Original-To: apmail-hive-user-archive@www.apache.org Delivered-To: apmail-hive-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 60B32175E0 for ; Sun, 26 Oct 2014 19:12:46 +0000 (UTC) Received: (qmail 89548 invoked by uid 500); 26 Oct 2014 19:12:45 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 89481 invoked by uid 500); 26 Oct 2014 19:12:44 -0000 Mailing-List: contact user-help@hive.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@hive.apache.org Delivered-To: mailing list user@hive.apache.org Received: (qmail 89471 invoked by uid 99); 26 Oct 2014 19:12:44 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 26 Oct 2014 19:12:44 +0000 X-ASF-Spam-Status: No, hits=1.5 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of sjayatheertha@gmail.com designates 209.85.216.182 as permitted sender) Received: from [209.85.216.182] (HELO mail-qc0-f182.google.com) (209.85.216.182) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 26 Oct 2014 19:12:18 +0000 Received: by mail-qc0-f182.google.com with SMTP id m20so2862372qcx.13 for ; Sun, 26 Oct 2014 12:11:32 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; bh=j9VPmvAF/U78LmkhmLKPnAbuGqIPowGYTcp836QQsdU=; b=gr6CnL7atRTq2S2Wc5kIacApFY9btOcskqNoi38/vOsCGdb/saSSqc4LVDuZYbp+Ba RAPZcX7EX9Mo8Xt8U78JjgqSr/AAIj6oULvjuTu5K1L612JoA9Lmm/W+/iJ9qeVM87y4 yyW6PDrVDF71DjZ1aER75YHZW9QS3DRudOlKew7+4+XZpENqEOHTkCGBQ+XMeeuIYbzz lHpAtHxnArpmQa7pP7Yq5tULyK8ZO7cAqtuCwHbZqdeCwpc+rwxH6ARYbJzQroNkFIFN Y1GXZtGf2C6c9eor2YfdDUUfHzv/jLgzVG1PcD6Y+keTcuhQSHFBEEyRgKFKWGfVEP+d BSMw== MIME-Version: 1.0 X-Received: by 10.140.102.169 with SMTP id w38mr24758800qge.95.1414350692001; Sun, 26 Oct 2014 12:11:32 -0700 (PDT) Received: by 10.140.27.210 with HTTP; Sun, 26 Oct 2014 12:11:31 -0700 (PDT) In-Reply-To: References: Date: Sun, 26 Oct 2014 12:11:31 -0700 Message-ID: Subject: Re: Converting datime formats in Hive - please help From: Vidya Sujeet To: user@hive.apache.org Content-Type: multipart/alternative; boundary=001a11c16c7895caa50506582e0a X-Virus-Checked: Checked by ClamAV on apache.org --001a11c16c7895caa50506582e0a Content-Type: text/plain; charset=UTF-8 But what should the format be if the source has data coming *"25-FEB-01' ? appreciate your help.* On Sun, Oct 26, 2014 at 12:01 PM, Nagarjuna Vissarapu < nagarjuna.viss@gmail.com> wrote: > Timestamp class is there to do this. > On Oct 27, 2014 12:26 AM, "Vidya Sujeet" wrote: > >> Hi, >> >> The date time format coming from the source is* "25-FEB-01' .* I want to >> convert it to the following format. *'MM/DD/YYYY' *. How can we do this >> in Hive? >> >> >> I see that as per the documentation >> >> >> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions >> >> I could possibly convert the string date to a Unix time stamp in seconds >> using the below UDF. However, what is the string pattern if the date at >> source is coming this way* "25-FEB-01'* ? The link provided to look up >> for the patterns does not work. >> >> Please help. >> >> *Name:* unix_timestamp (string date, string pattern) function. >> *Description: *Convert time string with given pattern (see [ >> http://java.sun.com/j2se/1.4.2/docs/api/java/text/SimpleDateFormat.html]) >> to Unix time stamp (in seconds), return 0 if fail: >> unix_timestamp('2009-03-20', 'yyyy-MM-dd') = 1237532400. >> >> >> >> Vidya >> >> >> >> --001a11c16c7895caa50506582e0a Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
But what should the format be if the source has data comin= g=C2=A0"25-FEB-01'=C2=A0? appreciate your help.

On Sun, Oct 26, 2014 at 1= 2:01 PM, Nagarjuna Vissarapu <nagarjuna.viss@gmail.com> wrote:

Timestamp class = is there to do this.

On Oct 27, 2014 12:26 AM, "Vidya Sujeet&quo= t; <sjayath= eertha@gmail.com> wrote:
Hi,

The date time forma= t coming from the source is "25-FEB-01' . I want to convert= it to the following format. 'MM/DD/YYYY' . How can we do th= is in Hive?


I see that as per the documentat= ion=C2=A0

https://cwiki.ap= ache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateF= unctions

I could possibly convert the string date to a Unix time stamp in= seconds using the below UDF. However, what is the string pattern if the da= te at source is coming this way "25-FEB-01' ? The link prov= ided to look up for the patterns does not work.

Please help.

Name: unix_tim= estamp (string date, string pattern) function.=C2=A0
Description:=C2=A0Convert time string with given pattern (= see [http://java.sun.com/j2se/1= .4.2/docs/api/java/text/SimpleDateFormat.html]) to Unix time stamp (in = seconds), return 0 if fail: unix_timestamp('2009-03-20', 'yyyy-= MM-dd') =3D 1237532400.



Vidya




--001a11c16c7895caa50506582e0a--