This is not correct. Timestamptz does not do a transformation when used in expressions requiring timestamptz (most of them) which timestamp does.
The two types are different and incompatible. Timestamptz refers to a fixed moment in time regardless of your local Timezone. Timestamp refers to the time on the clock wherever you are in the world. A timestamp of “7pm” means “7pm” wherever your server - or client, depending on how it gets executed - happens to be.
It’s really really confusing because select now(), which returns Timestamptz, internally uses UTC time, but prints the time in your local time zone eg ‘10:16 Aug 9 2021+10’ in my case.
Select now()::timestamp prints the same time, but without the timestamp, leading you to think that the former stores the +10, but it doesn’t.
In fact the +10 is telling you that PG knows that the time is UTC and your local TZ is UTC+10. The latter, without the offset, is telling you that PG doesn’t know what time zone the time is in and so can’t convert it to your local time zone.
It is super confusing but the best possible advice is never use a plain timestamp without time zone because it leads to a bunch of subtle errors that will drive you nuts.
Put another way: if you are used to using Unix Epoch Time, then the equivalent data type in PG is timestamptz.
I agree with you, but what I meant by both doing different transformations is that:
If the client sends 2021-08-08T07:00:00+03 to the server, in either case the offset of +03 is going to be lost. As you know, timestamp will keep the clock time and timestampz will keep the "moment in time" (under the hood by converting to UTC.) I'd argue those are just two different rules for "dropping the offset".
Oh ok yep sure, you're right. If you want to keep the timestamp then you need to keep it in both cases.
But with timestamptz you don't need to use the timezone in most calculations while with timestamp you do need to use the timezone otherwise unexpected behaviour can creep in.
But yes I do agree that you're technically correct, and that's the best kind of correct after all :)
The two types are different and incompatible. Timestamptz refers to a fixed moment in time regardless of your local Timezone. Timestamp refers to the time on the clock wherever you are in the world. A timestamp of “7pm” means “7pm” wherever your server - or client, depending on how it gets executed - happens to be.
It’s really really confusing because select now(), which returns Timestamptz, internally uses UTC time, but prints the time in your local time zone eg ‘10:16 Aug 9 2021+10’ in my case.
Select now()::timestamp prints the same time, but without the timestamp, leading you to think that the former stores the +10, but it doesn’t.
In fact the +10 is telling you that PG knows that the time is UTC and your local TZ is UTC+10. The latter, without the offset, is telling you that PG doesn’t know what time zone the time is in and so can’t convert it to your local time zone.
It is super confusing but the best possible advice is never use a plain timestamp without time zone because it leads to a bunch of subtle errors that will drive you nuts.
Put another way: if you are used to using Unix Epoch Time, then the equivalent data type in PG is timestamptz.