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

General
1

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
flow_debug1477Ă—728 42.8 KB

function_select
function_select1174Ă—507 10.7 KB

table
table513Ă—821 16.6 KB

sql
sql885Ă—676 23.8 KB

flow_vs_sql
flow_vs_sql1826Ă—753 78.8 KB

2

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.

3

I have one small correction, datetime2 ist timezone unaware

datetimeoffset 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.

1 Like
4

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