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.
Welcome to the nuBuilder forums!
Please register and login to view forums and other content only available to registered users.
Please register and login to view forums and other content only available to registered users.
Timezone & Daylight Savings Issues
-
- Posts: 122
- Joined: Mon Dec 05, 2011 12:23 pm
- nuBuilder Version: 4.5
- Location: Newton Abbot, UK
-
- Posts: 122
- Joined: Mon Dec 05, 2011 12:23 pm
- nuBuilder Version: 4.5
- Location: Newton Abbot, UK
Re: Timezone & Daylight Savings Issues
Following on...
From "After Save": will put '2017-02-01 11:00:00' into the table.
From MySQL CLI: will put '2017-02-01 12:00:00 into the table.
I don't understand why the same SQL gives different results!
From "After Save":
Code: Select all
nuRunQuery("insert into test values ('2017-02-01 12:00:00')");
From MySQL CLI:
Code: Select all
insert into test values ('2017-02-01 12:00:00')
I don't understand why the same SQL gives different results!
-
- Posts: 122
- Joined: Mon Dec 05, 2011 12:23 pm
- nuBuilder Version: 4.5
- Location: Newton Abbot, UK
Re: Timezone & Daylight Savings Issues
Here is transcript of a MySQL session investigating my timezone issues:
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 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.
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 have decided my simplest workaround for the time being is to comment out
Code: Select all
nuRunQuery("SET time_zone = '$offset'");
Neil.