Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id D9DAD9FBA95 for ; Fri, 1 Jun 2007 14:47:12 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 13803-10 for ; Fri, 1 Jun 2007 14:47:01 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from crestone.coronasolutions.com (crestone.coronasolutions.com [66.45.104.24]) by postgresql.org (Postfix) with ESMTP id 65EE79FBC66 for ; Fri, 1 Jun 2007 14:47:02 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by crestone.coronasolutions.com (Postfix) with ESMTP id 8F568644751; Fri, 1 Jun 2007 11:47:01 -0600 (MDT) Received: from crestone.coronasolutions.com ([127.0.0.1]) by localhost (crestone.coronasolutions.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 21944-06; Fri, 1 Jun 2007 11:46:58 -0600 (MDT) Received: from mac.local (c-71-196-213-0.hsd1.co.comcast.net [71.196.213.0]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by crestone.coronasolutions.com (Postfix) with ESMTP id C4F68644723 for ; Fri, 1 Jun 2007 11:46:58 -0600 (MDT) Message-ID: <46605B92.1020302@drivefaster.net> Date: Fri, 01 Jun 2007 11:46:58 -0600 From: Dan Harris User-Agent: Thunderbird 2.0.0.0 (Macintosh/20070326) MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: Function to offset current timestamp References: <46605886.1070404@drivefaster.net> In-Reply-To: <46605886.1070404@drivefaster.net> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at coronasolutions.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/5 X-Sequence-Number: 25673 Dan Harris wrote: > I'm trying to write a function that can do a select on an integer value > from a table and subtract that value from current_timestamp. > > Let's say I have a table called users and a field called tz_offset.. > > I want my function to do something like : > > select current_timestamp - '( select tz_offset from users where userid = > ? ) hours'::interval > > Obviously this syntax is incorrect, but I'm not quite sure how to do the > subquery and make the integer result of it part of the quoted interval > expression. > > Any tips? > > Thx > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org ok, sorry to reply to my own message, but I figured out a way: select current_timestamp - ( select tz_offset from users where userid = ? ) * '1 hour'::interval;