3 ms·

In my opinion that is not how you should be timestamps localized to a time zone in Postgres. You should instead use: SELECT now()::timestampz at time zone 'U

In my opinion that is not how you should be timestamps localized to a time zone in Postgres. You should instead use: SELECT now()::timestampz at time zone 'US/Pacific' The output is a timestamp that has no time zone offsets and therefore is localized. However I've noticed most novice programmers don't fully understand the implications of 'at time zone ...' syntax. It makes sense to me that the actual time zone is not stored within the timestamp itself since tomorrow a time zone can introduce DST and therefore some timestamps that were valid today will no longer be valid tomorrow.

hot_gril 3y ago
In a backend running this SQL, you'd have to hardcode or track which timezone to localize to ("at time zone"), which isn't desirable.
masklinn 3y ago
> It makes sense to me that the actual time zone is not stored within the timestamp itself since tomorrow a time zone can introduce DST and therefore some timestamps that were valid today will no longer be valid tomorrow. It's the exact inverse. If you want to store a future moment in time then you must store the local time with its timezone, so that you can adjust everything relative to it. Otherwise you lose critical information and any adjustment you do will be the wrong way around. Once the instant has passed it doesn't matter, because all the offsets are fixed. > tomorrow a time zone can introduce DST and therefore some timestamps that were valid today will no longer be valid tomorrow. That happens either way. But the one timestamp which remains valid throughout should be the one for the reference timezone. Whether that timezone is the one which introduces DST or not, the rest is what should shift relative to it. For instance let's say that last week a buddy told you to meet at 4, you store this information normalised without timezone. In the meantime the legislature declared summer time. Now when you ask for the meeting time your program tells you it's 5 local. Which it is not.

hot_gril 3y ago

In a backend running this SQL, you'd have to hardcode or track which timezone to localize to ("at time zone"), which isn't desirable.

masklinn 3y ago

> It makes sense to me that the actual time zone is not stored within the timestamp itself since tomorrow a time zone can introduce DST and therefore some timestamps that were valid today will no longer be valid tomorrow. It's the exact inverse. If you want to store a future moment in time then you must store the local time with its timezone, so that you can adjust everything relative to it. Otherwise you lose critical information and any adjustment you do will be the wrong way around. Once the instant has passed it doesn't matter, because all the offsets are fixed. > tomorrow a time zone can introduce DST and therefore some timestamps that were valid today will no longer be valid tomorrow. That happens either way. But the one timestamp which remains valid throughout should be the one for the reference timezone. Whether that timezone is the one which introduces DST or not, the rest is what should shift relative to it. For instance let's say that last week a buddy told you to meet at 4, you store this information normalised without timezone. In the meantime the legislature declared summer time. Now when you ask for the meeting time your program tells you it's 5 local. Which it is not.