# Yet another SQL timestamp doubt

**URL:** <https://discourse.nodered.org/t/yet-another-sql-timestamp-doubt/91301>\
**Category:** General\
**Tags:** database, mysql, time\
**Created:** [27 September 2024 10:59 UTC](https://discourse.nodered.org/t/yet-another-sql-timestamp-doubt/91301 "2024-09-27T10:59:50Z")\
**Posts on this page:** 18\
**Page:** 1

<div class="post-metadata">

**Author:** ![shubhamnodered](https://avatars.discourse-cdn.com/v4/letter/s/e8c25b/32.png) [@shubhamnodered](https://discourse.nodered.org/u/shubhamnodered)\
**Post date:** [27 September 2024 10:59 UTC](https://discourse.nodered.org/t/yet-another-sql-timestamp-doubt/91301/1 "2024-09-27T10:59:50Z")

</div>

Hi Guys and Hello node red team,

My query is in reference to the point that Julius @TotallyInformation mentioned = " **Always store DB entries in UTC**" in the article " [Wrong Timezone data in sql query result?!?](https://discourse.nodered.org/t/wrong-timezone-data-in-sql-query-result/60658)"

I found that its two ways to achieve this:

1. Client side conversion - _(Doing conversion in node-red & feed that into SQL)_
2. 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:

1. 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!_]

2. @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...

Regards,

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [27 September 2024 11:14 UTC](https://discourse.nodered.org/t/yet-another-sql-timestamp-doubt/91301/2 "2024-09-27T11:14:12Z")

</div>

Do you have to use mysql? Timestamps in mysql can be a nightmare. If you are storing time series data then you could consider influxdb instead.

---

<div class="post-metadata">

**Author:** ![shubhamnodered](https://avatars.discourse-cdn.com/v4/letter/s/e8c25b/32.png) [@shubhamnodered](https://discourse.nodered.org/u/shubhamnodered)\
**Post date:** [27 September 2024 11:22 UTC](https://discourse.nodered.org/t/yet-another-sql-timestamp-doubt/91301/3 "2024-09-27T11:22:28Z")

</div>

Yeah. I'll try it next. But my domain lies in IIOT. You can understand.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [27 September 2024 11:23 UTC](https://discourse.nodered.org/t/yet-another-sql-timestamp-doubt/91301/4 "2024-09-27T11:23:58Z")

</div>

> [@shubhamnodered](#):
>
> But my domain lies in IIOT

I think influxdb is very much in the IIOT domain.

---

<div class="post-metadata">

**Author:** ![shubhamnodered](https://avatars.discourse-cdn.com/v4/letter/s/e8c25b/32.png) [@shubhamnodered](https://discourse.nodered.org/u/shubhamnodered)\
**Post date:** [27 September 2024 11:37 UTC](https://discourse.nodered.org/t/yet-another-sql-timestamp-doubt/91301/5 "2024-09-27T11:37:42Z")

</div>

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

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/8/088d8891e21e268e9f829cb08ea73afa057be22a.png)

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [27 September 2024 11:53 UTC](https://discourse.nodered.org/t/yet-another-sql-timestamp-doubt/91301/6 "2024-09-27T11:53:06Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![shubhamnodered](https://avatars.discourse-cdn.com/v4/letter/s/e8c25b/32.png) [@shubhamnodered](https://discourse.nodered.org/u/shubhamnodered)\
**Post date:** [27 September 2024 12:02 UTC](https://discourse.nodered.org/t/yet-another-sql-timestamp-doubt/91301/7 "2024-09-27T12:02:01Z")

</div>

Hi Julius,

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.

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [27 September 2024 12:07 UTC](https://discourse.nodered.org/t/yet-another-sql-timestamp-doubt/91301/8 "2024-09-27T12:07:12Z")

</div>

> [@shubhamnodered](#):
>
> "SET GLOBAL SYSTEM = '+00:00'"

I'm not sure what command that is. I believe you need to issue these 2 commands if you don't want to change the conf file:

```sql
SET @@global.time_zone='+00:00';
SET @@session.time_zone='+00:00';

```

If your server(s) are Linux, you can check the tz using `timedatectl` change with something like `sudo timedatectl set-timezone UTC`.

---

<div class="post-metadata">

**Author:** ![shubhamnodered](https://avatars.discourse-cdn.com/v4/letter/s/e8c25b/32.png) [@shubhamnodered](https://discourse.nodered.org/u/shubhamnodered)\
**Post date:** [27 September 2024 12:10 UTC](https://discourse.nodered.org/t/yet-another-sql-timestamp-doubt/91301/9 "2024-09-27T12:10:28Z")

</div>

Sorry, My mistake. I typed wrong here in chat. What i used:

> SET GLOBAL time\_zone = '+00:00';

Even though your discussion lies with **UTC conversion taken care by server.** , problem still remains the same

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/a/8a94fc32b87907e6542234162e259571928e1bda.png)

---

<div class="post-metadata">

**Author:** ![shubhamnodered](https://avatars.discourse-cdn.com/v4/letter/s/e8c25b/32.png) [@shubhamnodered](https://discourse.nodered.org/u/shubhamnodered)\
**Post date:** [27 September 2024 15:56 UTC](https://discourse.nodered.org/t/yet-another-sql-timestamp-doubt/91301/10 "2024-09-27T15:56:45Z")

</div>

HI Juliu @TotallyInformation, ,

Waiting for your reply.

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [27 September 2024 16:17 UTC](https://discourse.nodered.org/t/yet-another-sql-timestamp-doubt/91301/11 "2024-09-27T16:17:11Z")

</div>

Didn't see a question in your last reply.

Having set the global and session defaults, what is a datetime field now returning?

---

<div class="post-metadata">

**Author:** ![shubhamnodered](https://avatars.discourse-cdn.com/v4/letter/s/e8c25b/32.png) [@shubhamnodered](https://discourse.nodered.org/u/shubhamnodered)\
**Post date:** [28 September 2024 04:25 UTC](https://discourse.nodered.org/t/yet-another-sql-timestamp-doubt/91301/12 "2024-09-28T04:25:10Z")

</div>

HI @TotallyInformation,

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

---

<div class="post-metadata">

**Author:** ![shubhamnodered](https://avatars.discourse-cdn.com/v4/letter/s/e8c25b/32.png) [@shubhamnodered](https://discourse.nodered.org/u/shubhamnodered)\
**Post date:** [30 September 2024 08:34 UTC](https://discourse.nodered.org/t/yet-another-sql-timestamp-doubt/91301/13 "2024-09-30T08:34:31Z")

</div>

Hi @TotallyInformation,

Waiting for your answer.

---

<div class="post-metadata">

**Author:** ![shubhamnodered](https://avatars.discourse-cdn.com/v4/letter/s/e8c25b/32.png) [@shubhamnodered](https://discourse.nodered.org/u/shubhamnodered)\
**Post date:** [1 October 2024 06:56 UTC](https://discourse.nodered.org/t/yet-another-sql-timestamp-doubt/91301/14 "2024-10-01T06:56:25Z")

</div>

HI @knolleary, @Steve-Mcl, @E1cid can anyone guide me with this one?

---

<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:** [1 October 2024 07:46 UTC](https://discourse.nodered.org/t/yet-another-sql-timestamp-doubt/91301/15 "2024-10-01T07:46:27Z")

</div>

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

There are docs for mySQL that detail the intricacies and differences of timestamps and DATETIME fields here: [https://dev.mysql.com/doc/refman/8.4/en/datetime.html#:~:text=MySQL%20converts%20TIMESTAMP%20values%20from,connection%20is%20the%20server's%20time](https://dev.mysql.com/doc/refman/8.4/en/datetime.html#:~:text=MySQL%20converts%20TIMESTAMP%20values%20from,connection%20is%20the%20server's%20time).

* * *

If you want further help, provide detail of everything including proofs like selecting the [`system_time_zone`](https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html#sysvar_system_time_zone) and the [`time_zone`](https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html#sysvar_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.

---

<div class="post-metadata">

**Author:** ![ThingsTinkerer](https://avatars.discourse-cdn.com/v4/letter/t/91b2a8/32.png) [@ThingsTinkerer](https://discourse.nodered.org/u/ThingsTinkerer)\
**Post date:** [1 October 2024 08:10 UTC](https://discourse.nodered.org/t/yet-another-sql-timestamp-doubt/91301/16 "2024-10-01T08:10:46Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![shubhamnodered](https://avatars.discourse-cdn.com/v4/letter/s/e8c25b/32.png) [@shubhamnodered](https://discourse.nodered.org/u/shubhamnodered)\
**Post date:** [1 October 2024 09:39 UTC](https://discourse.nodered.org/t/yet-another-sql-timestamp-doubt/91301/17 "2024-10-01T09:39:16Z")

</div>

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

---

<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:** [30 December 2024 09:40 UTC](https://discourse.nodered.org/t/yet-another-sql-timestamp-doubt/91301/18 "2024-12-30T09:40:11Z")

</div>

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