Re: pg_upgrade Python version issue on openSUSE
Hi Adrian, > On 27. Sep, 2020, at 00:09, Adrian Klaver wrote: > Could it be that at some point in these instances history plpython* where > installed as CREATE LANGUAGE and you are dealing with the vestiges of that? I do know for sure that that never happened because the database clusters with this effect are my personal test databases and I never used Python. I did some tests with plperl and plperlu, though, but never python, because I don't "speak" python. What is possible is, those databases are clones of a Patroni cluster database (primary) I used to experiment with. I just copied them to new PGDATAs back then and changed PGPORT of course. I know, I could have done initdb and pg_dumpall but just copying the whole database cluster was the fast way to go, even more so as the PostgreSQL software was exactly the same. Just PGDATA and PGPORT changed for the clone. From what I know this is a perfectly legal way to do it as long as the source database cluster is properly shut down during the copy process. Maybe Patroni did it then implicitly? I'm not sure how Patroni works internally but I know that it is written in Python. Maybe it does install something in the database which I don't know and can't find? I tried searching for anything owned by "replicator" but can't find anything. > Are you able to go back and reconstruct them and then do \dL (languages) and > \dx (extensions)? The machine in question is my personal test box at home. I don't do regular backups there. If I break something I just reinstall it. So going back into the past with backups is not possible for me. The only thing that I kept running a long time now is the Patroni cluster because I have some data stored in it. But this is the only "history" there is. However, \dx and \dL do not show any Python extension or language on the Patroni cluster too, which is still 12.4. Still, thanks for helping. Cheers, Paul
Re: pg_upgrade Python version issue on openSUSE
On 9/26/20 8:07 AM, Adrian Klaver wrote: On 9/26/20 7:49 AM, Tom Lane wrote: =?utf-8?Q?Paul_F=C3=B6rster?= writes: On 26. Sep, 2020, at 16:07, Tom Lane wrote: However, I don't understand how "drop extension plpythonu" worked for you, given your previous query showing that that extension wasn't installed. just checked with another 12.4. It's the same: postgres=# select * from pg_available_extension_versions where installed; name | version | installed | superuser | relocatable | schema | requires | comment -+-+---+---+-++--+-- plperlu | 1.0 | t | t | f | pg_catalog | | PL/PerlU untrusted procedural language dblink | 1.2 | t | t | t | | | connect to other PostgreSQL databases from within a database plpgsql | 1.0 | t | f | f | pg_catalog | | PL/pgSQL procedural language plperl | 1.0 | t | f | f | pg_catalog | | PL/Perl procedural language (4 rows) postgres=# drop extension plpythonu ; DROP EXTENSION postgres=# create extension plpython3u ; CREATE EXTENSION Actually, now that I think about it, you're querying the wrong view. I'm too lazy to check the source code right now, but I'm pretty sure that pg_available_extension_versions is mostly driven off what control files exist in the on-disk libdir. But that may have little to do with what's in the system catalogs. You should have checked pg_extension, or just "\dx" in psql. I believe the issue is here: select * from pg_pltemplate ; plpythonu | f | f | plpython_call_handler | plpython_inline_handler | plpython_validator | $libdir/plpython2 | NULL plpython2u | f | f | plpython2_call_handler | plpython2_inline_handler | plpython2_validator | $libdir/plpython2 | NULL plpython3u | f | f | plpython3_call_handler | plpython3_inline_handler | plpython3_validator | $libdir/plpython3 | NULL Some digging in the pg_upgrade code(function.c) proved the above wrong. Turns out pg_upgrade uses information from pg_proc. The default plpython is plpythonu and that points at $libdir/plpython2. The instructions here: https://www.postgresql.org/docs/12/plpython-python23.html offer a work around: "Daredevils, who want to build a Python-3-only operating system environment, can change the contents of pg_pltemplate to make plpythonu be equivalent to plpython3u, keeping in mind that this would make their installation incompatible with most of the rest of the world." regards, tom lane -- Adrian Klaver adrian.kla...@aklaver.com
Re: pg_upgrade Python version issue on openSUSE
On 9/27/20 2:00 AM, Paul Förster wrote: Hi Adrian, On 27. Sep, 2020, at 00:09, Adrian Klaver wrote: Could it be that at some point in these instances history plpython* where installed as CREATE LANGUAGE and you are dealing with the vestiges of that? I do know for sure that that never happened because the database clusters with this effect are my personal test databases and I never used Python. I did some tests with plperl and plperlu, though, but never python, because I don't "speak" python. What is possible is, those databases are clones of a Patroni cluster database (primary) I used to experiment with. I just copied them to new PGDATAs back then and changed PGPORT of course. I know, I could have done initdb and pg_dumpall but just copying the whole database cluster was the fast way to go, even more so as the PostgreSQL software was exactly the same. Just PGDATA and PGPORT changed for the clone. From what I know this is a perfectly legal way to do it as long as the source database cluster is properly shut down during the copy process. Maybe Patroni did it then implicitly? I'm not sure how Patroni works internally but I know that it is written in Python. Maybe it does install something in the database which I don't know and can't find? I tried searching for anything owned by "replicator" but can't find anything. Are you able to go back and reconstruct them and then do \dL (languages) and \dx (extensions)? The machine in question is my personal test box at home. I don't do regular backups there. If I break something I just reinstall it. So going back into the past with backups is not possible for me. The only thing that I kept running a long time now is the Patroni cluster because I have some data stored in it. But this is the only "history" there is. However, \dx and \dL do not show any Python extension or language on the Patroni cluster too, which is still 12.4. Does: SELECT lanname, proname, probin FROM pg_proc JOIN pg_language ON pg_language.oid = pg_proc.prolang WHERE pg_language.lanname='plpythonu' AND probin IS NOT NULL; show anything? This would need to be repeated for each cluster in database. Still, thanks for helping. Cheers, Paul -- Adrian Klaver adrian.kla...@aklaver.com
Re: pg_upgrade Python version issue on openSUSE
On 9/27/20 10:30 AM, Adrian Klaver wrote: On 9/27/20 2:00 AM, Paul Förster wrote: Hi Adrian, On 27. Sep, 2020, at 00:09, Adrian Klaver wrote: Could it be that at some point in these instances history plpython* where installed as CREATE LANGUAGE and you are dealing with the vestiges of that? I do know for sure that that never happened because the database clusters with this effect are my personal test databases and I never used Python. I did some tests with plperl and plperlu, though, but never python, because I don't "speak" python. What is possible is, those databases are clones of a Patroni cluster database (primary) I used to experiment with. I just copied them to new PGDATAs back then and changed PGPORT of course. I know, I could have done initdb and pg_dumpall but just copying the whole database cluster was the fast way to go, even more so as the PostgreSQL software was exactly the same. Just PGDATA and PGPORT changed for the clone. From what I know this is a perfectly legal way to do it as long as the source database cluster is properly shut down during the copy process. Maybe Patroni did it then implicitly? I'm not sure how Patroni works internally but I know that it is written in Python. Maybe it does install something in the database which I don't know and can't find? I tried searching for anything owned by "replicator" but can't find anything. Are you able to go back and reconstruct them and then do \dL (languages) and \dx (extensions)? The machine in question is my personal test box at home. I don't do regular backups there. If I break something I just reinstall it. So going back into the past with backups is not possible for me. The only thing that I kept running a long time now is the Patroni cluster because I have some data stored in it. But this is the only "history" there is. However, \dx and \dL do not show any Python extension or language on the Patroni cluster too, which is still 12.4. Does: SELECT lanname, proname, probin FROM pg_proc JOIN pg_language ON pg_language.oid = pg_proc.prolang WHERE pg_language.lanname='plpythonu' AND probin IS NOT NULL; show anything? This would need to be repeated for each cluster in database. Really? Make that for each database in cluster! Still, thanks for helping. Cheers, Paul -- Adrian Klaver adrian.kla...@aklaver.com
help flattening json
posgresql verion: 12
i can accomplish this procedurally fairly easily but would like to do this
strictly in SQL.
jsondb=# select jsonb_typeof(jsonb_path_query(vdl_json,'$.tables[*]')) from
vdl_json2;
jsonb_typeof
--
object
object
object
object
object
object
(6 rows)
jsondb=# select jsonb_pretty(jsonb_path_query(vdl_json,'$.tables[1]')) from
vdl_json2;
jsonb_pretty
{ +
"name": "AMP_DATA", +
"quoted": true, +
"columns": [ +
{ +
"field": "WELL", +
"selected": true, +
"displayName": "Well" +
},+
{ +
"field": "WELL_POSITION", +
"selected": true, +
"displayName": "Well Position"+
},+
{ +
"field": "CYCLE_NUMBER", +
"selected": true, +
"displayName": "Cycle Number" +
},+
{ +
"field": "TARGET",+
"selected": true, +
"displayName": "Target" +
},+
{ +
"field": "RN",+
"selected": true, +
"displayName": "Rn" +
},+
{ +
"field": "DRN", +
"selected": true, +
"displayName": "dRn" +
},+
{ +
"field": "SAMPLE",+
"selected": true, +
"displayName": "Sample" +
},+
{ +
"field": "OMIT", +
"selected": true, +
"displayName": "Omit" +
} +
],+
"labeled": false, +
"options": { +
},+
"displayName": "Amplification Data", +
"sortedColumns": [+
],+
"analysisModule": "primary" +
}
(1 row)
i would like to end up with (name text, field text[]).
I can get the field array when i manually filter on name:
jsondb=# select jsonb_path_query_array(vdl_json,'$.tables ? (@.name ==
"RAW_DATA").columns.field') fields
from vdl_json2;
fields
---
["WELL", "WELL_POSITION", "CYCLE_NUMBER"]
(1 row)
I can get the text of names:
jsondb=# \e
name
--
"RESULT"
"AMP_DATA"
"MULTICOMPONENT"
"RAW_DATA"
"REPLICATE_GROUP_RESULT"
"WELL_CALL"
(6 rows)
I think i should be able to do this with a lateral join but i can't seem to
get it right. something like:
jsondb=# select tnames.tname, tfields_arr.* from
(select jsonb_path_query(vdl_json,'$.tables[*].name') as tname
from vdl_json2) tnames,
lateral
(select jsonb_path_query_array(vdl_json,'$.tables ? (@.name ==
tnames.tname).columns.field') as tfields
from vdl_json2) tfields_arr;
ERROR: syntax error at or near " "
LINE 6: from vdl_json2) tfields_arr;
Any json folks out there willing to help out?
i'd also like to get records of "(name text, field text, selected text,
displayName text)" but i haven't started on that one yet.
any help is greatly appreciated!
Returning timestamp with timezone at specified timezone irrespective of client timezone
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".
Re: 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.kla...@aklaver.com
Re: Returning timestamp with timezone at specified timezone irrespective of client timezone
At 2020-09-27T18:31:49-04:00, Adrian Klaver 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.kla...@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.
Re: Returning timestamp with timezone at specified timezone irrespective of client timezone
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.
Re: Returning timestamp with timezone at specified timezone irrespective of client timezone
"aNullValue (Drew Stemen)" 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
Re: Returning timestamp with timezone at specified timezone irrespective of client timezone
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.
> On Sep 27, 2020, at 5:39 PM, aNullValue (Drew Stemen)
> wrote:
>
> At 2020-09-27T18:31:49-04:00, Adrian Klaver 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.kla...@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.
>
>
Re: Returning timestamp with timezone at specified timezone irrespective of client timezone
At 2020-09-27T19:13:09-04:00, Ron 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),'-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),'-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
Re: Returning timestamp with timezone at specified timezone irrespective of client timezone
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
Re: Returning timestamp with timezone at specified timezone irrespective of client timezone
At 2020-09-27T19:36:34-04:00, Tom Lane sent:
> "aNullValue (Drew Stemen)" 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.
Re: Returning timestamp with timezone at specified timezone irrespective of client timezone
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 sent:
> 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.
>
> > On Sep 27, 2020, at 5:39 PM, aNullValue (Drew Stemen)
> > wrote:
> >
> > At 2020-09-27T18:31:49-04:00, Adrian Klaver
> > 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
Re: Returning timestamp with timezone at specified timezone irrespective of client timezone
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 Sep 27, 2020, at 6:51 PM, aNullValue (Drew Stemen)
> wrote:
>
> 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 sent:
>> 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.
>>
>>> On Sep 27, 2020, at 5:39 PM, aNullValue (Drew Stemen)
>>> wrote:
>>>
>>> At 2020-09-27T18:31:49-04:00, Adrian Klaver
>>> 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
Re: Returning timestamp with timezone at specified timezone irrespective of client timezone
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
,'-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
Re: Returning timestamp with timezone at specified timezone irrespective of client timezone
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
