Postgres 16

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;
create table daily (id int,pub_date timestamptz);
insert into daily values
  (1,'2023-11-05 01:00'), --only this one's desired
  (2,'2023-11-04 23:00'),
  (3,'2023-11-04 15:00'),
  (4,'2023-11-03 23:00');
create table posts (id int,pub_date timestamptz);
insert into posts values
  (1,'2023-11-05 01:00'), --only this one's desired
  (2,'2023-11-04 23:00'),
  (3,'2023-11-04 15:00'),
  (4,'2023-11-03 23:00');

TimeZone

UTC

SHOW

CREATE TABLE

INSERT 0 4

CREATE TABLE

INSERT 0 4

--int UTC, everything looks normal
select current_setting('timezone') as client_timezone,* 
from daily 
where pub_date::date=(select max(pub_date)::date from posts);

client_timezone
id
pub_date

UTC
1
2023-11-05 01:00:00+00

SELECT 1

--assuming a client with mismatched timezone setting
set timezone='Japan';

SET

--because Japan is 9h ahead of UTC, 
--the timestamps 2 and 3 also correspond to 2023-11-05 
select current_setting('timezone') as client_timezone,* 
from daily 
where pub_date::date=(select max(pub_date)::date from posts);

client_timezone
id
pub_date

Japan
1
2023-11-05 10:00:00+09

Japan
2
2023-11-05 08:00:00+09

Japan
3
2023-11-05 00:00:00+09

SELECT 3

--explicitly stating that those time zones are supposed to match
--but only as observed in UTC, fixes that
select current_setting('timezone') as client_timezone,* 
from daily 
where             (pub_date at time zone 'utc')::date
     = (select max(pub_date at time zone 'utc')::date from posts);

client_timezone
id
pub_date

Japan
1
2023-11-05 10:00:00+09

SELECT 1

--the output date can also be shifted to "as observed in UTC"
select id,pub_date at time zone 'utc'
from daily 
where             (pub_date at time zone 'utc')::date
     = (select max(pub_date at time zone 'utc')::date from posts);

id
timezone

1
2023-11-05 01:00:00

SELECT 1

--it's easiest to align client timezone with the db
set timezone='utc';

select current_setting('timezone') as client_timezone,* 
from daily 
where             (pub_date at time zone 'utc')::date
     = (select max(pub_date at time zone 'utc')::date from posts);

SET

client_timezone
id
pub_date

UTC
1
2023-11-05 01:00:00+00

SELECT 1

client_timezone	id	pub_date
Japan	1	2023-11-05 10:00:00+09
Japan	2	2023-11-05 08:00:00+09
Japan	3	2023-11-05 00:00:00+09