Node-RED + Oracle, node bringing incorrect dates and times to the database

General
1

Hello everyone, I would like to know if there is somehow a solution to deal with this divergence of information between Node-RED and Oracle, my problem is in capturing the date and time of my database, the message arrives in my payload 3 hours apart

Query result in the database:

image
image1678×69 13.7 KB

msg.payload:

{
  "COD_OPERACAO": 948,
  "DSC_OPERACAO": "Aguard.Carregar",
  "OPE_PARADA": "S",
  "OPER_ATIVA": "S",
  "GAM_NRO_OS": null,
  "GBM_DATAABERTURA": "2022-01-27T11:12:37.000Z",
  "GBM_DATAENCERRA": "2022-01-27T11:55:59.000Z",
  "DAT_INICIO_APONT": "2022-01-27T11:54:25.000Z",
  "GBM_VERSAOMOBILE": "1.1.7",
  "GAM_DATAINCLUSAO": "2022-01-27T11:55:37.000Z",
  "GAM_MEDICAOINICIAL": 697483.5,
  "GAM_MEDICAOFINAL": 697483.5
}

I'm using the palette
"node-red-contrib-oracledb-mod" version 0.6.1
Node-RED version 2.1.6

2

Firstly I don't use oracle so cannot comment on the oracle node however it looks like potentially you are updating or reusing an object which is causing values to change by reference.

And without your flow is very difficult to to know for sure.

To help you help yourself, make a small demo flow with an inject, an oracle node with a select query and a debug node. Do the values look correct in this simple flow?

3

image
image854×224 14 KB

Hi, thanks for responding so fast, let me try to explain myself, what I'm trying to do is simply a "SELECT * FROM" on a table, nothing more.
But the information that I receive does not match the information that is in the database, here I receive "12:30:00" but there is "09:30:00" for example... in all data related to the date /hour I get this difference of 3 hours less

4

What is the current time on the node-red server?

What is the time on the oracle server?

What is the time & timezone of the computer where you are looking at node red in the browser?

Info..
"Oracle Time Zone | Database Journal" Oracle Time Zone | Database Journal

5

That looks like Portuguese, are you in Brasil, currently UTC -3 hours?
Well, bits of Brasil anyway

6

Yes, I'm in Brazil, here the timezone is "GMT-3" and node-red is giving me "000Z"

7

The server and viewer client times are correct, from what I understand the divergence is in the load that node-red understands, instead of GMT-3 it is delivering 000Z

8

Node-red displays times in UTC

Could you check the times & timezones and let us know?

Without all of the information it is difficult to assess

Note: I'm not saying it isn't a bug in the oracle node or even node red but the information is necessary to assess.

9

Timezones are a nightmare.

I use Mariadb which has a timezone setting.
I guess oracle has something similar.

Not sure if when I insert a time the database decides to convert it to database time.
Not sure if when I retrieve it the database decides to convert it to server time.
And I think when I look at it the browser converts it to the browser's time zone.

I think the best option is to have database time set to UTC and always store time converted to UTC.
That way you only have to wrestle with the browser? I'm not sure. In UK I only have to worry about it for 6 months of the year.
But I don't know what would happen to existing data if I changed the database timezone.

10

Not sure if this is the information you asked for

image
image753×161 8.3 KB

11

That screenshot suggests the browser computer has a different time or time zone to the node red server.

Which computer are you browsing from?

12

This screenshot shows a node-red service running localhost as is the system date/time and it returning me a different timestamp

image
image779×223 24.8 KB

13

Have you changed the system time after starting node-red?

Also, if you click the timestamp, it cycles through the various representations...

chrome_qOPSNfXzCY

could you tell/show me what you get please?

14

I need the timezone setting of your server and client (I realise they might be both the same machine - but i dont know unless told :slight_smile: )

On windows...

image
image860×579 43.6 KB

15

Client Node-RED

image
image887×615 65.4 KB

Server Oracle+Node-RED
image

16

and this...

The problem I have assessing this is your answers are disjointed and are several hours apart - I still have no idea if the times displayed in node-red debug, the server time, the client time and timestamps from oracle are relative to each other or even if they are relevant.

17

Realmente era o formato, conforme a primeira imagem eu demostro que o payload chega em formato "String" convertendo para "Number" eu consegui ter mais representações

image

image

18

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