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?.
SHOW timezone;
SELECT (EXTRACT(TIMEZONE FROM '2022-01-01T03:25:00+03:00'::TIMESTAMPTZ) / 3600)::SMALLINT AS timezone_in_hours;
| TimeZone |
|---|
| UTC |
SHOW
| timezone_in_hours |
|---|
| 0 |
SELECT 1
SET timezone = 'Europe/Istanbul';
SELECT (EXTRACT(TIMEZONE FROM '2022-01-01T03:25:00+03:00'::TIMESTAMPTZ) / 3600)::SMALLINT AS timezone_in_hours;
SET
| timezone_in_hours |
|---|
| 3 |
SELECT 1
WITH t(id, tstz_txt) AS (
VALUES
(1, '2022-01-01T03:25:00+15:59:59')
, (2, '2022-01-01T03:25:00+11:00')
, (3, '2022-01-01T03:25:00+1:23')
, (4, '2022-01-01T03:25:00+0:00')
, (5, '2022-01-01T03:25:00-0:00')
, (6, '2022-01-01T03:25:00-1:23')
, (7, '2022-01-01T03:25:00-13:00')
, (8, '2022-01-01T03:25:00-15:59:59')
, (10, '2022-01-01T03:25:00 MEZ')
, (11, '2022-01-01T03:25:00 EST')
, (12, '2022-01-01T03:25:00 Europe/Vienna')
, (13, '2022-08-01T03:25:00 America/New_York')
)
SELECT tstz_txt
, tstz_txt::timestamptz AS tstz
, tstz_txt::timestamptz AT TIME ZONE 'UTC' AS ts_utc
, tstz_txt::timestamp AT TIME ZONE 'UTC' - tstz_txt::timestamptz AS tz_offset
, EXTRACT(hour FROM tstz_txt::timestamp AT TIME ZONE 'UTC' - tstz_txt::timestamptz)::int2 AS tz_offset_hours
FROM t
| tstz_txt | tstz | ts_utc | tz_offset | tz_offset_hours |
|---|---|---|---|---|
| 2022-01-01T03:25:00+15:59:59 | 2021-12-31 14:25:01+03 | 2021-12-31 11:25:01 | 15:59:59 | 15 |
| 2022-01-01T03:25:00+11:00 | 2021-12-31 19:25:00+03 | 2021-12-31 16:25:00 | 11:00:00 | 11 |
| 2022-01-01T03:25:00+1:23 | 2022-01-01 05:02:00+03 | 2022-01-01 02:02:00 | 01:23:00 | 1 |
| 2022-01-01T03:25:00+0:00 | 2022-01-01 06:25:00+03 | 2022-01-01 03:25:00 | 00:00:00 | 0 |
| 2022-01-01T03:25:00-0:00 | 2022-01-01 06:25:00+03 | 2022-01-01 03:25:00 | 00:00:00 | 0 |
| 2022-01-01T03:25:00-1:23 | 2022-01-01 07:48:00+03 | 2022-01-01 04:48:00 | -01:23:00 | -1 |
| 2022-01-01T03:25:00-13:00 | 2022-01-01 19:25:00+03 | 2022-01-01 16:25:00 | -13:00:00 | -13 |
| 2022-01-01T03:25:00-15:59:59 | 2022-01-01 22:24:59+03 | 2022-01-01 19:24:59 | -15:59:59 | -15 |
| 2022-01-01T03:25:00 MEZ | 2022-01-01 05:25:00+03 | 2022-01-01 02:25:00 | 01:00:00 | 1 |
| 2022-01-01T03:25:00 EST | 2022-01-01 11:25:00+03 | 2022-01-01 08:25:00 | -05:00:00 | -5 |
| 2022-01-01T03:25:00 Europe/Vienna | 2022-01-01 05:25:00+03 | 2022-01-01 02:25:00 | 01:00:00 | 1 |
| 2022-08-01T03:25:00 America/New_York | 2022-08-01 10:25:00+03 | 2022-08-01 07:25:00 | -04:00:00 | -4 |
SELECT 12