# Fix date and time display (read from SQL) on a table

**URL:** <https://discourse.nodered.org/t/fix-date-and-time-display-read-from-sql-on-a-table/96850>\
**Category:** General\
**Created:** [2 May 2025 10:04 UTC](https://discourse.nodered.org/t/fix-date-and-time-display-read-from-sql-on-a-table/96850 "2025-05-02T10:04:29Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![jumpier](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/jumpier/32/44058_2.png) [@jumpier](https://discourse.nodered.org/u/jumpier)\
**Post date:** [2 May 2025 10:04 UTC](https://discourse.nodered.org/t/fix-date-and-time-display-read-from-sql-on-a-table/96850/1 "2025-05-02T10:04:30Z")

</div>

Good morning,

I read data from SQL Server Express Edition 2019 and I need to display it in a table. Since I live in Italy, I notice a difference of two hours (less) between the DB data and the data shown on the Node-Red UI

I tried to use a Change-node to vary the output of the SQL node but I can't figure it out.

Do you have any advice? Thanks

 ![flow_debug](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/0/6037d57b6d575e50af17c151385fea1797da89e2.png)  
 ![function_select](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/9/99bb62e45256c16cf3bebbb482bd920dea64abb6.png)  
 ![table](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/0/a040edd11a2faf85f7fa7c9aac0fdbd142925ace.png)  
 ![sql](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/2/b2ed90fe75853b3dcf81bd8de4a013e980b5048c.png)  
 ![flow_vs_sql](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/5/25fa11a2d5010ad8e0e91b3870208cc64f345a5b.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [2 May 2025 10:14 UTC](https://discourse.nodered.org/t/fix-date-and-time-display-read-from-sql-on-a-table/96850/2 "2025-05-02T10:14:16Z")

</div>

Time is hard!

One thing to realise is the time is (probably) correct but due to UTC vs locale it "looks" different

IMO, the real issue is using `datetime` instead of `datetimeoffset` (datetimeoffset stores TZ and so Node-RED should adjust when getting the value backout of the DB) - but I am not 100% certain where your issue is starting (e.g. is the data written with _correct_ date / UTC or is it when it retrieved?)

There are several kinds of fix.

1. use datetimeoffset everywhere while storing records in UTC
2. disable `Assume UTC` in the driver options and let it figure stuff out implicitly
3. use `moment` in a change node to specify the timezone

My only strong recommendation is that times in the DB are actually UTC (so that you avoid future pain / DST issues / locale issues etc). I get this can be awkward when viewing data - one system I used to use stored both UTC and locale time (made it easier to understand the data when viewing tables in SQL tool) but always retrieve the UTC value and do any conditioning you need to only when coming to display the value.

---

<div class="post-metadata">

**Author:** ![kitori](https://avatars.discourse-cdn.com/v4/letter/k/f08c70/32.png) [@kitori](https://discourse.nodered.org/u/kitori)\
**Post date:** [2 May 2025 11:46 UTC](https://discourse.nodered.org/t/fix-date-and-time-display-read-from-sql-on-a-table/96850/3 "2025-05-02T11:46:15Z")

</div>

I have one small correction, [datetime2](https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-ver16) ist timezone unaware

[datetimeoffset](https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetimeoffset-transact-sql?view=sql-server-ver16) is timezone aware. i normally us this in my mssql tables, since it gives me the least pain when dealing with timezones between systems. I still saves all records in UTC.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [2 May 2025 11:53 UTC](https://discourse.nodered.org/t/fix-date-and-time-display-read-from-sql-on-a-table/96850/4 "2025-05-02T11:53:21Z")

</div>

yeah, my bad, I mixed them up (I will update thread for future readers)

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [31 July 2025 11:54 UTC](https://discourse.nodered.org/t/fix-date-and-time-display-read-from-sql-on-a-table/96850/5 "2025-07-31T11:54:06Z")

</div>

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.