Page 1 of 1
Timezone & Daylight Savings Issues
Posted: Wed May 31, 2017 12:38 pm
by vario
I have a report which selects a field defined as a TIMESTAMP in MySQL. When I select values from it using date_format(p1.fixtime,'%H:%i'), they are all adjusted to the current timezone setting for daylight savings. i.e a database value of '2017-02-07 15:00:00' will be reported as '16:00'. I have set my timezone to 'Europe/London' both in nuBuilder and at the OS level.
Do you have any ideas / suggestions as to where I am going wrong with this?
Neil.
Re: Timezone & Daylight Savings Issues
Posted: Thu Jun 08, 2017 2:11 pm
by vario
Following on...
From "After Save":
Code: Select all
nuRunQuery("insert into test values ('2017-02-01 12:00:00')");
will put '2017-02-01 11:00:00' into the table.
From MySQL CLI:
Code: Select all
insert into test values ('2017-02-01 12:00:00')
will put '2017-02-01 12:00:00 into the table.
I don't understand why the same SQL gives different results!
Re: Timezone & Daylight Savings Issues
Posted: Fri Jun 09, 2017 10:46 am
by vario
Here is transcript of a MySQL session investigating my timezone issues:
Code: Select all
MariaDB [nubuilder]> SELECT @@global.time_zone;
+--------------------+
| @@global.time_zone |
+--------------------+
| SYSTEM |
+--------------------+
1 row in set (0.00 sec)
MariaDB [nubuilder]> SELECT @@session.time_zone;
+---------------------+
| @@session.time_zone |
+---------------------+
| SYSTEM |
+---------------------+
1 row in set (0.00 sec)
MariaDB [nubuilder]> SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);
+--------------------------------+
| TIMEDIFF(NOW(), UTC_TIMESTAMP) |
+--------------------------------+
| 01:00:00 |
+--------------------------------+
1 row in set (0.01 sec)
MariaDB [nubuilder]> select * from test;
+----------+---------------------+---------------------+------------+----------+
| test_id | test_stamp | test_dt | test_d | test_t |
+----------+---------------------+---------------------+------------+----------+
| 1002 | 2017-02-01 11:00:00 | 2017-02-01 12:00:00 | 2017-02-01 | 12:00:00
| 1001 | 2017-02-01 12:00:00 | 2017-02-01 12:00:00 | 2017-02-01 | 12:00:00
+----------+---------------------+---------------------+------------+----------+
2 rows in set (0.00 sec)
MariaDB [nubuilder]> SET time_zone = '+1:00';
Query OK, 0 rows affected (0.00 sec)
MariaDB [nubuilder]> SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);
+--------------------------------+
| TIMEDIFF(NOW(), UTC_TIMESTAMP) |
+--------------------------------+
| 01:00:00 |
+--------------------------------+
1 row in set (0.00 sec)
MariaDB [nubuilder]> SELECT @@global.time_zone;
+--------------------+
| @@global.time_zone |
+--------------------+
| SYSTEM |
+--------------------+
1 row in set (0.00 sec)
MariaDB [nubuilder]> SELECT @@session.time_zone;
+---------------------+
| @@session.time_zone |
+---------------------+
| +01:00 |
+---------------------+
1 row in set (0.00 sec)
MariaDB [nubuilder]> select * from test;
+----------+---------------------+---------------------+------------+----------+
| test_id | test_stamp | test_dt | test_d | test_t |
+----------+---------------------+--------------------+------------+----------+
| 1002 | 2017-02-01 12:00:00 | 2017-02-01 12:00:00 | 2017-02-01 | 12:00:00
| 1001 | 2017-02-01 13:00:00 | 2017-02-01 12:00:00 | 2017-02-01 | 12:00:00
+----------+---------------------+---------------------+------------+----------+
2 rows in set (0.00 sec)
I don't yet understand how setting the offset to "+1:00" gives a different value when selecting the TIMESTAMP field.
I have decided my simplest workaround for the time being is to comment out
Code: Select all
nuRunQuery("SET time_zone = '$offset'");
in nucommon.php which then means my TIMESTAMP columns report the correct values in nubuilder. FYI, the TIMESTAMP values are created by a separate application and I report on them using nubuilder.
Neil.