Returning timestamp with timezone at specified timezone irrespective of client timezone

On 9/27/20 2: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.

---------------------------------------------------

SET TIME ZONE 'UTC';

CREATE TABLE loc
(
    id serial not null,
timezone text not null,
    loc_date date NOT NULL,
    loc_time text NOT NULL,
    CONSTRAINT loc_pkey PRIMARY KEY (id),
    CONSTRAINT loc_loc_time_check CHECK (loc_time ~
'(^(2[0-3]|[01][0-9]|[0-9]):?([0-5][0-9]):?([0-5][0-9])?$)|(^(1[012]|0[1-9]|[1-9]):[0-5][0-9]
[AaPp][Mm]$)'::text)
)
;

INSERT INTO loc (timezone, loc_date, loc_time) VALUES
('US/Eastern', '2020-10-31', '08:00'),
('US/Eastern', '2020-11-03', '08:00'),
('US/Central', '2020-10-31', '08:00'),
('US/Central', '2020-11-03', '08:00');

SELECT *
, timezone(l.timezone, l.loc_date + l.loc_time::time without time zone)
tswtz
, (l.loc_date + l.loc_time::time without time zone) tswotz
FROM loc l
ORDER BY timezone, loc_date, loc_time
;

---------------------------------------------------

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".

Something like?:

select '2020-10-31' || ' 08:00 ' || utc_offset from pg_timezone_names
where name = 'US/Eastern';
?column?
----------------------------
2020-10-31 08:00 -04:00:00

--
Adrian Klaver
adrian.klaver@aklaver.com

At 2020-09-27T18:31:49-04:00, Adrian Klaver <adrian.klaver@aklaver.com> sent:

On 9/27/20 2: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.

---------------------------------------------------

SET TIME ZONE 'UTC';

CREATE TABLE loc
(
    id serial not null,
timezone text not null,
    loc_date date NOT NULL,
    loc_time text NOT NULL,
    CONSTRAINT loc_pkey PRIMARY KEY (id),
    CONSTRAINT loc_loc_time_check CHECK (loc_time ~
'(^(2[0-3]|[01][0-9]|[0-9]):?([0-5][0-9]):?([0-5][0-9])?$)|(^(1[012]|0[1-9]|[1-9]):[0-5][0-9]
[AaPp][Mm]$)'::text)
)
;

INSERT INTO loc (timezone, loc_date, loc_time) VALUES
('US/Eastern', '2020-10-31', '08:00'),
('US/Eastern', '2020-11-03', '08:00'),
('US/Central', '2020-10-31', '08:00'),
('US/Central', '2020-11-03', '08:00');

SELECT *
, timezone(l.timezone, l.loc_date + l.loc_time::time without time zone)
tswtz
, (l.loc_date + l.loc_time::time without time zone) tswotz
FROM loc l
ORDER BY timezone, loc_date, loc_time
;

---------------------------------------------------

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".

Something like?:

select '2020-10-31' || ' 08:00 ' || utc_offset from pg_timezone_names
where name = 'US/Eastern';
?column?
----------------------------
2020-10-31 08:00 -04:00:00

--
Adrian Klaver
adrian.klaver@aklaver.com

The problem there is that the value of utc_offset in pg_timezone_names is correct only as of the current point in time, and not as of the date/time values in the row.

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.

"aNullValue (Drew Stemen)" <drew@anullvalue.net> writes:

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 might be confused, but I think that the way to get the timestamptz
values you want is

# SELECT *
, ((l.loc_date || ' ' || l.loc_time)::timestamp) at time zone timezone tswtz
FROM loc l
ORDER BY timezone, loc_date, loc_time
;
id | timezone | loc_date | loc_time | tswtz
----+------------+------------+----------+------------------------
3 | US/Central | 2020-10-31 | 08:00 | 2020-10-31 13:00:00+00
4 | US/Central | 2020-11-03 | 08:00 | 2020-11-03 14:00:00+00
1 | US/Eastern | 2020-10-31 | 08:00 | 2020-10-31 12:00:00+00
2 | US/Eastern | 2020-11-03 | 08:00 | 2020-11-03 13:00:00+00
(4 rows)

These are the correct timestamptz values, as displayed with
the session timezone set to UTC as per your example. If what
you're asking for is that the *presentation* vary per the timezone
column, then you have to fake it, because timestamptz_out simply
will not do that for you. However, it's not very clear to me
why you don't just concatenate the loc_date, loc_time, and timezone
columns if that's the presentation you want.

Alternatively, if this was just a dummy example and you really
mean you've done a timestamptz calculation and now want to present
it in a varying timezone, you could do something like this,
using now() as a placeholder for some timestamptz expression:

# select timezone, now(), (now() at time zone timezone) || ' ' || timezone tswtz from loc l;
timezone | now | tswtz
------------+-------------------------------+---------------------------------------
US/Eastern | 2020-09-27 23:32:19.321202+00 | 2020-09-27 19:32:19.321202 US/Eastern
US/Eastern | 2020-09-27 23:32:19.321202+00 | 2020-09-27 19:32:19.321202 US/Eastern
US/Central | 2020-09-27 23:32:19.321202+00 | 2020-09-27 18:32:19.321202 US/Central
US/Central | 2020-09-27 23:32:19.321202+00 | 2020-09-27 18:32:19.321202 US/Central
(4 rows)

The key thing to understand here is that AT TIME ZONE either
rotates from local time to UTC, or vice versa, depending on
whether its input is timestamp or timestamptz.

regards, tom lane

Is it really a requirement to hold the datetime in the database actually in the specified time zone ? Usual practice is to hold UTC only and convert when necessary to user-configured (or specified) or column-specified time zone perhaps only when transferring to/from the db or when otherwise necessary. Any time zones that have daylight savings will also have a problem when calculating datetime differences when crossing the daylight savings boundary. UTC doesn’t have this problem.

Can you refactor to only store UTC and the desired time zone, then convert to that time zone when needed?

Also, what programming language outside of SQL are you using (if any)?

Greg S.

At 2020-09-27T19:13:09-04:00, Ron <ronljohnsonjr@gmail.com> sent:

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.

What it was at a specific date/time combination, yes. PostgreSQL already has that knowledge because of its ability to accurately calculate the value above in column "tswtz". It just doesn't seem to be able to output that easily.

Though I just did realize that (obviously) I can do math to arrive at the answer I need. I'm perplexed by my not having realized that long before I considered creating this thread. -_-

This is ugly and ineligant, but it seems to work:

CASE WHEN ((timezone('UTC',(l.loc_date + l.loc_time::time without time zone)) - timezone(l.timezone, l.loc_date + l.loc_time::time without time zone))::interval) > '-00:00:01' THEN
to_char((l.loc_date + l.loc_time::time without time zone),'YYYY-MM-DD HH24:MI:SS') ||'+'|| to_char((timezone('UTC',(l.loc_date + l.loc_time::time without time zone)) - timezone(l.timezone, l.loc_date + l.loc_time::time without time zone))::interval, 'HH24:MI:SS')
ELSE
to_char((l.loc_date + l.loc_time::time without time zone),'YYYY-MM-DD HH24:MI:SS') || to_char((timezone('UTC',(l.loc_date + l.loc_time::time without time zone)) - timezone(l.timezone, l.loc_date + l.loc_time::time without time zone))::interval, 'HH24:MI:SS')
END AS correct_format

On 9/27/20 16:13, Ron wrote:

On 9/27/20 4:16 PM, aNullValue (Drew Stemen) wrote:

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.

Are you really asking what the TZ offset was on a specific date (Like
DST or not)?

IIUC, there is a gap here in PostgreSQL. i think it could most
easily/quickly be addressed with an overloaded version of to_char that
accepts a "display timezone" for its timestamp to character conversion.

FWIW - in Oracle this is handled by having two different data types:
1) TIMESTAMP WITH TIME ZONE
2) TIMESTAMP WITH LOCAL TIME ZONE

ironically, oracle's "local" data type is the equivalent PostgreSQL's
timestamp with time zone where the timestamp is converted and
processed/stored without a time zone. afaik postgresql doesn't have a
native data type equivalent to the first variant in oracle, which
actually considers the time zone as part of the data. (am i missing
something?)

in lieu of having built-in support, a PL/pgSQL function to set the
session-level timezone in between processing each record is the best
approach i've thought of so far.

-Jeremy

--
http://about.me/jeremy_schneider

At 2020-09-27T19:36:34-04:00, Tom Lane <tgl@sss.pgh.pa.us> sent:

"aNullValue (Drew Stemen)" <drew@anullvalue.net> writes:

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 might be confused, but I think that the way to get the timestamptz
values you want is

# SELECT *
, ((l.loc_date || ' ' || l.loc_time)::timestamp) at time zone timezone tswtz
FROM loc l
ORDER BY timezone, loc_date, loc_time
;
id | timezone | loc_date | loc_time | tswtz
----+------------+------------+----------+------------------------
3 | US/Central | 2020-10-31 | 08:00 | 2020-10-31 13:00:00+00
4 | US/Central | 2020-11-03 | 08:00 | 2020-11-03 14:00:00+00
1 | US/Eastern | 2020-10-31 | 08:00 | 2020-10-31 12:00:00+00
2 | US/Eastern | 2020-11-03 | 08:00 | 2020-11-03 13:00:00+00
(4 rows)

These are the correct timestamptz values, as displayed with
the session timezone set to UTC as per your example. If what
you're asking for is that the *presentation* vary per the timezone
column, then you have to fake it, because timestamptz_out simply
will not do that for you. However, it's not very clear to me
why you don't just concatenate the loc_date, loc_time, and timezone
columns if that's the presentation you want.

Alternatively, if this was just a dummy example and you really
mean you've done a timestamptz calculation and now want to present
it in a varying timezone, you could do something like this,
using now() as a placeholder for some timestamptz expression:

# select timezone, now(), (now() at time zone timezone) || ' ' ||
timezone tswtz from loc l;
timezone | now | tswtz

------------+-------------------------------+---------------------------------------
US/Eastern | 2020-09-27 23:32:19.321202+00 | 2020-09-27
19:32:19.321202 US/Eastern
US/Eastern | 2020-09-27 23:32:19.321202+00 | 2020-09-27
19:32:19.321202 US/Eastern
US/Central | 2020-09-27 23:32:19.321202+00 | 2020-09-27
18:32:19.321202 US/Central
US/Central | 2020-09-27 23:32:19.321202+00 | 2020-09-27
18:32:19.321202 US/Central
(4 rows)

The key thing to understand here is that AT TIME ZONE either
rotates from local time to UTC, or vice versa, depending on
whether its input is timestamp or timestamptz.

regards, tom lane

I should have been much more clear about this ultimately being converted to text for output. I didn't simply because the default-rendering of the timestamptz column provided format identical to the character format I'll ultimately be using. Apologies for my lack of clarity.

I cannot simply append because then I'd wind up with your example, where 'US/Eastern' is appended to the computed string; I need that to be the actual UTC offset ('-04:00:00' or '-05:00:00', rather than the string 'US/Eastern'). The desired result is '2020-11-03 18:12:34-05:00:00', where '-05:00:00' is the effective UTC offset in the timezone specified by its name in another column.

Yes, unfortunately there's no easy way for me to convert the time to UTC for storage in pg, though I think that's more or less immaterial to the output problem I'm having.

History: the table I'm working on holds the effective open/close hours of ballot drop-box open-for-service hours, and the timezone is not always known at the time the open/close time (in local time) is captured. There will be some ballot drop-boxes for which this will never able to output the timestamp being discussed here, because the jurisdiction hasn't bothered to specify the UTC offset for their drop boxes.

But yes, I understand your points, and in most cases I agree entirely.

There are multiple programming languages being used by multiple teams; I'm personally only working on and knowledgeable regarding the database.

Thanks for your help,

Drew

At 2020-09-27T19:37:39-04:00, Greg Smith <ecomputerd@yahoo.com> sent:

If the time zone is not always known, then maybe the time zone field is NULL in that case? Would it be possible to use UTC for datetimes that have a known time zone (and thus specify UTC in the time zone field)? And NULL otherwise? Or is this a case where the datetime comes in without time zone and you have the column there (perhaps configured from elsewhere) to indicate the tz for this inserted time-zone-unaware value? Or maybe it’s the case that you can set the time zone for datetime values you’ve already inserted that, when inserted, didn’t specify a time zone? Sounds like problems any time you need to display a datetime for which you don’t have a time zone. That would be a special case in processing or display. Ugh.

I hope my comments aren’t distracting. I’m just throwing out ideas that might be worth considering.

Greg S.

On 9/27/20 16:42, Jeremy Schneider wrote:

On 9/27/20 16:13, Ron wrote:

On 9/27/20 4:16 PM, aNullValue (Drew Stemen) wrote:

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.

Are you really asking what the TZ offset was on a specific date (Like
DST or not)?

in lieu of having built-in support, a PL/pgSQL function to set the
session-level timezone in between processing each record is the best
approach i've thought of so far

FYI, here's the hack approach I was thinking of.

I intentionally didn't preserve the session's timezone in the
transaction, but that could easily be done with a few more lines of
PL/pgSQL.

create or replace function to_char(
v_tstz timestamp with time zone
,v_format text
,v_tz text
) returns text language plpgsql
immutable parallel safe
as $$
begin
perform set_config('timezone',v_tz,true);
return to_char(v_tstz,v_format);
end;
$$
;

SELECT
id
,to_char(l.loc_date+l.loc_time::time
,'YYYY-MM-DD HH24:MI:SSOF'
,timezone) tsw
FROM loc l
ORDER BY timezone, loc_date, loc_time
;

id | tsw
----+------------------------
3 | 2020-10-31 03:00:00-05
4 | 2020-11-03 08:00:00-06
1 | 2020-10-31 09:00:00-04
2 | 2020-11-03 08:00:00-05

https://gist.github.com/aNullValue/ba838d6b40495695df0daa11c2748248

--
http://about.me/jeremy_schneider

On 9/27/20 20:13, Jeremy Schneider wrote:

create or replace function to_char(
v_tstz timestamp with time zone
,v_format text
,v_tz text
) returns text language plpgsql
immutable parallel safe
as $$
begin
perform set_config('timezone',v_tz,true);
return to_char(v_tstz,v_format);
end;
$$
;

Just occurred to me, I don't know if this is actually parallel safe. I'm
not sure how transaction-level session configuration is handled inside
parallel workers. Might be best to leave off the "parallel safe" flag
from the function for now.

-J

--
http://about.me/jeremy_schneider

On Sun, 2020-09-27 at 17:16 -0400, aNullValue (Drew Stemen) wrote:

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.

Try a function like this:

CREATE FUNCTION format_timestamp(
ts timestamp with time zone,
time_zone text
) RETURNS text
LANGUAGE plpgsql IMMUTABLE STRICT AS
$$DECLARE
tz text;
result text;
BEGIN
tz := current_setting('timezone');
PERFORM set_config('timezone', time_zone, TRUE);
result := ts AT TIME ZONE 'UTC' AT TIME ZONE 'UTC';
PERFORM set_config('timezone', tz, TRUE);
RETURN result;
END;$$;

SELECT format_timestamp(current_timestamp, '+08');

format_timestamp
-------------------------------
2020-09-28 17:15:25.083677+08
(1 row)

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com