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 69EF6C230 for ; Tue, 15 May 2012 13:11:38 +0000 (UTC) Received: (qmail 38188 invoked by uid 500); 15 May 2012 13:11:37 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 38135 invoked by uid 500); 15 May 2012 13:11:37 -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 38127 invoked by uid 99); 15 May 2012 13:11:37 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 15 May 2012 13:11:37 +0000 X-ASF-Spam-Status: No, hits=2.2 required=5.0 tests=HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of zoltan.tothczifra@softonic.com designates 46.28.209.18 as permitted sender) Received: from [46.28.209.18] (HELO CAS02.domino.softonic.com) (46.28.209.18) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 15 May 2012 13:11:29 +0000 Received: from CAS04.domino.softonic.com (172.20.4.207) by CAS02.domino.softonic.com (192.168.248.106) with Microsoft SMTP Server (TLS) id 14.1.323.0; Tue, 15 May 2012 15:11:08 +0200 Received: from MAILSVR01.domino.softonic.com ([fe80::a987:e1ba:65d2:f66c]) by CAS04.domino.softonic.com ([::1]) with mapi id 14.01.0323.000; Tue, 15 May 2012 15:11:08 +0200 From: =?iso-8859-1?Q?Zolt=E1n_T=F3th-Czifra?= To: "user@hive.apache.org" Subject: Date format - any easier way Thread-Topic: Date format - any easier way Thread-Index: Ac0ynAQ7OVGtYRG/Sqy6nfhvB/Leiw== Date: Tue, 15 May 2012 13:11:07 +0000 Message-ID: <0005DA68C31EA7428873518FF86A943683BF24@MAILSVR01.domino.softonic.com> Accept-Language: en-US, es-ES Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [192.168.248.106] Content-Type: multipart/alternative; boundary="_000_0005DA68C31EA7428873518FF86A943683BF24MAILSVR01dominoso_" MIME-Version: 1.0 --_000_0005DA68C31EA7428873518FF86A943683BF24MAILSVR01dominoso_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi guys, Thanks you very much in advance for your help. My problem in short is getting the date for yesterday in a YYYYMMDD format.= As I use this format for partitions, I need this format in quite some quer= ies. So far I have this: concat( year( date_sub( to_date( from_unixtime( unix_timestamp() ) ), 1 ) ), CASE WHEN month( date_sub( to_date( from_unixtime( unix_timestamp() ) ), 1 ) ) <= 10 THEN concat( '0', month( date_sub( to_date( from_unixtime( unix_timestamp()= ) ), 1 ) ) ) ELSE trim( month( date_sub( to_date( from_unixtime( unix_timestamp() ) ), 1= ) ) ) END, CASE WHEN day( date_sub( to_date( from_unixtime( unix_timestamp() ) ), 1 ) ) < 1= 0 THEN concat( '0', day( date_sub( to_date( from_unixtime( unix_timestamp() )= ), 1 ) ) ) ELSE trim(day( date_sub( to_date( from_unixtime( unix_timestamp() ) ), 1 ) = ) ) END ); ...but it seems to be a bit crazy, especially if you have to repeat it in h= undreds of queries. Is there any other (better) way to get this format from= yesterday? - there has to be. As I can't use local user variables nor macr= os whatsoever, I need to repeat myself a lot here. If there is no other way= , probably I need to change my partitions. Any ideas are appreciated. Thank you! Zoltan --_000_0005DA68C31EA7428873518FF86A943683BF24MAILSVR01dominoso_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi guys,

Thanks you very much in advance for your help.

My problem in short is getting the date for yesterday in a Y= YYYMMDD format. As I use this format for partitions, I need this format in = quite some queries.

So far I have this:

concat(
year( = date_sub( to_date( from_unixtime( unix_timestamp() ) ), 1 ) ),
CASE
WHEN m= onth( date_sub( to_date( from_unixtime( unix_timestamp() ) ), 1 ) ) < 10=
THEN c= oncat( '0', month( date_sub( to_date( from_unixtime( unix_timestamp() ) ), = 1 ) ) )
ELSE t= rim( month( date_sub( to_date( from_unixtime( unix_timestamp() ) ), 1 ) ) )=
END,
CASE
WHEN d= ay( date_sub( to_date( from_unixtime( unix_timestamp() ) ), 1 ) ) < 10
THEN c= oncat( '0', day( date_sub( to_date( from_unixtime( unix_timestamp() ) ), 1 = ) ) )
ELSE t= rim(day( date_sub( to_date( from_unixtime( unix_timestamp() ) ), 1 ) ) )
END
);


...but it seems to be a bit crazy, especially if you have to= repeat it in hundreds of queries. Is there any other (better) way to get t= his format from yesterday? - there has to be. As I can't use local use= r variables nor macros whatsoever, I need to repeat myself a lot here. If there is no other way, probably I need to change my = partitions.

Any ideas are appreciated. Thank you!

Zoltan
--_000_0005DA68C31EA7428873518FF86A943683BF24MAILSVR01dominoso_--