The Recurring Timestamp Anomaly in NocoBase
For a segment of the NocoBase user base, particularly in its Chinese-speaking community, a peculiar bug has been a persistent source of frustration: timestamps appearing consistently off by 8 hours, or dates showing up a day earlier. This pattern, where the discrepancy matches the user's UTC offset (e.g., 8 hours for UTC+8, 9 hours for UTC+9), strongly suggests a systematic issue rather than random data corruption. It points to a specific mechanism at play within the application's handling of time data.
To uncover the root cause, a rigorous testing setup was implemented. NocoBase versions 2.0.51 and 2.1.23, utilizing official Docker images, were deployed against both PostgreSQL 16 and MySQL 8.4. The critical aspect of this investigation was the method of data interaction: all data was written to and read from the databases exclusively through the REST API. The server's timezone was precisely controlled using the TZ environment variable within the Docker container. This controlled environment allowed for a focused examination of how NocoBase, its underlying databases, and server timezones interact, specifically excluding browser-side rendering quirks.
Database Behavior: PostgreSQL vs. MySQL
The investigation revealed distinct behaviors between PostgreSQL and MySQL when handling timestamps via NocoBase's REST API, particularly concerning timezone information.
PostgreSQL: A Consistent UTC Approach
When NocoBase was configured with PostgreSQL, the results were notably consistent and aligned with PostgreSQL's standard handling of timestamp data. PostgreSQL, by default, stores timestamp data in UTC and then converts it to the client's or server's timezone upon retrieval. In this test setup, with the TZ variable set (e.g., to Asia/Shanghai, which is UTC+8), NocoBase would send a timestamp to PostgreSQL. PostgreSQL would dutifully store this timestamp internally as UTC.
The critical observation occurred during data retrieval. When NocoBase queried the data back through the API, PostgreSQL would return the stored UTC timestamp. However, the problem arose in how NocoBase subsequently interpreted this UTC value. Instead of applying the server's configured timezone offset (e.g., +8 hours) to the UTC value to display it correctly for the local user, NocoBase appeared to be treating the UTC timestamp as if it were already a local time. This misinterpretation led to the observed 8-hour deficit. If the server's `TZ` was set to UTC+9, the shift was 9 hours, confirming the direct correlation with the server's configured offset.
MySQL: Ambiguity and Default Timezones
MySQL's behavior presented a more complex picture, influenced by its configuration and the specific timestamp data type used. In the test environment, using MySQL 8.4, the results were less uniform than with PostgreSQL.
When NocoBase sent timestamps to MySQL, the storage behavior depended on whether the `use_timestamp` setting in NocoBase's database configuration was enabled or disabled. If `use_timestamp` was enabled, NocoBase would attempt to store values as MySQL's `TIMESTAMP` type. MySQL's `TIMESTAMP` type is inherently timezone-aware; it stores values in UTC internally and converts them to the server's current timezone upon retrieval. This behavior is similar to PostgreSQL in principle, but the implementation details and potential for misconfiguration can differ.
The primary issue with MySQL seemed to stem from how NocoBase interacted with the `TIMESTAMP` type when the server's `TZ` variable was set. If NocoBase did not correctly account for the server's timezone during insertion or, more likely, during retrieval and reinterpretation, the same misinterpretation observed with PostgreSQL could occur. The data stored might be correct UTC, but NocoBase's application logic, when reading it back in a specific server timezone context, could fail to apply the offset correctly, leading to the perceived time shift.
Conversely, if `use_timestamp` was disabled in NocoBase's configuration, the data would be stored using MySQL's `DATETIME` type. The `DATETIME` type in MySQL is not timezone-aware; it stores values exactly as provided, with no internal conversion to UTC. This means if NocoBase sent a local time value (e.g., 2023-10-27 10:00:00 in UTC+8) and it was stored as `DATETIME`, it would be retrieved as exactly that, regardless of the server's `TZ` setting. In this scenario, the timestamp shift would not occur due to database storage, but any application logic expecting timezone awareness would still be problematic.
The Root Cause: NocoBase's Time Interpretation
The consistent 8-hour (or N-hour) shift across different UTC offsets points to a single culprit: NocoBase's application-level interpretation of timestamps retrieved from the database, rather than a fundamental flaw in either PostgreSQL or MySQL's time storage mechanisms themselves. Both databases correctly handle and store time information, often in UTC.
The prevailing theory, supported by the test results, is that NocoBase retrieves a timestamp value from the database (which might be stored as UTC by PostgreSQL, or as UTC by MySQL's `TIMESTAMP` type) and then, in its own code, incorrectly treats this value as if it were already in the server's local timezone. For instance, if PostgreSQL returns `2023-10-27 02:00:00Z` (UTC) and the server's `TZ` is set to `Asia/Shanghai` (UTC+8), NocoBase should ideally display this as `2023-10-27 10:00:00`. Instead, it appears to display it as `2023-10-27 02:00:00`, effectively ignoring the +8 hour offset and presenting a time that is 8 hours behind what it should be for a user in that timezone.
This misinterpretation can be likened to receiving a letter from an international friend, where the postmark clearly indicates it was sent from London (UTC), but you read it assuming it was sent from your local city without accounting for the time difference. The content is the same, but the time reference is off.
Implications and Mitigation
This bug has significant implications for any NocoBase application where accurate time tracking is crucial, such as in logging, scheduling, or any feature involving user-specific event times. The core issue lies in NocoBase's server-side handling of timezones after fetching data from the database. While both PostgreSQL and MySQL can store time data accurately, NocoBase's application layer needs to correctly apply the server's configured timezone offset when presenting these times to the user, or when performing any time-based calculations.
The most direct mitigation, as suggested by the pattern, involves ensuring the server's `TZ` environment variable is correctly set to the desired timezone (e.g., `Asia/Shanghai` for UTC+8). However, this does not fix the underlying misinterpretation within NocoBase. A true fix would require NocoBase developers to ensure that when a timestamp is retrieved, its UTC nature is recognized, and the appropriate offset is applied based on the server's `TZ` setting before the time is displayed or used in further logic. Until such a fix is implemented in NocoBase itself, users relying on accurate, localized timestamps may face ongoing challenges, especially those operating outside of UTC.