Created DateTime TimeZone saving issue

owainjones.dev · March 14, 2025, 5:24pm

I have also ran into this issue:

Time Zone issue on built in DateTime fields (CreateDate, UpdateDate etc...)

opened 07:31PM - 22 Apr 24 UTC

type/bug

### Which Umbraco version are you using? (Please write the *exact* version, exam…ple: 10.1.0) 13.2.2 ### Bug summary Built in _DateTime_ properties like `CreateDate` and `UpdateDate`, return a _DateTime_ with incorrect TimeZone and Daylight Savings information, when using a SQL Server database, which causes UTC conversions to be incorrect. ### Specifics I ran into this issue in v13.2.2, I have some code that shows the elapsed time since a Node has been Published, by converting the _UpdateDate_ to UTC and subtracting it's Ticks from _DateTime.UtcNow.Ticks_, to get the time difference in seconds. This logic all works fine, but the _CreateDate_ and _UpdateDate_ that Umbraco returns, is different depending on if I run **SQLite** or **SQL Server Express**. Discord thread on this issue: https://discord.com/channels/869656431308189746/1232004629441679391 Public mirror: https://discord-chats.umbraco.com/t/18468992/v13-weird-timezone-issue-on-sql-server-express-vs-sqlite ### Steps to reproduce Here's a .zip of a demo project which demonstrates the issue. [Umbraco-13-timezone-issue.zip](https://github.com/umbraco/Umbraco-CMS/files/15067549/Umbraco-13-timezone-issue.zip) As you can see here, when running this project with a **SQLite** database, the _DateTime_ returned is properly set as a Daylight Savings time (as I'm in BST), and when converting to UTC it correctly minuses an hour: ![image](https://github.com/umbraco/Umbraco-CMS/assets/17008780/d016e520-817b-4587-b25a-c66787b18269) But when you run the project with a **SQL Server Express** database, the _DateTime_ returned is not set as Daylight Savings, and therefore does NOT minus an hour when converting to UTC, which messes up the elapsed time calculation: ![image](https://github.com/umbraco/Umbraco-CMS/assets/17008780/a8513458-1b1b-40cc-87a0-17573553b4c3) According to a comment on this closed issue: https://github.com/umbraco/Umbraco-CMS/issues/7078#issuecomment-551600622 > NPoco assumes that all DateTime columns are UTC unless told otherwise in the column attributes Which i understand, we can assume that _CreateDate_ and _UpdateDate_ are stored as server local time (although I imagine if you load balance across time zones, this could be annoying no? Shouldn't these be DateTimeOffset?), so this must indicate that the SQLite and SQL Server database providers are doing different things when reading DateTime columns; it seems like the SQLite provider converts them to local, whilst the SQL Server provider does not? I also changed my Windows Time Zone to _Brisbane (UTC+10)_ and, as expected, I get the exact same issue; the _CreateDate_ and _UpdateDate_ properties don't convert to UTC correctly when using SQL Server. (they stay 10 hours ahead) ### Expected result / actual result _No response_