Weird values because of daylight saving time (summer/winter)

Please use template to report bugs and problems. Post here your questions when not sure where else to post
Only for bugs in the Domoticz application! other problems go in different subforums!
Forum rules
Before posting here, make sure you are on the latest Beta or Stable version.
If you have problems related to the web gui, clear your browser cache + appcache first.

Use the following template when posting here:

Version: xxxx
Platform: xxxx
Plugin/Hardware: xxxx
Description:
.....

If you are having problems with scripts/blockly, always post the script (in a spoiler or code tag) or screenshots of your blockly

If you are replying, please do not quote images/code from the first post
stlaha2007
Posts: 415
Joined: Monday 05 October 2015 10:16
Target OS: -
Domoticz version:
Contact:

Re: Weird values because of daylight saving time (summer/winter)

Postby stlaha2007 » Tuesday 08 November 2016 13:43

Please try to look different...
The server is responsible to log the data according to consecutive time ! I.o.w. UTC.
The userview depends on availability of DST (Australians don't know {quote one: "weird behaviour"}) and if the user wants to look with gaps and zigzags or not.

Sorry, that i coded in php and perl, but havent looked at the timelogging and displaytroubles before. But i do understand that it is a user view, like Toulon7559 said.

Sent from my K00C using Tapatalk

User avatar
gizmocuz
Posts: 8119
Joined: Thursday 11 July 2013 18:59
Target OS: Raspberry Pi
Domoticz version: beta
Location: Top of the world
Contact:

Re: Weird values because of daylight saving time (summer/winter)

Postby gizmocuz » Tuesday 08 November 2016 14:35

I dont look at it differently

a) should be correctly logged in the database, without DST, means UTC
b) the presentation to the user should be based on his timezone
Quality outlives Quantity!

stlaha2007
Posts: 415
Joined: Monday 05 October 2015 10:16
Target OS: -
Domoticz version:
Contact:

Re: RE: Re: Weird values because of daylight saving time (summer/winter)

Postby stlaha2007 » Tuesday 08 November 2016 14:51

gizmocuz wrote:I dont look at it differently

a) should be correctly logged in the database, without DST, means UTC
b) the presentation to the user should be based on his timezone

Sorry quote didn't come through, actually ment for gordon... i noticed you look at it, like i do, as its supposed to be...

Sent from my K00C using Tapatalk

gordonb3
Posts: 383
Joined: Friday 22 January 2016 11:15
Target OS: Linux
Domoticz version: custom
Location: The Netherlands
Contact:

Re: Weird values because of daylight saving time (summer/winter)

Postby gordonb3 » Tuesday 08 November 2016 19:52

Allow me to explain.

Domoticz logs the following values and their times:
  1. Last known state of a device
  2. Up to 7 x 24 hours counter values and measurements in 5 minute intervals (the "shortlog)
  3. Daily counters and measurements closed at midnight according to local time zone (the "longlog")
Let's start with (3). Obviously it will be pointless to have times in here that reference anything other than 00:00:00 (although since we are not manually setting this date but allowing SQLight to auto generate it you may see a one second delay on slower systems).
Edit: with respect to localtime of course. We have absolutely no business with knowing what UTC time corresponds with our midnight time. If for instance I would want to know the totals for September 24, 2016, what date do I look for in the tables if datetime is stored as UTC? Think about it.

Number (1) is easy. It makes absolutely no difference what time format is used here, because there is only one value for each device. It therefore makes sense to use the storage format that is most convenient. Since SQL returns time as a string with format "YYYY-MM-DD HH:mm:ss" and the only portable (i.e. works on every system) function we can use to convert this string references localtime, it makes absolute sense to use localtime in the database as well.

In essence the same applies to (2). The trouble in this case however is that when we make a transition from summertime to wintertime the database will contain some overlapping values. The result is some ugly graphs and probably some strange looking reports as well when we simply do a date sort (like is presently done). But this is disposable data. It is discarded after the set amount of 24 hours (from 1 to 7). It has no long time function. The whole issue therefore is purely cosmetic.

Is it possible to convert the entire code base to use UTC? Sure it is. Everything is possible. But why go to the trouble of converting everything to UTC and back again and probably multiple times in a row when in the end the whole application is built around using local time? There's no added value to it. Moving to UTC will not help find the correct value for midnight (or any other specific time) on a given day, because this relates to local time and is therefore subject to DST. That's the whole point. It is not the fix all answer.

And here's a bonus argument: because it makes no sense and will actually be confusing to use UTC for values that reference start of day this would result in a database where some tables contain UTC times and other tables localtime. Which if you would like to address the database with any other application would be complete incomprehensible. Which is probably why I've never seen anyone else use multiple standards for storing the same type of data.
Last edited by gordonb3 on Tuesday 08 November 2016 21:41, edited 1 time in total.
Excito B3 running Gentoo Linux, P1, Rfxtrx433 to read and control TFA, KaKu, EvoHome RFG100
Custom patched Domoticz v3.7153

User avatar
gizmocuz
Posts: 8119
Joined: Thursday 11 July 2013 18:59
Target OS: Raspberry Pi
Domoticz version: beta
Location: Top of the world
Contact:

Re: Weird values because of daylight saving time (summer/winter)

Postby gizmocuz » Tuesday 08 November 2016 20:39

We should store everything in UTC, and then you got 1 standard
Quality outlives Quantity!

gordonb3
Posts: 383
Joined: Friday 22 January 2016 11:15
Target OS: Linux
Domoticz version: custom
Location: The Netherlands
Contact:

Re: Weird values because of daylight saving time (summer/winter)

Postby gordonb3 » Tuesday 08 November 2016 21:30

Okay. Here's your work plan:

  1. rename all tables that have a datetime field with property default=datetime('now'','localtime')
  2. recreate all renamed tables with the UTC default property
  3. copy all data with date convert from the original tables to the new tables
  4. destroy the original tables
  5. change all read references to datetime values in the application to datetime(Date,'localtime') (except in the order by clause)
  6. change all write references to datetime values in the application to datetime(Date,'utc')
  7. pray

Good luck.

PS Do note that this work plan does not change any code behaviour, except for the sort order when querying the shortlog tables. If you followed the steps correctly, which is probably a lot less simple than it seems and why there is a step 7.
Excito B3 running Gentoo Linux, P1, Rfxtrx433 to read and control TFA, KaKu, EvoHome RFG100
Custom patched Domoticz v3.7153

stlaha2007
Posts: 415
Joined: Monday 05 October 2015 10:16
Target OS: -
Domoticz version:
Contact:

Re: RE: Re: Weird values because of daylight saving time (summer/winter)

Postby stlaha2007 » Tuesday 08 November 2016 23:48

gordonb3 wrote:Okay. Here's your work plan:

  1. rename all tables that have a datetime field with property default=datetime('now'','localtime')
  2. recreate all renamed tables with the UTC default property
  3. copy all data with date convert from the original tables to the new tables
  4. destroy the original tables
  5. change all read references to datetime values in the application to datetime(Date,'localtime') (except in the order by clause)
  6. change all write references to datetime values in the application to datetime(Date,'utc')
  7. pray

Good luck.

PS Do note that this work plan does not change any code behaviour, except for the sort order when querying the shortlog tables. If you followed the steps correctly, which is probably a lot less simple than it seems and why there is a step 7.

I think this looks like how it has to be done. I haven't build these db-tables, have done some webdev with db's before.

Made some mistakes back then. Thanks to backup/restore and not having the availability of sites like github, handmade and versioncontrol within Dreamweaver. And before that TurboPascal ;-)

So: Yes it has some/lots of work, i won't say it would be easy.

But hey: It has to be solved, lets go :-)

gordonb3
Posts: 383
Joined: Friday 22 January 2016 11:15
Target OS: Linux
Domoticz version: custom
Location: The Netherlands
Contact:

Re: Weird values because of daylight saving time (summer/winter)

Postby gordonb3 » Wednesday 09 November 2016 8:40

stlaha2007 wrote:But hey: It has to be solved, lets go :-)

Care to see the work plan for the easy "solution"?

  1. Alter table [shortlog] add field isdst(byte)
  2. Update table [shortlog] set isdst=(current DST value)
  3. change order by in shortlog queries to 'Date ASC, isdst (DESC if currect DST = 0 | ASC if current DST = 1)'
  4. change inserts to shortlog to include current DST value
Excito B3 running Gentoo Linux, P1, Rfxtrx433 to read and control TFA, KaKu, EvoHome RFG100
Custom patched Domoticz v3.7153

stlaha2007
Posts: 415
Joined: Monday 05 October 2015 10:16
Target OS: -
Domoticz version:
Contact:

Re: RE: Re: Weird values because of daylight saving time (summer/winter)

Postby stlaha2007 » Wednesday 09 November 2016 8:58

gordonb3 wrote:
stlaha2007 wrote:But hey: It has to be solved, lets go :-)

Care to see the work plan for the easy "solution"?

  1. Alter table [shortlog] add field isdst(byte)
  2. Update table [shortlog] set isdst=(current DST value)
  3. change order by in shortlog queries to 'Date ASC, isdst (DESC if currect DST = 0 | ASC if current DST = 1)'
  4. change inserts to shortlog to include current DST value

I do care to see, and the greater part i understand. As in updating table to include dst active/not active and sorting it based on time with/without dst active state.

Short of saying: fixed the quick and 'dirty' way.

Most coding is done by specialists, i admire that. Thats why i do mostly scriptcoding and contact the specialist for those harder parts.

gordonb3
Posts: 383
Joined: Friday 22 January 2016 11:15
Target OS: Linux
Domoticz version: custom
Location: The Netherlands
Contact:

Re: Weird values because of daylight saving time (summer/winter)

Postby gordonb3 » Wednesday 09 November 2016 18:38

Nothing quick and dirty about this actual fix for the DST issue.

Still a lot of source files with references to time functions to plough through .
Excito B3 running Gentoo Linux, P1, Rfxtrx433 to read and control TFA, KaKu, EvoHome RFG100
Custom patched Domoticz v3.7153

stlaha2007
Posts: 415
Joined: Monday 05 October 2015 10:16
Target OS: -
Domoticz version:
Contact:

Re: RE: Re: Weird values because of daylight saving time (summer/winter)

Postby stlaha2007 » Thursday 10 November 2016 0:12

gordonb3 wrote:Nothing quick and dirty about this actual fix for the DST issue.

Still a lot of source files with references to time functions to plough through .

I do know that... Keep up the good work !!!

OT: Gonna focus on the OTWG-kit. Soldered and hooked up tonight... now implementing the devices and schedules...

I'm just gonna ignore the timeshifts for now, hope its implemented in the next stable before next DST ;-)

User avatar
EddyG
Posts: 30
Joined: Monday 02 November 2015 6:54
Target OS: Raspberry Pi
Domoticz version: 3.5877
Location: Netherlands
Contact:

Re: Weird values because of daylight saving time (summer/winter)

Postby EddyG » Friday 31 March 2017 9:02

Anything new on this item?
Last weekend still some weird values.
Regards,
Eddy

gordonb3
Posts: 383
Joined: Friday 22 January 2016 11:15
Target OS: Linux
Domoticz version: custom
Location: The Netherlands
Contact:

Re: Weird values because of daylight saving time (summer/winter)

Postby gordonb3 » Friday 31 March 2017 10:18

Since the clock moved forward, I don't think there should be strange values. In any case, if that version in your profile is accurate you would not benefit from any of the changes made since.
Excito B3 running Gentoo Linux, P1, Rfxtrx433 to read and control TFA, KaKu, EvoHome RFG100
Custom patched Domoticz v3.7153

stlaha2007
Posts: 415
Joined: Monday 05 October 2015 10:16
Target OS: -
Domoticz version:
Contact:

Re: RE: Re: Weird values because of daylight saving time (summer/winter)

Postby stlaha2007 » Saturday 01 April 2017 8:27

EddyG wrote:Anything new on this item?
Last weekend still some weird values.

Which version are you using? Mine is on 'latest' stable 3.5877. And also have data moved ontop of saterday.

As i recall correctly, it is only fixed in beta after this stable.

User avatar
gizmocuz
Posts: 8119
Joined: Thursday 11 July 2013 18:59
Target OS: Raspberry Pi
Domoticz version: beta
Location: Top of the world
Contact:

Re: Weird values because of daylight saving time (summer/winter)

Postby gizmocuz » Saturday 01 April 2017 9:15

Its correct in the beta version
Quality outlives Quantity!


Return to “Bugs and Problems”

Who is online

Users browsing this forum: No registered users and 8 guests