By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select version();
| version |
|---|
| PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit |
SELECT 1
set session timezone to UTC;
SET
select '2017-05-01T16:16:35-05:00' ::timestamptz;
| timestamptz |
|---|
| 2017-05-01 21:16:35+00 |
SELECT 1
create table test_timestamp(
org text,
tsz timestamptz,
ts timestamp,
offsettz interval
);
CREATE TABLE
create or replace function supporttsz()
returns trigger as $$
begin
new.tsz := (new.org)::timestamptz at time zone 'utc';
new.ts := (new.org)::timestamptz at time zone 'utc';
new.ts := new.ts :: timestamp;
if
SUBSTRING((right(trim(new.org),5)), 1,1) = '-' then
new.offsettz :=(right(trim(new.org),5))::interval;
elsif
SUBSTRING((right(trim(new.org),5)), 1,1) = '+' then
new.offsettz := (right(trim(new.org),5))::interval;
elsif
SUBSTRING((right(trim(new.org),6)), 1,1) = '-' then
new.offsettz := (right(trim(new.org),6))::interval;
elsif
SUBSTRING((right(trim(new.org),6)), 1,1) = '+' then
new.offsettz :=(right(trim(new.org),6))::interval;
else new.offsettz := '0::00' ::interval;
end if;
return new;
end;
$$ language plpgsql;
CREATE FUNCTION
create trigger tg_supporttsz_test_timestamp
before insert on test_timestamp
for each row execute procedure supporttsz();
CREATE TRIGGER
insert into test_timestamp(org) values ('1999-01-08 04:05:06-8:00');
insert into test_timestamp(org) values ('2017-05-01T16:16:35-05:00');
INSERT 0 1
INSERT 0 1
table test_timestamp;
| org | tsz | ts | offsettz |
|---|---|---|---|
| 1999-01-08 04:05:06-8:00 | 1999-01-08 12:05:06+00 | 1999-01-08 12:05:06 | -08:00:00 |
| 2017-05-01T16:16:35-05:00 | 2017-05-01 21:16:35+00 | 2017-05-01 21:16:35 | -05:00:00 |
SELECT 2
set session timezone to Egypt;
SET
show timezone;
| TimeZone |
|---|
| Egypt |
SHOW
insert into test_timestamp(org) values ('2017-05-11T16:16:35-05:00');
INSERT 0 1
table test_timestamp;
| org | tsz | ts | offsettz |
|---|---|---|---|
| 1999-01-08 04:05:06-8:00 | 1999-01-08 14:05:06+02 | 1999-01-08 12:05:06 | -08:00:00 |
| 2017-05-01T16:16:35-05:00 | 2017-05-01 23:16:35+02 | 2017-05-01 21:16:35 | -05:00:00 |
| 2017-05-11T16:16:35-05:00 | 2017-05-11 21:16:35+02 | 2017-05-11 21:16:35 | -05:00:00 |
SELECT 3