From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Returning timestamp with timezone at specified timezone irrespective of client timezone |
Date: | 2020-09-27 23:13:09 |
Message-ID: | 6b269cb9-5572-acaa-de9c-fe1c705fed11@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 9/27/20 4:16 PM, aNullValue (Drew Stemen) wrote:
> 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.
>
[snip]
>
> 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:00
> 8 | US/Central | 2020-11-03 | 08:00 | 2020-11-03 14:00:00+00 |
> 2020-11-03 08:00:00
> 5 | US/Eastern | 2020-10-31 | 08:00 | 2020-10-31 12:00:00+00 |
> 2020-10-31 08:00:00
> 6 | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2020-09-27 23:36:34 | Re: Returning timestamp with timezone at specified timezone irrespective of client timezone |
Previous Message | aNullValue (Drew Stemen) | 2020-09-27 22:39:41 | Re: Returning timestamp with timezone at specified timezone irrespective of client timezone |