Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select '2024-03-19'::timestamp at time zone 'GMT';


timezone


2024-03-19 00:00:00+00



SELECT 1


--How can I get the current date in Shanghai 
--for the result '2024-03-19' if the time zone is GMT?
select '2024-03-19'::timestamp at time zone 'GMT' 
                               at time zone 'Asia/Shanghai';


timezone


2024-03-19 08:00:00



SELECT 1


select ('2024-03-19'::timestamp at time zone 'GMT' 
                                at time zone 'Asia/Shanghai')::date;


timezone


2024-03-19



SELECT 1


--For example, now in shanghai is 2024-03-19 01:29, 
--I just need to get '2024-03-19'
select '2024-03-19 01:29'::timestamp at time zone 'GMT' 
                                     at time zone 'Asia/Shanghai';


timezone


2024-03-19 09:29:00



SELECT 1


select '[2024-03-19 00:00,2024-03-20 00:00)'::tsrange;


tsrange


["2024-03-19 00:00:00","2024-03-20 00:00:00")



SELECT 1


with example(date_as_tsrange) as (values 
  ('[2024-03-19 00:00,2024-03-20 00:00)'::tsrange) )
select tsrange(lower(date_as_tsrange) at time zone 'GMT' at time zone 'Asia/Shanghai',
               upper(date_as_tsrange) at time zone 'GMT' at time zone 'Asia/Shanghai')
from example;


tsrange


["2024-03-19 08:00:00","2024-03-20 08:00:00")



SELECT 1


with example(date_as_tsrange) as (values 
  ('[2024-03-19 00:00,2024-03-20 00:00)'::tsrange) )
select tstzrange(lower(date_as_tsrange) at time zone 'GMT' at time zone 'Asia/Shanghai',
                 upper(date_as_tsrange) at time zone 'GMT' at time zone 'Asia/Shanghai')
from example;


tstzrange


["2024-03-19 08:00:00+00","2024-03-20 08:00:00+00")



SELECT 1


with 
 test(event_a,event_a_timezone, event_b,event_b_timezone) as (
  values ('2024-03-19 07:59'::timestamp,'GMT',
          '2024-03-19 06:13'::timestamp,'Asia/Shanghai')
        ,('2024-03-19 00:59'::timestamp,'GMT',
          '2024-03-19 22:13'::timestamp,'Asia/Shanghai')
        ,('2024-03-19 06:47'::timestamp,'GMT',
          '2024-03-19 10:40'::timestamp,'Asia/Shanghai') )
,test_with_dates_and_tz as (
  select *
        ,event_a at time zone event_a_timezone as event_a_tz
        ,event_b at time zone event_b_timezone as event_b_tz
        ,tstzrange( (event_a::date  )::timestamp at time zone event_a_timezone
                   ,(event_a::date+1)::timestamp at time zone event_a_timezone) 
           as event_a_date
  from test)
select event_b_tz <@ event_a_date as "did B happen on A's perceived date"
      ,*
from test_with_dates_and_tz;


did B happen on A's perceived date
event_a
event_a_timezone
event_b
event_b_timezone
event_a_tz
event_b_tz
event_a_date


f
2024-03-19 07:59:00
GMT
2024-03-19 06:13:00
Asia/Shanghai
2024-03-19 07:59:00+00
2024-03-18 22:13:00+00
["2024-03-19 00:00:00+00","2024-03-20 00:00:00+00")

t
2024-03-19 00:59:00
GMT
2024-03-19 22:13:00
Asia/Shanghai
2024-03-19 00:59:00+00
2024-03-19 14:13:00+00
["2024-03-19 00:00:00+00","2024-03-20 00:00:00+00")

t
2024-03-19 06:47:00
GMT
2024-03-19 10:40:00
Asia/Shanghai
2024-03-19 06:47:00+00
2024-03-19 02:40:00+00
["2024-03-19 00:00:00+00","2024-03-20 00:00:00+00")



SELECT 3


create table test(event_a,event_a_timezone, event_b,event_b_timezone) as 
  values ('2024-03-19 07:59'::timestamp,'GMT',
          '2024-03-19 06:13'::timestamp,'Asia/Shanghai')
        ,('2024-03-19 00:59'::timestamp,'GMT',
          '2024-03-19 22:13'::timestamp,'Asia/Shanghai')
        ,('2024-03-19 06:47'::timestamp,'GMT',
          '2024-03-19 10:40'::timestamp,'Asia/Shanghai');
table test;

SELECT 3




event_a
event_a_timezone
event_b
event_b_timezone


2024-03-19 07:59:00
GMT
2024-03-19 06:13:00
Asia/Shanghai

2024-03-19 00:59:00
GMT
2024-03-19 22:13:00
Asia/Shanghai

2024-03-19 06:47:00
GMT
2024-03-19 10:40:00
Asia/Shanghai



SELECT 3


with test_with_dates_and_tz as (
  select *,event_a at time zone event_a_timezone as event_a_tz
          ,event_b at time zone event_b_timezone as event_b_tz
          ,tstzrange( (event_a::date  )::timestamp at time zone event_a_timezone
                     ,(event_a::date+1)::timestamp at time zone event_a_timezone) 
           as event_a_date
  from test)
select event_b_tz <@ event_a_date as "did B happen on A's perceived date", *
from test_with_dates_and_tz;


did B happen on A's perceived date
event_a
event_a_timezone
event_b
event_b_timezone
event_a_tz
event_b_tz
event_a_date


f
2024-03-19 07:59:00
GMT
2024-03-19 06:13:00
Asia/Shanghai
2024-03-19 07:59:00+00
2024-03-18 22:13:00+00
["2024-03-19 00:00:00+00","2024-03-20 00:00:00+00")

t
2024-03-19 00:59:00
GMT
2024-03-19 22:13:00
Asia/Shanghai
2024-03-19 00:59:00+00
2024-03-19 14:13:00+00
["2024-03-19 00:00:00+00","2024-03-20 00:00:00+00")

t
2024-03-19 06:47:00
GMT
2024-03-19 10:40:00
Asia/Shanghai
2024-03-19 06:47:00+00
2024-03-19 02:40:00+00
["2024-03-19 00:00:00+00","2024-03-20 00:00:00+00")



SELECT 3

did B happen on A's perceived date	event_a	event_a_timezone	event_b	event_b_timezone	event_a_tz	event_b_tz	event_a_date
f	2024-03-19 07:59:00	GMT	2024-03-19 06:13:00	Asia/Shanghai	2024-03-19 07:59:00+00	2024-03-18 22:13:00+00	["2024-03-19 00:00:00+00","2024-03-20 00:00:00+00")
t	2024-03-19 00:59:00	GMT	2024-03-19 22:13:00	Asia/Shanghai	2024-03-19 00:59:00+00	2024-03-19 14:13:00+00	["2024-03-19 00:00:00+00","2024-03-20 00:00:00+00")
t	2024-03-19 06:47:00	GMT	2024-03-19 10:40:00	Asia/Shanghai	2024-03-19 06:47:00+00	2024-03-19 02:40:00+00	["2024-03-19 00:00:00+00","2024-03-20 00:00:00+00")