UTC Settings for API and Database

[Patowhiz]

Overview
As we strive for uniformity and precision in handling date and time operations across our application, it's crucial we align on the standards and configurations that best suit our development and operational practices, especially regarding the use of Coordinated Universal Time (UTC). This prompts me to raise a discussion to evaluate the necessity of explicitly configuring UTC Settings for Our API and Database.

Here's a brief overview of our current setup and some inherent behaviors:

• Date and Time Operations: Our expectation is that all date and time CRUD operations, including all requests to the API, operate in UTC to maintain consistency and accuracy.

• PostgreSQL's Handling of Timestamps: By default, PostgreSQL stores timestamp with time zone values in UTC. Additionally, PostgreSQL sessions default to UTC when the session time zone isn't explicitly specified. Note also, PostgreSQL session time zone determines whether date time conversion during retrieval and saving(when time zone is part of the date time value) .

• Node.js and TypeORM Behavior: In the context of Node.js, the default time zone setting is considered to be UTC. Correspondingly, TypeORM, through the pg driver, sends date-time values as UTC times, derived from JavaScript Date objects. It's important to note that JavaScript Date objects are inherently time zone agnostic*, representing specific points in time without direct association to any time zones.

• Current Time Zone Configuration: In our API's database connection settings, we haven't altered the default time zone configuration. This implicitly means we operate PostgreSQL under UTC settings. Similarly, we haven't modified the Node.js time zone setting from its default.

Given this context, I'd like us to consider and discuss the necessity of explicitly configuring UTC settings within our API and database connections. Some points to ponder:

• Considering the default behaviors mentioned, is explicitly setting everything to UTC redundant or a necessary step for clarity and consistency across different environments?

• Could there be scenarios or edge cases where not explicitly setting UTC might lead to discrepancies or bugs, particularly as our application scales or in distributed environments?

• Is there additional maintenance overhead associated with explicitly configuring these settings, and does it outweigh the benefits of ensuring consistency and clarity in our date and time handling practices?

I believe having a clear and consistent approach to managing time zones, especially committing to UTC, could help mitigate potential issues down the line. However, I'm eager to hear your thoughts, insights, and any experiences you might have had related to this topic.