Re: Inconsistency of timezones in postgresql - Mailing list pgsql-bugs
| From | Chris BSomething |
|---|---|
| Subject | Re: Inconsistency of timezones in postgresql |
| Date | |
| Msg-id | CADrHaBFp7_y+_P0kE5n-gkP1t4V0oQ9XZuD60pJXDXoMvQ8n6Q@mail.gmail.com Whole thread Raw |
| In response to | Re: Inconsistency of timezones in postgresql ("David G. Johnston" <david.g.johnston@gmail.com>) |
| List | pgsql-bugs |
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
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.
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.g.johnston@gmail.com> wrote:
On Wednesday, July 31, 2024, Chris BSomething <xpusostomos@gmail.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.
pgsql-bugs by date: