Re: Returning timestamp with timezone at specified timezone irrespective of client timezone
| От | Ron |
|---|---|
| Тема | Re: Returning timestamp with timezone at specified timezone irrespective of client timezone |
| Дата | |
| Msg-id | 6b269cb9-5572-acaa-de9c-fe1c705fed11@gmail.com обсуждение исходный текст |
| Ответ на | Returning timestamp with timezone at specified timezone irrespective of client timezone ("aNullValue (Drew Stemen)" <drew@anullvalue.net>) |
| Ответы |
Re: Returning timestamp with timezone at specified timezone irrespective of client timezone
Re: Returning timestamp with timezone at specified timezone irrespective of client timezone |
| Список | pgsql-general |
On 9/27/20 4:16 PM, aNullValue (Drew Stemen) wrote:
[snip]
Are you really asking what the TZ offset was on a specific date (Like DST or not)?
Hello,
I've attempted to obtain help with this problem from several other places, but numerous individuals recommended I ask this mailing list.
What I need is for the ability to return a timestamp with timezone, using the UTC offset that corresponds to a column-defined timezone, irrespective of the client/session configured timezone.
I have three columns in a table:
Timezone: 'US/Eastern'
Date: 2020-10-31
Time: 08:00
The output I'm able to find includes these possibilities:
'2020-10-31 08:00:00'
'2020-10-31 12:00:00+00'
Whereas what I actually need is:
'2020-10-31 08:00:00-05'
Using the postgresql session-level timezone configuration won't work because I need multiple timezones to be handled in a single set.
Example code follows. I'm not using to_char in the examples as I likely would in the production code, but I haven't found any way that it could be helpful here regardless.
id | timezone | loc_date | loc_time | tswtz | tswotz----+------------+------------+----------+------------------------+---------------------7 | US/Central | 2020-10-31 | 08:00 | 2020-10-31 13:00:00+00 | 2020-10-31 08:00:008 | US/Central | 2020-11-03 | 08:00 | 2020-11-03 14:00:00+00 | 2020-11-03 08:00:005 | US/Eastern | 2020-10-31 | 08:00 | 2020-10-31 12:00:00+00 | 2020-10-31 08:00:006 | US/Eastern | 2020-11-03 | 08:00 | 2020-11-03 13:00:00+00 | 2020-11-03 08:00:00(4 rows)What I actually need is, in example id=7, '2020-10-31 08:00:00-05'.Is this even possible? Several people have proposed that I write a custom function to do this on a per-row basis, which... I suppose I can do... I'm just blown away that this isn't something that just works "out of the box".
Are you really asking what the TZ offset was on a specific date (Like DST or not)?
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
В списке pgsql-general по дате отправления: