X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id C1E01D1B556 for ; Mon, 20 Oct 2003 08:28:36 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 60281-09 for ; Mon, 20 Oct 2003 05:28:07 -0300 (ADT) Received: from anchor-post-31.mail.demon.net (anchor-post-31.mail.demon.net [194.217.242.89]) by svr1.postgresql.org (Postfix) with ESMTP id 5EBF6D1B54E for ; Mon, 20 Oct 2003 05:27:49 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-31.mail.demon.net with esmtp (Exim 3.35 #1) id 1ABVOB-000Br1-0V; Mon, 20 Oct 2003 09:27:43 +0100 Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id 7ECF917DA1; Mon, 20 Oct 2003 09:27:34 +0100 (BST) Received: from client17.archonet.com (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id C9A3416334; Mon, 20 Oct 2003 09:27:33 +0100 (BST) From: Richard Huxton To: email lists , pgsql-sql@postgresql.org Subject: Re: date_trunc for 5 minutes intervals Date: Mon, 20 Oct 2003 09:27:32 +0100 User-Agent: KMail/1.5 References: <035C9F7CE28601428BBB5B051C9F77F20766F4@orion.mackay.local> In-Reply-To: <035C9F7CE28601428BBB5B051C9F77F20766F4@orion.mackay.local> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200310200927.32941.dev@archonet.com> X-Virus-Scanned: by AMaViS snapshot-20020531 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/225 X-Sequence-Number: 15625 On Sunday 19 October 2003 14:38, email lists wrote: > Hi All, > > I am wanting to perform the equivalent of date_trunc to 5/10/15 minute > intervals. As this does mnot seem to be natively supported by > date_trunc, can anyone point me in the right direction to possible write > the SQL ro acheive the desired outcome? I'd probably convert to seconds-since-epoch and then subtract that value modulo 300 and convert back. Might be worth checking the cookbook link on http://techdocs.postgresql.org/ since I can't believe you are the first person to need this. -- Richard Huxton Archonet Ltd