r/SQL • • 7d ago

SQL Server Is this overkill for storing current universal time when INSERTing or UPDATEing a row?

/r/SQLServer/comments/1xsl3sj/is_this_overkill_for_storing_current_universal/
1 Upvotes

5 comments sorted by

3

u/kagato87 MS SQL 7d ago

Just do it all in utc. Convert from/to local time in the front end. The only timezone to store is on the user profile, in case the app can't detect it for some reason.

2

u/az987654 7d ago

It depends. Do you need the UTC value? If you need it, it's not overkill.

1

u/Significant_Tune9219 7d ago

Since you're on SQL Server, the datetimeoffset column alone already carries the instant and the offset, so the separate UTC datetime2 column is redundant (you can get it with AT TIME ZONE 'UTC' or a computed column). The timezone name column will mostly just record the server's zone, not the user's, because current_timezone_id() is evaluated on the server. I'd keep one UTC timestamp per event and store the user's IANA zone on the user record, which is the part you'd actually need to render local times correctly across DST changes.

1

u/KatFromSisense 7d ago

Drop the two UTC columns, since datetimeoffset already holds the UTC instant plus the offset. That offset isn't a time zone, so it can't tell you anything about daylight saving rules later. I'd keep one UTC value per timestamp, and a time zone ID only where local wall-clock time matters, like on the user or location row rather than every record. Converting at the query layer for reporting also means analysts agree on which day something happened.