X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 65FEC9FA5EA for ; Mon, 11 Sep 2006 11:08:39 -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 81375-01 for ; Mon, 11 Sep 2006 11:08:29 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 898509FB228 for ; Mon, 11 Sep 2006 11:08:29 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k8BE8OjO022489; Mon, 11 Sep 2006 10:08:24 -0400 (EDT) To: "Aaron Bono" cc: "Mike C" , pgsql-admin@postgresql.org Subject: Re: SELECT and DATE Function question In-reply-to: References: Comments: In-reply-to "Aaron Bono" message dated "Mon, 11 Sep 2006 00:27:46 -0500" Date: Mon, 11 Sep 2006 10:08:24 -0400 Message-ID: <22488.1157983704@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.11 tagged_above=0 required=5 tests=AWL, SPF_HELO_PASS, SPF_PASS X-Spam-Level: X-Archive-Number: 200609/90 X-Sequence-Number: 23004 "Aaron Bono" writes: > Use: > select start_date + (cast(number_of_days as text) || ' days')::interval from > blah This is a pretty awful way to do it, much better is select start_date + number_of_days * '1 day'::interval ... which reduces to basically one multiplication instead of conversion to text, text string append, parse interval string value (relying on a couple of undocumented cast abilities). But the real question here is whether you actually want sub-day precision in your result. Should the column have been 'date' rather than 'timestamp' to begin with? If not, what behavior are you expecting at DST boundaries? regards, tom lane