Re: Function to offset current timestamp

Поиск
Список
Период
Сортировка
От Dan Harris
Тема Re: Function to offset current timestamp
Дата
Msg-id 46606ACA.1090708@drivefaster.net
обсуждение исходный текст
Ответ на Re: Function to offset current timestamp  (Steve Crawford <scrawford@pinpointresearch.com>)
Список pgsql-admin
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

В списке pgsql-admin по дате отправления:

Предыдущее
От: Steve Crawford
Дата:
Сообщение: Re: Function to offset current timestamp
Следующее
От: "Simon Riggs"
Дата:
Сообщение: Re: Attempt to re-archive existing WAL logs afterrestoringfrom backup