Client side conversion - (Doing conversion in node-red & feed that into SQL)
Server side conversion - (By using SET GLOBAL time-zone = '+00:00')
And as per GPT, first one is more preferrable.
Let's discuss client side approach:
Issue - Let's say, i am working in IST(UTC+05:30) and current time is 15:00:00 then this timestamp(converted to UTC in nodered) pushed into SQL & is logged as +09:30:00 (UTC). Now, when i read this value back into node-red, this get value further subtracted by 05:30hr, and so the current time is reading as '04:00:00'.
Now, there is two way to address this issue:
a. Either using CONVERT_TZ(timestamp, '+00:00', '+11:00') in func node
b. Or, By setting MySQL database's timezone in MySQL node to +05:30(IST).
The thing is:
The 2nd approach that i mentioned above is not giving desirable result (which it should) because i am categorically mentioning the timezone of SQL server. Because by setting it as +05:30, it push back reading by 11:00hrs from actual time. So, what exactly is happening here? [note- but if i set it as -05:30, node-red displays SQL time correctly! ]
@TotallyInformation@E1cid as already mentioned that node-red converts current local time into ISO 8061 UTC format which is true, But what if i/p data is already in UTC format(as in my case as i'm importing UTC time data from SQL)? Why it cannot able to categorize it as UTC and goes on to further subtract it by 05:30 hrs showing actual time lagging by 11:00hrs?
Also, MySQL time-zone is 'SYSTEM' which is 'UTC+05:30'
Hope, i made myself clear. Please, help me understand this...
You may be right but i haven't tried it yet. Heard about it LOT! - like High cardinality, data compression & more suited for times-series data. Will start on it with documention maybe from day after tomorrow.
However, the issue that i raised above still remain unanswered. And i think, the issue lies with Node red. Why? - Because, I visualized the same SQL timestamp data into Grafana and data showed correctly mapped with time[For correct reading, In datasouce connection settings, i assigned the SQL session timezone as UTC+05:30 [same thing, i tried in MySQL node in node-red as already mentioned but time read lagged by 11:00hr].
Have you checked the tz on your server(s)? Are they set to use UTC?
By default, MySQL uses the servers system timezone settings. You can force it to use UTC though by adding this to the conf file and restarting the server: default_time_zone='+00:00'
By using UTC for everything except user inputs and user outputs, you avoid all of the pitfalls of timezone and daylight savings changes.
For IoT in particular, daylight savings swaps can really mess you up. Since there are typically 2 times a year where you either get duplicate timestamps or a gap.
As i already mentioned, i particular talked & discussing client side(node-red) UTC conversion [to avoid confusion] with server runing in local time [mentioned at foot note].
Though, i also tried server side UTC conversion as well by using "SET GLOBAL SYSTEM = '+00:00'" but result remained the same.
Timestamp data when read on node-red, lags by 05:30hrs.
For example, The given timestamp data with time 15:00:00 converted to UTC(09:30:00) is pushed into MySQL and saved with 09:30:00. When same data read into Node-red from MySQL, it get further lagged by 05:30:00 thus showing 04:00:00.
@shubhamnodered it is generally not good netiquette to randomly tag users not in the conversation.
As far as I can see, you have been given good information.
The general rule of thumb is to use UTC. That does not necessarily mean you set the Node-RED server or Database Servers timezone to UTC it means you set your database (and sometimes the client connection) and you provide correctly formed data for the field type of the table. All of this dictates what will be stored and retrieved and you have to get the right mix to do it correctly.
As far as I can see, this is not really a node-red issue but more a combination of database configuration/connection, table field configuration, what you are pushing into it and how you are reading it.
If you want further help, provide detail of everything including proofs like selecting the system_time_zone and the time_zone variable from both Node-RED and a separate client (like CLI), details of your Node-RED server (docker based? timezone? use ctrl+shift+p -> system info), the DDL of your table and a BASIC demo flow that performs an INSERT and SELECT that clearly demonstrates your issue.
I worked for many years with MySQL used as data warehouse and big data statistics with unix timestamps. These were stored as normal integers.
To isolate your problem, why not first convert your date to unix timestamp outside the db (before insert) and again outside the db (after select)? That should exclude all timezone settings of the database. If you get correct result then, then try to convert from timezone to utc unix timestamp in the sql insert. Then convert it back again in sql select.
My Apologies if it felt that way. @TotallyInformation really tried to help me & i pushed myself to resolve the query on my own from every angle but i think my approach wasn't enough
