X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 121B19FA5E6 for ; Sun, 18 Jun 2006 06:51:23 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 67651-02 for ; Sun, 18 Jun 2006 06:51:14 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from wolff.to (wolff.to [66.93.197.194]) by postgresql.org (Postfix) with SMTP id A7E6D9F9AD3 for ; Sun, 18 Jun 2006 06:51:14 -0300 (ADT) Received: (qmail 31037 invoked by uid 500); 18 Jun 2006 09:54:50 -0000 Date: Sun, 18 Jun 2006 04:54:50 -0500 From: Bruno Wolff III To: Richard Broersma Jr Cc: pgsql-sql@postgresql.org Subject: Re: any additional date_time functions? Message-ID: <20060618095450.GA29167@wolff.to> Mail-Followup-To: Bruno Wolff III , Richard Broersma Jr , pgsql-sql@postgresql.org References: <20060617200820.83165.qmail@web31805.mail.mud.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20060617200820.83165.qmail@web31805.mail.mud.yahoo.com> User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/223 X-Sequence-Number: 25459 On Sat, Jun 17, 2006 at 13:08:20 -0700, Richard Broersma Jr wrote: > I am working with the date_trunc() function with great success especially in the group by clause > for aggregates. > > However, it is limited to returning "WHOLE" time units. i.e. years, months, days, hours, minutes, > seconds. > > Are there any functions similar to date_trunc that can return variable increments i.e.: > > 5, 10, or 15 minutes increments, > 3, 4, 6 hour increments, > 1, 2 weekly increments, > > I imagine that the returned values would have to either be the "floor" or "ceiling" of the actual > time stamps. You might be able to extract the time since the epoch and divide it by the appropiate number of seconds (the length of your interval) and truncate the result. This might have unexpected results for you when you span daylight savings time changes. Another option is to not use timestamp, but rather just store an integer that represents some number of your intervals offset from an epoch.