Timezone & Daylight Savings Issues

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

[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!

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