Standardize Timestamps to ISO-8601 UTC Strings for UpdatedAt and ChangedAt Fields

[cadam11]

In our current implementation, the ChangedAt timestamp is set in TypeScript code after replaying a record, while the UpdatedAt timestamp is set by a trigger within SQL Server. This process can lead to time zone discrepancies between the two fields, especially if the SQL Server and TypeScript code run in different time zones.

Analysis

A new Date object in JavaScript is internally represented as epoch milliseconds, so time zones or daylight saving time (DST) do not affect its numeric value. However, if we are storing rendered strings, we should use ISO-8601 format in UTC to ensure consistency.

On the SQL side, we can use GETUTCDATE() to avoid embedding any time zone information. The format returned by GETUTCDATE() in SQL is interpreted by JavaScript as the local time zone unless we append a 'Z' to indicate UTC.

Proposed Solution

Use GETUTCDATE() in SQL: Update the SQL trigger to set UpdatedAt as an ISO-8601 UTC string by appending a 'Z'.

UpdatedAt = CONCAT(GETUTCDATE(), 'Z')

[AN-BC]

@cadam11 the CreatedAt field is set by the default value in all the columns so this would also require

• All existing tables/fields having this default changed
• All future tables doing the same thing
• for users of MJ following the same thing

Is there another approach where we don't have to make such significant changes?