0

Similarly as in Convert Postgres TIMESTAMP to TIMESTAMPTZ, but with the difference that I don't plan to modify existing table's timestamp columns. Are there any gotchas when using timestamptz for new tables, when all the existing ones use timestamp? Presuming that the existing clients will continue to use the DB as thus far (by always converting timestamps to UTC on their end), is there anything else one should consider?

2
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
    – Community Bot
    Commented Nov 15, 2023 at 18:46
  • There are only two sane ways to handle time zone management: 1. use timestamp with time zone everywhere in the database and have the application set timezone correctly 2. use timestamp without time zone everywhere in the database and let the application do time zone conversions. So yes, using timestamp with time zone may be a bad idea. I don't know enough about your application to say more. Commented Nov 15, 2023 at 19:26

1 Answer 1

0

timestamptz columns will be converted to the client's locale for display )and converted from it if no zone is specified on the input). so if you have several different users you will need be prepared for that.

If you existing clients are all configured to use UTC The only impact will be a '+00' at the end of the timestamptzs when they are returned as strings.

See also SQL ALTER DATABASE foo SET TIME ZONE 'UTC'; which can be used to set the default time zone for new connections from clients who don't explicitly set a time zone.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.