From user-return-2336-apmail-drill-user-archive=drill.apache.org@drill.apache.org Fri May 29 20:41:29 2015 Return-Path: X-Original-To: apmail-drill-user-archive@www.apache.org Delivered-To: apmail-drill-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 6649118447 for ; Fri, 29 May 2015 20:41:29 +0000 (UTC) Received: (qmail 54866 invoked by uid 500); 29 May 2015 20:41:29 -0000 Delivered-To: apmail-drill-user-archive@drill.apache.org Received: (qmail 54806 invoked by uid 500); 29 May 2015 20:41:29 -0000 Mailing-List: contact user-help@drill.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@drill.apache.org Delivered-To: mailing list user@drill.apache.org Received: (qmail 54795 invoked by uid 99); 29 May 2015 20:41:28 -0000 Received: from Unknown (HELO spamd4-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 29 May 2015 20:41:28 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd4-us-west.apache.org (ASF Mail Server at spamd4-us-west.apache.org) with ESMTP id 5C7A4C0B58 for ; Fri, 29 May 2015 20:41:28 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd4-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=0 tagged_above=-999 required=6.31 tests=[SPF_PASS=-0.001, URIBL_BLOCKED=0.001] autolearn=disabled Received: from mx1-eu-west.apache.org ([10.40.0.8]) by localhost (spamd4-us-west.apache.org [10.40.0.11]) (amavisd-new, port 10024) with ESMTP id UvakkNzA6hRc for ; Fri, 29 May 2015 20:41:18 +0000 (UTC) Received: from mail-pd0-f173.google.com (mail-pd0-f173.google.com [209.85.192.173]) by mx1-eu-west.apache.org (ASF Mail Server at mx1-eu-west.apache.org) with ESMTPS id 9794F209DC for ; Fri, 29 May 2015 20:41:17 +0000 (UTC) Received: by pdbnf5 with SMTP id nf5so239120pdb.2 for ; Fri, 29 May 2015 13:40:31 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:content-type:mime-version:subject:from :in-reply-to:date:content-transfer-encoding:message-id:references:to; bh=SVG3k/Sj3EUhqkZ2mFIg37f4O+C3kKEXrmP++NvLCFA=; b=gt5KHYGnGDoA+mftjmwytU8WszpMMNbbN8u342nwnsYntIqgWyuhRktlPu0D4ay1no 8nRrkoYGhjiiyDgW902Ac/ysS/pGjYvRn1kT662yFBOOB6X2Lo/mLlJeJLIkMGOe2vIJ l86XRcnxaH2Op+BmyCvWkjmptadFb62zh0LfVLmdepYFvFhzxUY2/G66hErWcZhwsfM+ ++dzppExdrAMOzQ4F19zfdUqs0McwiXCzBlq+vAKm9UAxd6qOQxe9gk90015ROKXRoY8 nAOpxyKIFy2KKWyfOzjgj+QvDQdU2bFyrOuyGDr/Co1i9pKKFligHtmaLHnFYywIG+hf GN8g== X-Gm-Message-State: ALoCoQnyKQzt43S8wUUqnnYD62WUq5TOyVyM9wXhUbzr+g+Cje2sT7Gqz1HFL+WPrc5GSbimAc6A X-Received: by 10.66.156.198 with SMTP id wg6mr18140864pab.126.1432932031120; Fri, 29 May 2015 13:40:31 -0700 (PDT) Received: from [192.168.1.7] (68-186-49-95.dhcp.mdfd.or.charter.com. [68.186.49.95]) by mx.google.com with ESMTPSA id to6sm6476266pbc.19.2015.05.29.13.40.30 for (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Fri, 29 May 2015 13:40:30 -0700 (PDT) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 7.3 \(1878.6\)) Subject: Re: convert unix timestamp From: Andries Engelbrecht In-Reply-To: Date: Fri, 29 May 2015 13:40:28 -0700 Content-Transfer-Encoding: quoted-printable Message-Id: <58AD6D52-A9D4-4346-8DD2-31AE5B18CC9F@maprtech.com> References: To: user@drill.apache.org X-Mailer: Apple Mail (2.1878.6) Good call Kristine!!! Seems to_timestamp is the better way to go that using from_unixtime. select date_part('hour', to_timestamp(1432912733)) from `sys`.`version`; +---------+ | EXPR$0 | +---------+ | 15 | +---------+ 1 row selected (0.171 seconds) =E2=80=94Andries On May 29, 2015, at 1:22 PM, Kristine Hahn wrote: > I think TO_TIMESTAMP is what you're looking for. Please see > http://drill.apache.org/docs/data-type-conversion/#to_timestamp. >=20 > Kristine Hahn > Sr. Technical Writer > 415-497-8107 @krishahn >=20 >=20 > On Fri, May 29, 2015 at 11:40 AM, Christopher Matta = wrote: >=20 >> I need to be able to convert a unix timestamp to a drill timestamp. >>=20 >> I can use the from_unixtime function return what seems like a = timestamp, or >> a string formatted correctly, however it seems like it doesn=E2=80=99t = return it as >> a string or as a timestamp type: >>=20 >> 0: jdbc:drill:zk=3Dsen11:5181,sen12:5181> select >> from_unixtime(1432912733) from `sys`.`version`; >> +----------------------+ >> | EXPR$0 | >> +----------------------+ >> | 2015-05-29 15:18:53 | >> +----------------------+ >> 1 row selected (0.134 seconds) >>=20 >> Trying to run date-specific functions on the result: >>=20 >> 0: jdbc:drill:zk=3Dsen11:5181,sen12:5181> select datepart('hour', >> from_unixtime(1432912733)) from `sys`.`version`; >> Error: PARSE ERROR: =46rom line 1, column 8 to line 1, column 50: No >> match found for function signature datepart(, ) >>=20 >> [Error Id: aa906f4b-fa49-4a3d-9a7c-0063c7c6c97b on >> se-node10.se.lab:31010] (state=3D,code=3D0) >>=20 >> Trying to cast it to a timestamp returns an error: >>=20 >> 0: jdbc:drill:zk=3Dsen11:5181,sen12:5181> select >> CAST(from_unixtime(1432912733) as TIMESTAMP) from `sys`.`version`; >> Error: SYSTEM ERROR: >> org.apache.drill.exec.exception.SchemaChangeException: Failure while >> trying to materialize incoming schema. Errors: >>=20 >> Error in expression at index -1. Error: Missing function >> implementation: [castBIGINT(VAR16CHAR-OPTIONAL)]. Full expression: >> --UNKNOWN EXPRESSION--.. >>=20 >> Fragment 0:0 >>=20 >> [Error Id: 7e75d4b9-a478-40cd-80b4-fda27815b9ef on >> se-node10.se.lab:31010] (state=3D,code=3D0) >>=20 >> Any ideas? >>=20 >> Chris Mattacmatta@mapr.com >> 215-701-3146 >> =E2=80=8B >>=20