Re: Inconsistency of timezones in postgresql

>
> And why does the "timestamp" datatype selected "with time zone
>> Australia/Sydney" throw a "+00" on the output? I don't understand why that
>> scenario in particular gets any +- on the output, but if anything it should
>> be "+10" right?
>>
>
> As you noted subsequently as being UTC, the offset shown reflects your
> time zone setting. A lack of it on the UTC expression is contradictory to
> my test.
>

[resend for list]

I don't know what that means.... offsets on dates are supposed to always be
relative to UTC aren't they? Local times are always +0 to themselves,
that's a tautology.

ibis=# set time zone 'UTC';
SET
ibis=# select update_date from collection_item where item_id=2197;
update_date
---------------------
2012-07-06 12:59:55
(1 row)

ibis=# select update_date at time zone 'UTC' from collection_item where
item_id=2197;
timezone
------------------------
2012-07-06 12:59:55+00
(1 row)

ibis=# select update_date at time zone 'Australia/Sydney' from
collection_item where item_id=2197;
timezone
------------------------
2012-07-06 02:59:55+00

Firstly, isn't the conversion wrong? Sydney times are later than UTC times.
When it's midday UTC, it's 10pm Sydney. So how is it that in the above
date, UTC is 12:59pm and Sydney is 2:59am on the same day? Surely, surely
that can't be right can it?

The Australia/Sydney time is shown +00 even though I did set time zone
'UTC'. So what is the +00 relative to?
Doesn't the SET TIME ZONE 'UTC' make my zone as UTC? BTW, I'm not myself in
Australia/Sydney, I'm not in +10, I'm in +8, so it can't even be picking
that up from my local machine in some obscure way. And the server is
running in UTC.

ibis=# SELECT current_setting('TIMEZONE');
current_setting
UTC
ibis=# select now() at time zone 'UTC';
timezone
2024-08-02 03:53:38.852841
As I write, that is the correct UTC time.

And whatever it is doing, where is that documented?

--
Chris

On Thu, 1 Aug 2024 at 13:06, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Wednesday, July 31, 2024, Chris BSomething <xpusostomos(at)gmail(dot)com>
> wrote:
>
>>
>> So... the wiki (not the documentation mind you) says "don't use
>> timestamp" because it’s there for compatibility with other databases?
>>
>
> The docs focus on what is and try to generally withhold judgement. This
> particular area is also quite challenging to address and the motivation to
> want to make the effort drops significantly when the tone and quality of
> the instigating bug report is so unfavorable.
>
>
>> and when I use "AT TIME ZONE", it assumes the timestamp is UTC, and makes
>> its calculations accordingly.
>>
>
> The system rarely assumes things about user data. In this case the
> session time zone setting and the various ways to specify time zones exist
> because we choose not to assume. The application may store whatever time
> literal it wants in a timestamp field and is promised to get that exact
> same value out upon retrieval no matter their time zone setting. That’s
> all it is documented to do as a data type. Everything else is functions.
>
> Timestamptz is documented to perform rotations according to the time zone
> setting upon producing text output. And it tells you what zone it ended up
> in.
>
>
> I do an ALTER COLUMN type timestamptz, and it adds a seemingly random 8
>> hours to the values... that's not a bug?
>>
>
> As noted on the other reply the bug seems to be in your test setup, not
> the system.
>
> David J.
>
>