# Mysql and daylight saving time
**URL:** https://discourse.nodered.org/t/mysql-and-daylight-saving-time/92713
**Category:** General
**Created:** [27 October 2024 16:46 UTC](https://discourse.nodered.org/t/mysql-and-daylight-saving-time/92713 "2024-10-27T16:46:34Z")
**Posts on this page:** 15
**Page:** 1
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [27 October 2024 16:46 UTC](https://discourse.nodered.org/t/mysql-and-daylight-saving-time/92713/1 "2024-10-27T16:46:34Z")
I was 100% certain that timestamps in my database are in UTC, but following last night's shift to GMT I seem to have evidence to the contrary in my 15 minute temperature records.
```auto
select id, timestamp, temperature from doorsensor where id between 4933 and 4945;
+------+---------------------+-------------+
| id | timestamp | temperature |
+------+---------------------+-------------+
| 4933 | 2024-10-27 00:25:28 | 12.7 |
| 4934 | 2024-10-27 00:40:28 | 12.7 |
| 4935 | 2024-10-27 00:55:28 | 12.7 |
| 4936 | 2024-10-27 01:10:28 | 12.7 | }
| 4937 | 2024-10-27 01:25:28 | 12.7 | }
| 4938 | 2024-10-27 01:40:28 | 12.7 | }
| 4939 | 2024-10-27 01:55:28 | 12.7 | }
| 4940 | 2024-10-27 01:10:28 | 12.7 | ****** Repeated timestamps
| 4941 | 2024-10-27 01:25:28 | 12.7 |
| 4942 | 2024-10-27 01:40:28 | 12.8 |
| 4943 | 2024-10-27 01:55:28 | 12.9 |
| 4944 | 2024-10-27 02:10:28 | 12.8 |
| 4945 | 2024-10-27 02:25:28 | 12.8 |
```
This is my table definition, note timestamp default now()
```auto
CREATE TABLE `doorsensor` (
`id` int(11) NOT NULL AUTO_INCREMENT,
timestamp datetime not null default now(),
temperature float null,
humidity float null,
dewpoint float null,
pressure float null,
seapressure float null,
gas float null,
PRIMARY KEY (`id`)
);
```
And the INSERT statement
```auto
newmsg.topic = "insert into doorsensor"
newmsg.topic += "(temperature, humidity, dewpoint, pressure, seapressure, gas)"
newmsg.topic += "VALUES(:temperature, :humidity, :dewpoint, :pressure, :seapressure, :gas)"
```
How come the duplicated timestamps?
What do I need to change to store data in UTC?
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [27 October 2024 17:57 UTC](https://discourse.nodered.org/t/mysql-and-daylight-saving-time/92713/2 "2024-10-27T17:57:23Z")
I think that there was a related thread a few weeks ago that went into depth about MySQL locale and date/time settings.
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [27 October 2024 18:55 UTC](https://discourse.nodered.org/t/mysql-and-daylight-saving-time/92713/3 "2024-10-27T18:55:09Z")
I guess that was this thread [https://discourse.nodered.org/t/yet-another-sql-timestamp-doubt/91301/14](https://discourse.nodered.org/t/yet-another-sql-timestamp-doubt/91301/14)
I have looked online but i confess I'm overwhelmed by all the out of date / incorrect information and jargon.
My database is running on a Pi.
timedatectl gives me
```auto
Local time: Sun 2024-10-27 18:18:50 GMT
Universal time: Sun 2024-10-27 18:18:50 UTC
RTC time: n/a
Time zone: Europe/London (GMT, +0000)
System clock synchronized: yes
NTP service: active
RTC in local TZ: no
```
and in mysql
```auto
SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | SYSTEM |
+--------------------+---------------------+
1 row in set (0.000 sec)
```
I understand that means MySQL is using the system timezone but I don't know how to interpret the timedatectl output.
Does `Time zone: Europe/London (GMT, +0000)` mean it's GMT today, or is it always GMT?
Trying to follow the examples online is most frustrating...
`edit /etc/my.cnf.d/server.conf`
Doesn't exist.
```auto
sudo vi /etc/mysql/my.cnf
find [mysql] section
```
No such section.
There is a file /etc/mariadb/mariadb.conf.d/50-server.cnf which contains a [mysqld] section.
```auto
After opening the file, we need to scroll down a bit to find the `[mysqld]` section, and here we will see `default-time-zone = "+00:00"`. Here `"+00:00"` shows an offset from the GMT zone.
```
Nope.
Well let's add it and see what happens. Umm do I need those back ticks?
Restart mysql.
Now I see
```auto
SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| +00:00 | +00:00 |
+--------------------+---------------------+
```
Is that correct?
I guess I'll need to subtract an hour from all the timestamps before last night?
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [27 October 2024 22:13 UTC](https://discourse.nodered.org/t/mysql-and-daylight-saving-time/92713/4 "2024-10-27T22:13:15Z")
Apologies but it's been a long time since I used MySQL. There should be a way to make sure that all date/datetime entries use UTC but I'm afraid I don't know the details.
---
### Author:  [@Buckskin](https://discourse.nodered.org/u/Buckskin)
#### Post date: [28 October 2024 19:34 UTC](https://discourse.nodered.org/t/mysql-and-daylight-saving-time/92713/5 "2024-10-28T19:34:01Z")
Don't know if you have found this but it appears to be about UTC
[MySQL UTC\_TIMESTAMP() function - w3resource](https://www.w3resource.com/mysql/date-and-time-functions/mysql-utc_timestamp-function.php)
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [28 October 2024 19:49 UTC](https://discourse.nodered.org/t/mysql-and-daylight-saving-time/92713/6 "2024-10-28T19:49:53Z")
Oh that's promising.
Looks like you can store UTC timestamps regardless of database, server or any other timezone setting.
Thanks!
---
### Author:  [@shubhamnodered](https://discourse.nodered.org/u/shubhamnodered)
#### Post date: [29 October 2024 09:46 UTC](https://discourse.nodered.org/t/mysql-and-daylight-saving-time/92713/7 "2024-10-29T09:46:40Z")
@jbudd,
**\> I guess I'll need to subtract an hour from all the timestamps before last night?**
**\> I understand that means MySQL is using the system timezone but I don't know how to interpret the timedatectl output.**
==\> No you do not need to do anything. Take my timedatectl as a reference. In my case local time (Timezone in which PI is running) is ahead of UTC time by 05:30 with timezone as Asia/Kolkata. Now coming to your case, local time and universal time is same when running timedatectl, it implies your pi (system) is running at UTC/GMT timezone. And since MySQL server is also running on PI, MySQL timezone is showing as System (i.e., PI timezone which is local time zone). In your case, when you push the timestamp data from node-red to MySQL database, the stored value will be in UTC time zone.
.
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [29 October 2024 10:53 UTC](https://discourse.nodered.org/t/mysql-and-daylight-saving-time/92713/8 "2024-10-29T10:53:37Z")
Thank you for your reply @shubhamnodered, always valuable to get time reference opinions from other timezones.
But I think India has (wisely) abandoned Daylight Saving Time and you are on India Standard Time all year round.
We still cling to British Summer Time and I _think_ my SQL select above shows a loop in timestamps in the table.
This is the Mysql command line interface, It's as near as I can get to seeing what's actually in the table without it being adjusted to the server/PC/browser's preferences.
Unfortunately I did not think to run `timedatectl` on my Pi before the clocks went back last week, but I am fairly sure that the `date` command would have shown the time in BST - I might be wrong!
Today it shows eg
```auto
$ date
Tue 29 Oct 10:50:19 GMT 2024
```
My PC, from which I access the Linux command line, also says it's 10:50.
---
### Author:  [@E1cid](https://discourse.nodered.org/u/E1cid)
#### Post date: [29 October 2024 11:22 UTC](https://discourse.nodered.org/t/mysql-and-daylight-saving-time/92713/9 "2024-10-29T11:22:48Z")
jbudd if your chart is displaying local time, then is it not displaying correctly as at 2am the time becomes 1am again?
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [29 October 2024 13:26 UTC](https://discourse.nodered.org/t/mysql-and-daylight-saving-time/92713/10 "2024-10-29T13:26:15Z")
I think it's probably true that the chart shows local time, since it loops back at 2am. Questionable if that's desirable.
But I don't think the output from the command line SQL select should loop back, think that indicates the DB is not set to use UTC.
Unfortunately I don't know if the mysql CLI helpfully converts the timestamp into the locale timezone before displaying it.
---
### Author:  [@E1cid](https://discourse.nodered.org/u/E1cid)
#### Post date: [29 October 2024 13:36 UTC](https://discourse.nodered.org/t/mysql-and-daylight-saving-time/92713/11 "2024-10-29T13:36:03Z")
> [@jbudd](#):
>
> I think it's probably true that the chart shows local time, since it loops back at 2am. Questionable if that's desirable.
I don't see how you would avoid it, as if you displayed UTC then all times would be out for half the year. Day light savings is a pain, about time they stopped using it.
It certainly looks like DB is storing local time.
You could store a unix timestamp in milliseconds, that would insure stored date/time are UTC.
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [29 October 2024 14:19 UTC](https://discourse.nodered.org/t/mysql-and-daylight-saving-time/92713/12 "2024-10-29T14:19:59Z")
> [@jbudd](#):
>
> I was 100% certain that timestamps in my database are in UTC, but following last night's shift to GMT I seem to have evidence to the contrary in my 15 minute temperature records.
Looking back at your first post, that is exactly what I would expect to see if the timestamps in the DB are in UTC, but the browser showing the chart is running in local time.
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [29 October 2024 14:57 UTC](https://discourse.nodered.org/t/mysql-and-daylight-saving-time/92713/13 "2024-10-29T14:57:52Z")
> [@E1cid](#):
>
> as if you displayed UTC then all times would be out for half the year
They would only be "out" for the locale time, they would still be correct for UTC. 😄 Or GMT as we Brit's still prefer to call it. 🤣
But yes, stop pandering to the farming community and lets ditch DST! (Apologies to any farmers in the forum but it is only the farming lobby that stops the UK from ditching DST).
---
### Author:  [@E1cid](https://discourse.nodered.org/u/E1cid)
#### Post date: [29 October 2024 15:11 UTC](https://discourse.nodered.org/t/mysql-and-daylight-saving-time/92713/14 "2024-10-29T15:11:55Z")
> [@TotallyInformation](#):
>
> They would only be "out" for the locale time, they would still be correct for UTC.
Yes, that goes without saying obviously.
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [27 January 2025 15:12 UTC](https://discourse.nodered.org/t/mysql-and-daylight-saving-time/92713/15 "2025-01-27T15:12:16Z")
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.