Timezone & Daylight Savings Issues

Timezone & Daylight Savings Issues

Postby vario » Wed May 31, 2017 8:08 pm

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
 
Posts: 36
Joined: Mon Dec 05, 2011 8:53 pm

Re: Timezone & Daylight Savings Issues

Postby vario » Thu Jun 08, 2017 9:41 pm

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
 
Posts: 36
Joined: Mon Dec 05, 2011 8:53 pm

Re: Timezone & Daylight Savings Issues

Postby vario » Fri Jun 09, 2017 6:16 pm

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.
vario
 
Posts: 36
Joined: Mon Dec 05, 2011 8:53 pm


Return to General



cron