mysql getting epoch time

Question

I am using a table with:

CREATE TABLE tv (
    datetime datetime NOT NULL,
    value int(4),
    metric varchar(25),
    PRIMARY KEY (datetime)
);

My system is in CET so I get this from a select:

select * from tv;
+---------------------+-------+----------+
| datetime            | value | metric   |
+---------------------+-------+----------+
| 2017-08-09 14:17:27 |     0 | TV power |
| 2017-08-09 14:20:04 |     0 | TV power |
| 2017-08-09 14:40:04 |     0 | TV power |
| 2017-08-09 14:45:03 |     0 | TV power |

When I try to graph it (in grafana), I use:

SELECT
  UNIX_TIMESTAMP(datetime) as time_sec,
  value,
  metric
FROM tv
ORDER BY datetime

And it works fine except that it writes everything with 2 hours more in the future. So I guess this is because the UNIX_TIMESTAMP is thinking that the datetime is in UTC and looking at the system value, as it is CET, it is doing a +02:00 before converting to epoch or something like this.

I get this also:

SELECT @@global.time_zone;
+--------------------+
| @@global.time_zone |
+--------------------+
| SYSTEM             |
+--------------------+
1 row in set (0.00 sec)

Meaning it is using the system timezone which is CET.

How can I get my UNIX_TIMESTAMP to convert correctly to the CET?

Thanks.

I think you can find your answer in the following link: https://stackoverflow.com/questions/930900/how-do-i-set-the-time-zone-of-mysql — Besart Jashari, Aug 09 '17 at 13:02
I think you can find you answer in this : [link](https://stackoverflow.com/questions/930900/how-do-i-set-the-time-zone-of-mysql) — Besart Jashari, Aug 09 '17 at 13:11

score 0 · Answer 1 · answered Aug 09 '17 at 14:00

Here is what the documentation has to say about UNIX_TIMESTAMP:

If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC.

From what I understand, MySQL is shifting the input timestamp to UTC time, doing the calculation, and then returning the offset still in UTC time. One option to correct for this would be to shift your timestamps from "UTC" time to "CET" time before calling UNIX_TIMESTAMP. I use quotes here, because your timestamps are already in the desired CET timezone, but we have to spoof MySQL by countering its own efforts to be smart and convert everything to UTC.

Something along these lines should work:

SELECT
    UNIX_TIMESTAMP(CONVERT_TZ(datetime, 'UTC', 'CET')) AS time_sec,
    value,
    metric
FROM tv
ORDER BY datetime

Once again, we are shifting your timestamps from UTC to CET time, to offset UNIX_TIMESTAMP() which will be doing the opposite of that.

If the call above to CONVERT_TZ() does not work for you, you may have to spend some time configuring your MySQL timezone tables. Here is a SO question which will get you started:

Database returned an invalid value in QuerySet.dates()

As a final comment, I think the best long term solution for you would be to just store your timestamps in UTC time. Then, you only need to convert for incoming and outgoing dates, but not for internal MySQL calculations. Storing all timestamp information in UTC time is a common database practice.

mysql getting epoch time

1 Answers1