3 ms·

> where timestamptz is counterintuitively almost always what you want. Funny I think the exact opposite. timestamptz would be useful if it stored a timezone, s

> where timestamptz is counterintuitively almost always what you want. Funny I think the exact opposite. timestamptz would be useful if it stored a timezone, since it does not, it's useless, the application is a much better location to perform timezone handling (or store the timezone in a nearby column, if applicable, possibly even define a custom domain to bundle both). > So unless you set your cluster to UTC or maybe the "C" locale (which I think converts everything to UTC but I forget), any backend reading from your DB will get timestamps off by several hours. AKA they'll get the actual timestamp. A timestamp is either UTC, or it's zoned and associated with a timezone. Adjusting randomly based on the locale machine's configuration or a piece of connection state is definitely a thing I have no interest in. Locale string configurations have a similar issue of being half-assed and broken. They barely even worked before the internet existed, as soon as more than two different cultures interact with the software they might as well be chucked away for all the good they do (which is close to none). And that's before finding out all the harm they do.

hot_gril 3y ago
> AKA they'll get the actual timestamp. No, they'll be 7 hours ahead. In my example, the actual data stored in the table is approx 1696004800, which it translated to 2023-09-29 09:26:40.277-07 for timestamptz output, and for timestamp it outputted 2023-09-29 09:26:40.277 which the client would incorrectly assume is 2023-09-29 09:26:40.277Z, or 1696030000. [Edited with Unix timestamps to be clearer.] > Adjusting randomly based on the locale machine's configuration or a piece of connection state is definitely a thing I have no interest in. Right, and timestamp is the one that causes this random adjustment. timestamptz avoids this by being explicit about the time zone.
- masklinn 3y ago
  > Right, and timestamp is the one that causes this random adjustment. No, you having decided to leverage timezones is what causes it. This is a case of violence begetting more violence.
  - hot_gril 3y ago
    I'm not sure what you mean by leveraging timezones. The SET TIMEZONE line was to simulate a fresh Postgres setup on my machine, which defaults to US/Pacific for me. If my cluster is hosted elsewhere, idk what it defaults to. Postgres's built-in now() returns a timestamptz, and if I were instead inserting values from – say – JS w/ node-pg and using `new Date()`, I'd get the same behavior. I'm not going out of my way here. If you're suggesting using timestamp rather than timestamptz, how do you do that without changing or assuming the DB's locale settings?

hot_gril 3y ago

> AKA they'll get the actual timestamp. No, they'll be 7 hours ahead. In my example, the actual data stored in the table is approx 1696004800, which it translated to 2023-09-29 09:26:40.277-07 for timestamptz output, and for timestamp it outputted 2023-09-29 09:26:40.277 which the client would incorrectly assume is 2023-09-29 09:26:40.277Z, or 1696030000. [Edited with Unix timestamps to be clearer.] > Adjusting randomly based on the locale machine's configuration or a piece of connection state is definitely a thing I have no interest in. Right, and timestamp is the one that causes this random adjustment. timestamptz avoids this by being explicit about the time zone.

masklinn 3y ago
> Right, and timestamp is the one that causes this random adjustment. No, you having decided to leverage timezones is what causes it. This is a case of violence begetting more violence.
- hot_gril 3y ago
  I'm not sure what you mean by leveraging timezones. The SET TIMEZONE line was to simulate a fresh Postgres setup on my machine, which defaults to US/Pacific for me. If my cluster is hosted elsewhere, idk what it defaults to. Postgres's built-in now() returns a timestamptz, and if I were instead inserting values from – say – JS w/ node-pg and using `new Date()`, I'd get the same behavior. I'm not going out of my way here. If you're suggesting using timestamp rather than timestamptz, how do you do that without changing or assuming the DB's locale settings?