Postgres 13

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

CREATE TABLE tbl (ts timestamp without time zone);
INSERT INTO tbl VALUES ('2021-06-24 11:00:00');

CREATE TABLE

INSERT 0 1

-- plain display for timestamp
TABLE tbl;

ts

2021-06-24 11:00:00

SELECT 1

-- timestamptz is displayed for crrent TimeZone setting
SHOW timezone;
SELECT now()

TimeZone

GB

SHOW

now

2023-05-09 07:52:52.048983+01

SELECT 1

-- plain cast assumes the current timezone for the value
SELECT ts::timestamptz FROM tbl;

ts

2021-06-24 11:00:00+01

SELECT 1

-- different result for different TimeZone setting
SET timezone = 'America/New_York';
SELECT ts::timestamptz FROM tbl;
SET timezone = 'UTC';
SELECT ts::timestamptz FROM tbl;

SET

ts

2021-06-24 11:00:00-04

SELECT 1

SET

ts

2021-06-24 11:00:00+00

SELECT 1

-- Use the desired timezone for the conversion
ALTER TABLE tbl ALTER COLUMN ts type timestamptz USING ts AT TIME ZONE 'UTC';

ALTER TABLE

TABLE tbl;

ts

2021-06-24 11:00:00+00

SELECT 1

RESET timezone; -- back to original setting 'GB'; same value, different display
TABLE tbl;

RESET

ts

2021-06-24 12:00:00+01

SELECT 1