How to retain the time zone info when truncating a timestamp with time zone

Question

TL;DR:

How to select '1999-12-31 20:59:59+00:00'::timestamptz as truncated date-time information in a different time zone (without minutes and seconds, for instance), showing the time zone used, such as 2000-01-01 07:00:00 +10:30

---

In my multi-time-zone application with timestamps stored in UTC, I want to count the number of rows grouped by minute, hour, day, etc., in a local timezone supplied as part of the SQL query (the local time zone is a subject of user settings, not server configuration)

I'm trying to use date_trunc on a timestamptz, but the time zone information is not shown in the end product.

The reason is that I then take the resulting date and pass it to PHP's DateTime object. In that process, if the time zone is not part of the result string, the information is lost.

Here is what I tried so far, with a couple of test in UTC in an attempt to verify I'm working with the right data on input:

CREATE TEMPORARY TABLE tstest (
    ts timestamptz not null
);

INSERT INTO tstest VALUES ('1999-12-31 20:59:59'::timestamptz at time zone 'UTC');

select 1 AS row, ts::text, 'Control: Original value. Correct in local time zone, showing TZ' AS observation from tstest union
select 2, (ts AT TIME ZONE 'UTC')::text, 'Control: Correct in UTC, but no TZ' from tstest union
select 3, (ts::timestamptz AT TIME ZONE 'Australia/Adelaide')::text, 'Test case: Correct in Adelaide, **but no TZ information**' from tstest union
select 4, date_trunc('hour', ts::timestamptz)::text, 'Truncated Control: Correct in local time zone, showing TZ' from tstest UNION
select 5, date_trunc('hour', ts::timestamptz AT TIME ZONE 'UTC')::text, 'Truncated Control: Correct in UTC, but no TZ' from tstest UNION
select 6, date_trunc('hour', ts::timestamptz AT TIME ZONE 'Australia/Adelaide')::text, 'Truncated Test case: Correct time in Adelaide, **but no TZ information**' from tstest UNION
select 7, to_char(date_trunc('hour', ts::timestamptz AT TIME ZONE 'Australia/Adelaide'), 'YYYY-mm-dd HH:MI:ss OF')::text, 'Truncated test case: Correct time in Adelaide, **but the TZ shown is incorrect**' from tstest UNION
select 8, concat(to_char(date_trunc('hour', ts::timestamptz AT TIME ZONE 'Australia/Adelaide'), 'YYYY-mm-dd HH:MI:ss')::text, ' Australia/Adelaide'), 'Desired result, **but concatenated manually**.' from tstest
;

resulting in:

row	ts	observation
1	1999-12-31 20:59:59+00	Control: Original value. Correct in local time zone, showing TZ
2	1999-12-31 20:59:59	Control: Correct in UTC, but no TZ
3	2000-01-01 07:29:59	Test case: Correct in Adelaide, **but no TZ information**
4	1999-12-31 20:00:00+00	Truncated Control: Correct in local time zone, showing TZ
5	1999-12-31 20:00:00	Truncated Control: Correct in UTC, but no TZ
6	2000-01-01 07:00:00	Truncated Test case: Correct time in Adelaide, **but no TZ information**
7	2000-01-01 07:00:00 +00	Truncated test case: Correct time in Adelaide, **but the TZ shown is incorrect**
8	2000-01-01 07:00:00 Australia/Adelaide	Desired result, **but concatenated manually**.

Rows 3 and 6 and 7 are the time I'm after but lack the (correct) timezone. Row 8 is what I'm ultimately after (I think the TZ name is better than offset because of DST), but I find it difficult to accept I need to append the TZ information myself after casting a TZ-aware timestamp to a string.

What am I missing?

Here is how those dates translate into PHP objects (= why I need the timezone be part of the string output in the SELECT result): https://3v4l.org/u7A8q#v8.2.10

<?php

// @see https://stackoverflow.com/questions/77131068/how-to-retain-the-time-zone-info-when-truncating-a-timestamp-with-time-zone

date_default_timezone_set('UTC');

$dates = [
    '1999-12-31 20:59:59+00',
    '1999-12-31 20:59:59',
    '2000-01-01 07:29:59',
    '1999-12-31 20:00:00+00',
    '1999-12-31 20:00:00',
    '2000-01-01 07:00:00',
    '2000-01-01 07:00:00 +00',
    '2000-01-01 07:00:00 Australia/Adelaide'
];

foreach ($dates as $dateString) {
    $date = new \DateTimeImmutable($dateString);
    echo $dateString."\n-> ".$date->format('r')."\n\n";
}

producing:

1999-12-31 20:59:59+00
-> Fri, 31 Dec 1999 20:59:59 +0000

1999-12-31 20:59:59
-> Fri, 31 Dec 1999 20:59:59 +0000

2000-01-01 07:29:59
-> Sat, 01 Jan 2000 07:29:59 +0000

1999-12-31 20:00:00+00
-> Fri, 31 Dec 1999 20:00:00 +0000

1999-12-31 20:00:00
-> Fri, 31 Dec 1999 20:00:00 +0000

2000-01-01 07:00:00
-> Sat, 01 Jan 2000 07:00:00 +0000

2000-01-01 07:00:00 +00
-> Sat, 01 Jan 2000 07:00:00 +0000

2000-01-01 07:00:00 Australia/Adelaide
-> Sat, 01 Jan 2000 07:00:00 +1030

I have seen similar questions asked around (timezone aware date_trunc function, date_trunc at time zone with original timestamptz, date_trunc at time zone with original timestamptz), but none were zooming on to what I think is my problem: the fact the timezone information is removed from the result when using date_trunc or ::timestamptz

Answer 1

Your options are either to put the session time zone to the time zone you want to use for the session, or barring that to manually concatenate the strings. You can write a function to do the latter if you want to do it without visible repetition.

(the local time zone is a subject of user settings, not server configuration)

That is pretty much already how PostgreSQL works. The server configuration only controls what the default session time zone is if the session neglects to declare one for itself. So make the 1st or 2nd command of every session set the users preferred time zone (Looking it up in the database, if that is where the preference is stored)