3 ms·

> So unless you set your cluster to UTC sounds like a good idea to me

hot_gril 3y ago
Generally it is, and many cloud services do this, but the default Postgres packages for Linux/Mac/Windows do not iirc. And sometimes it's convenient to see things in your own time zone when querying manually.
marcosdumay 3y ago
This breaks about as many things as it fixes. But the things it breaks are noisy, and the things it fixes are silent. So, yeah, sounds like a good trade-off. Still, I have no idea what was going through the head of the people that standardized timestamp as local time.
- hot_gril 3y ago
  I can see why they'd want to support local time zone formatting. Even setting to UTC is merely covering up the problem that if you have a string like "2023-09-29 09:26:43.927", that does not indicate a particular point in time any better than my friend across the world proposing a Zoom call "at 8PM today."
  - masklinn 3y ago
    > Even setting to UTC is merely covering up the problem that if you have a string like "2023-09-29 09:26:43.927", that does not indicate a particular point in time any better than my friend across the world proposing a Zoom call "at 8PM." If the string in question is known to be UTC, which it should be since there is no timezone associated, then it does indicate a particular point in time.
    - hot_gril 3y ago
      UTC is a time zone too. ISO standard "2023-09-29 09:26:43.927" isn't known to be UTC; you need a "Z" at the end for that. For whatever reason, the client doesn't ask the DB for its implied time zone either. If no UTC relation information is given with a time representation, the time is assumed to be in local time. If the time is in UTC, add a Z directly after the time without a space. The only way to totally avoid the concept of time zones is to use a Unix-like timestamp, 1696004800.927.
- arp242 3y ago
  What does this break? I always want either UTC or the user's timezone (that is, the person using the interface, so that "your meeting next week at 13:00 in Europe/Amsterdam TZ" is accurate). I don't think I ever want the database's timezone. I never care about it, so just setting to UTC always seemed to work for me?
  - marcosdumay 3y ago
    For a start, your cron jobs will be scheduled at UTC, what will induce people into many mistakes.
    - arp242 3y ago
      You can set timezone = 'UTC' in your postgresql.conf, don't need to change it for the entire system. And personally I wouldn't really consider that "breaking", that seems far too strong. Although it can obviously a caveat. (Personally I do prefer to set the system time to UTC though, so it doesn't matter if the server is in Singapore, Germany, or the US – it's always the same. Also makes migrating things to different machines or moving VPSes to a different data centre a lot easier.)
      - hot_gril 3y ago
        It's good to set all servers to UTC. EC2 does this by default, so do most cloud Postgres instances.

Generally it is, and many cloud services do this, but the default Postgres packages for Linux/Mac/Windows do not iirc. And sometimes it's convenient to see things in your own time zone when querying manually.

marcosdumay 3y ago

This breaks about as many things as it fixes. But the things it breaks are noisy, and the things it fixes are silent. So, yeah, sounds like a good trade-off. Still, I have no idea what was going through the head of the people that standardized timestamp as local time.

hot_gril 3y ago
I can see why they'd want to support local time zone formatting. Even setting to UTC is merely covering up the problem that if you have a string like "2023-09-29 09:26:43.927", that does not indicate a particular point in time any better than my friend across the world proposing a Zoom call "at 8PM today."
- masklinn 3y ago
  > Even setting to UTC is merely covering up the problem that if you have a string like "2023-09-29 09:26:43.927", that does not indicate a particular point in time any better than my friend across the world proposing a Zoom call "at 8PM." If the string in question is known to be UTC, which it should be since there is no timezone associated, then it does indicate a particular point in time.
  - hot_gril 3y ago
    UTC is a time zone too. ISO standard "2023-09-29 09:26:43.927" isn't known to be UTC; you need a "Z" at the end for that. For whatever reason, the client doesn't ask the DB for its implied time zone either. If no UTC relation information is given with a time representation, the time is assumed to be in local time. If the time is in UTC, add a Z directly after the time without a space. The only way to totally avoid the concept of time zones is to use a Unix-like timestamp, 1696004800.927.
arp242 3y ago
What does this break? I always want either UTC or the user's timezone (that is, the person using the interface, so that "your meeting next week at 13:00 in Europe/Amsterdam TZ" is accurate). I don't think I ever want the database's timezone. I never care about it, so just setting to UTC always seemed to work for me?
- marcosdumay 3y ago
  For a start, your cron jobs will be scheduled at UTC, what will induce people into many mistakes.
  - arp242 3y ago
    You can set timezone = 'UTC' in your postgresql.conf, don't need to change it for the entire system. And personally I wouldn't really consider that "breaking", that seems far too strong. Although it can obviously a caveat. (Personally I do prefer to set the system time to UTC though, so it doesn't matter if the server is in Singapore, Germany, or the US – it's always the same. Also makes migrating things to different machines or moving VPSes to a different data centre a lot easier.)
    - hot_gril 3y ago
      It's good to set all servers to UTC. EC2 does this by default, so do most cloud Postgres instances.