Re: Inconsistency of timezones in postgresql - Mailing list pgsql-bugs
| From | Chris BSomething |
|---|---|
| Subject | Re: Inconsistency of timezones in postgresql |
| Date | |
| Msg-id | CADrHaBGysCz0UnQDDueeA51BNhO=3Dr+BRYDefRTDU-vTAeymw@mail.gmail.com Whole thread Raw |
| In response to | Re: Inconsistency of timezones in postgresql (Chris BSomething <xpusostomos@gmail.com>) |
| Responses |
Re: Inconsistency of timezones in postgresql
Re: Inconsistency of timezones in postgresql Re: Inconsistency of timezones in postgresql |
| List | pgsql-bugs |
Riddle me this... I have a field of type timestamp. I do 3 queries on a particular record one is AT TIME ZONE 'UTC', ones is AT TIME ZONE Australia/Sydney, one is plain, without a clause and I get:
change_time timestamp not null default current_timestamp,
UTC: 2024-01-01 10:42:13
Australia/Sydney: 2024-01-01 00:42:13:+00
plain: 2024-01-01 10:42:13
Now I alter table and I alter column change_time type timestamp with time zone, so I add the "with time zone clause", now its type is "timestamptz", both timestamp and "with time zone" are both stored in UTC right? So nothing important should change by altering the column should it? But now I get:
UTC: 2024-01-01 02:42:13+00
Australia/Sydney: 2024-01-01 12:42:13
plain: 2024-01-01 02:42:13
So all the timestamps are now different. How can that be?
And to make matters worse, as I write it is 2024-08-01 01:26 UTC ... or in other words BOTH scenarios return a date in the future, even though the field is populated with a default clause of "current_timestamp". select now() at time zone UTC returns the correct thing.
Is it wrong to assign current_timestamp to a timestamp field because current_timestamp is "with time zone"? If so, that's amazing since I thought under the hood its all UTC. I can't see that mentioned in the documentation that using current_timestamp can be so dangerously wrong when assigned to a timestamp.
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?
And why did the numbers shift 8 hours later when I changed the data type of the table? I happen to be 8 hours east of GMT... but I didn't create this record, if anything it was created by a program and person 10 hours east. If I do "set time zone 'UTC'" in psql, it changes nothing, so it doesn't seem to be anything in the psql client that causes the offset.
On Thu, 1 Aug 2024 at 03:03, Chris BSomething <xpusostomos@gmail.com> wrote:
Well... I guess then at least we have...AT TIME ZONE INTERVAL '8 hours'or indeed...AT TIME ZONE INTERVAL '+8h'so at a bare minimum we need documentation that promotes that, and warns about UTC+-I still think Z+- would be a few lines of code that would be a cool fix that wouldn't hurt anyone, but anyway.ChrisOn Thu, 1 Aug 2024 at 01:42, David G. Johnston <david.g.johnston@gmail.com> wrote:On Wednesday, July 31, 2024, Chris BSomething <xpusostomos@gmail.com> wrote:Tom Lane said:"However, notice that the value following TIME ZONE is only allowed tobe an interval by the spec (and this is still true in SQL:2021,
the latest version I have handy). Such an interval is interpreted per
ISO (positive = east of Greenwich)."Erm, what do you mean by an interval? If you mean a number, then it’s broken, because "UTC+10" and "+10" do the same thing. But you seem to be saying there is indeed some syntax that is interpreted by ISO logic?There is a named data type called “interval”. He’s referring to that. Neither of those text values is an interval. ‘4 hours 30 minutes’::interval is a relevant example.David J.
pgsql-bugs by date: