Mysql and daylight saving time

General
1

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.

image
image295Ă—245 16.4 KB

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()

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

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?

2

I think that there was a related thread a few weeks ago that went into depth about MySQL locale and date/time settings.

3

I guess that was this thread 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

               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

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.

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.

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

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?

4

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.

5

Don't know if you have found this but it appears to be about UTC
MySQL UTC_TIMESTAMP() function - w3resource

6

Oh that's promising.

Looks like you can store UTC timestamps regardless of database, server or any other timezone setting.

Thanks!

7

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

.

image
image513Ă—157 4.8 KB

8

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

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

9

jbudd if your chart is displaying local time, then is it not displaying correctly as at 2am the time becomes 1am again?

10

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.

11

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.

1 Like
12

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.

13

They would only be "out" for the locale time, they would still be correct for UTC. :smile: Or GMT as we Brit's still prefer to call it. :rofl:

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

14

Yes, that goes without saying obviously.