https://discord.umbraco.com logo
Join DiscordCommunities
Powered by
[Issue Opened] v13 weird timezone issue on SQL Ser...
# help-with-umbraco
o
Hi, I just ran into this issue in v13.2.2, I created some code that shows the elapse time since a Node has been Published, by converting the Update Date to UTC and subtracting it's Ticks from DateTime.UtcNow.Ticks, to get the time difference in seconds. (can then convert that to minutes ago, hours ago, and days ago etc...) Whenever I work with DateTime calculations, I always convert to UTC to make sure the dates I'm manipulating match up. This all works fine, but the Create Date and Update Date that Umbraco returns, is different depending on if I run SQLite or SQL Server Express!
And as you can see here, when running with SQLite, 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. https://cdn.discordapp.com/attachments/1232004629441679391/1232004800888180736/image.png?ex=663904e2&is=66268fe2&hm=2a38fd15aecb2df86bc502251cfd325c401a2a6b53ae2d7e1a4976e35ecdce43&
But when I use SQL Server Express, the DateTime returned is not set as Daylight Savings, and therefore does NOT minus an hour when converting to UTC, which messes up the calculation. https://cdn.discordapp.com/attachments/1232004629441679391/1232004890587299910/image.png?ex=663904f7&is=66268ff7&hm=51412dd203310a32cfb4ace5ebf7ee33deb0871f022ae6b21a11804c37b658b3&
This feels like it could be a configuration issue, but I have no idea what that could be, or could this actually be an issue with Umbraco? (if so, I'll raise it on GitHub)
k
You're not reading a 
DateTimeOffset
as a 
DateTime
by mistake in your code? If you stick to 
DateTimeOffset
in your code, you never have to think about UTC conversion.
Sorry, I see now that in SQL Server, the umbraco data type for e.g., Create Date is actually a DateTime without an offset stored. I wonder why this is, it creates all kinds of headaches.
o
I'm just using the 
CreateDate
and 
UpdateDate
properties from the page's models builder model, which is a 
DateTime
k
Not sure how this could be a configuration issue. It looks like Umbraco stores dates as "local time" (local to what? Server? User?) and it looks from your tests like the sqlite provider accidentally has different behavior for reading dates.
o
Yea it's really weird why it's not stored as an Offset... I did find a similar issue report, but it was closed as stale in 2021: https://github.com/umbraco/Umbraco-CMS/issues/7078 (looks like I mentioned it in a another similar issue, which I don't remember doing lol)
k
The issue does say all dates are server-local. I'm still guessing the sqlite provider accidentally converts dates "from local".
Not sure what your workaround would be...
...except to petition to abolish DST. 🙂
Are the Date Picker dates coming from the clientside and the others from the serverside? It's strange that the datetimekind is different. Where is the code claiming the datetimekind is UTC? The only way a database DateTime can be UTC if it's been manually translated by someone knowing the source data offset. Technically Umbraco could use the server offset to provide a datetime with datetimekind UTC but that sounds unlikely.
Let's hope Umbraco 14 only uses DateTimeOffsets. 🕰️
o
Yea both database providers must be doing something different... The picker ones are just properties on my test page, and as for the Kind, I'm just doing a 
.Kind
on the DateTime objects:
Copy code
html
<tr>
    <td><strong>Updated</strong></td>
    <td>@Model.UpdateDate</td>
    <td>@Model.UpdateDate.Kind</td>
    <td>@Model.UpdateDate.ToUniversalTime()</td>
    <td>@Model.UpdateDate.IsDaylightSavingTime()</td>
    <td>@Model.UpdateDate.ElapsedTime()</td>
</tr>
https://cdn.discordapp.com/attachments/1232004629441679391/1232011034391220235/image.png?ex=66390ab0&is=662695b0&hm=09ed6f0547277b2b61f31169d355bcc48ffddd476d44914eba09f7c4bb1fada7&
It is weird how it's showing Create/Update dates as UTC rather than Unspecified on SQL Server
k
And that's probably the problem
o
Yup, I just changed my Windows TimeZone to Brisbane (UTC+10) and, as expected, I get the exact same issue... The Create and Update dates don't convert to UTC properly. Seeing as Denmark is UTC+1 (+2 in summer), I am surprised that this issue hasn't been reported/spotted already. https://cdn.discordapp.com/attachments/1232004629441679391/1232014759357059205/image.png?ex=66390e28&is=66269928&hm=cc0927e338a4985734bcf51ec512a0702dd156f3c06e230e8eeadbc743bfec04&
k
I'd say this thread has everything a ticket needs
o
Aye, I'll get one raised this evening 🙂 thanks for your help mate!
45 Views
PreviousNext
Powered by