Re: Function to offset current timestamp

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