Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id A71052E3610 for ; Wed, 12 Dec 2007 02:31:59 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 67517-03 for ; Wed, 12 Dec 2007 02:31:45 -0400 (AST) Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.171]) by postgresql.org (Postfix) with ESMTP id CDBD12E2F4C for ; Wed, 12 Dec 2007 02:31:46 -0400 (AST) Received: from webserv.wug.de ([194.25.154.138]) by mrelayeu.kundenserver.de (node=mrelayeu3) with ESMTP (Nemesis) id 0MKxQS-1J2L8C318a-0004mH; Wed, 12 Dec 2007 07:31:45 +0100 Received: from kretschmer by webserv.wug.de with local (Exim 3.36 #1) id 1J2L8A-00074B-00 for pgsql-sql@postgresql.org; Wed, 12 Dec 2007 07:31:42 +0100 Date: Wed, 12 Dec 2007 07:31:42 +0100 From: "A. Kretschmer" To: pgsql-sql@postgresql.org Subject: Re: Query design assistance - getting daily totals Message-ID: <20071212063142.GA26523@a-kretschmer.de> References: <475F3AAB.2050609@reynolds.com.au> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <475F3AAB.2050609@reynolds.com.au> X-OS: Debian/GNU Linux - weil ich es mir Wert bin! User-Agent: Mutt/1.5.9i X-Provags-ID: V01U2FsdGVkX1+Hohxj8faSn6w1K4id+AkrTM3deK0CHyBE2r8 vR/cNGnQoOn53+o09kE0BhLfrrqElYvGp6p5bKKuwz/LTlm3Bh ttyzSW3Tdd/A8yMnM5z4Ju8rkwxJz2q X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/65 X-Sequence-Number: 29757 am Wed, dem 12.12.2007, um 10:34:35 +0900 mailte Paul Lambert folgendes: > I have a table of account balances as at the end of a working day and > want to from that, calculate daily total figures. > > Eg, let's say I have a table structure of: > year_id integer > month_id integer > working_day integer Why this broken data types? We have date and timestamp[tz]. > I suspect the second option would be more efficient than the first, and > probably easier to implement since it would be easier to handle > cross-month boundaries, i.e. day 1's daily total will be the amount on > that day minus the amount of the final day in the previous month - but > does anyone have any alternate suggestions that would be better still? Yes, i would also write a similar function. And if you have proper datatypes it would be simpler to calculate the previous date and you can use a proper index on the date column. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net