Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 43F239FC5A2 for ; Fri, 1 Jun 2007 15:52:09 -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 13291-04-2 for ; Fri, 1 Jun 2007 15:51:58 -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 67DE69FC587 for ; Fri, 1 Jun 2007 15:51:58 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by crestone.coronasolutions.com (Postfix) with ESMTP id D57A3644751; Fri, 1 Jun 2007 12:51:56 -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 22773-05; Fri, 1 Jun 2007 12:51:55 -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 01949644723 for ; Fri, 1 Jun 2007 12:51:54 -0600 (MDT) Message-ID: <46606ACA.1090708@drivefaster.net> Date: Fri, 01 Jun 2007 12:51:54 -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> <4660646D.3070208@pinpointresearch.com> In-Reply-To: <4660646D.3070208@pinpointresearch.com> 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/7 X-Sequence-Number: 25675 Steve Crawford wrote: > 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? > > > You don't need to make it part of the quoted expression - just multiply > an interval by your integer. Using your version, try something like: > > select current_timestamp - '1 hour'::interval * ( select tz_offset from > users where userid = ? ) > > But this is actually wrong because the returned time is still in your > current timezone - just offset by X hours. Thanks for the extra explanation here. I think I'll be OK because the timezone offset comes directly from the browser. I grab it via javascript and as long as their system timezone offset is set correctly, the dates will be displayed relative to them. I do understand that I will have to do more work if I want to display their dates to other users in different timezones correctly. -Dan