Announcement

Collapse
No announcement yet.

Jon00 Database Charting Utility for Homeseer 3 & Homeseer 4

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    Is it possible to manipulate the time stamp when a value is written to the database? I would like to log the daily average temperature and electicrity usage just before midnight but set the timestamp to noon.

    This is because how the graphs are presented. If I log a value close to midnight it looks like it belongs to the day after.

    Does my question make sense? [emoji4]

    Comment


      No you can't.

      Have you played with the AxisXInterval & AxisXIntervalType setting (or on the Chart Style setup under advanced settings)?
      Jon

      Comment


        No, I haven't. Thanks! I will try it out and report back.

        Comment


          So I've tried a few different combinations of values for AxisXInterval & AxisXIntervalType but can't figure it out.

          If you look at the attached chart the recorded values looks like they belong to the day after as they are logged close to midnight. What should I try to get them closer to the "correct" date?
          Attached Files

          Comment


            I ran into the same issue a couple years back when I started doing the same thing, tracking daily power used for things like HVAC, pool, and total consumption, and also daily counters for various things (AC cycles, etc) My solution was to run a SQL script just before midnight 23:57 (after the day's data was inserted at 23:55), which updates the timestamp of the data from 23:55 to 00:00. Basically it trims off the time component from the datetimestamp, and it defaults back to 00:00 (first thing in the morning for that day). Been working flawlessly ever since.

            The script is very broad and updates ALL data in the table (DBtable3 in my case). You could add criteria to scale down the scope of the update, but in my case that table is only used for "daily totals data"

            Basically I execute this inside a script from an event:

            Imports System.Data.SQLite

            Public Sub Main(ByVal Parms As Object)

            Try
            Dim SQLconnect As New SQLiteConnection()
            Dim SQLcommand As SQLite.SQLiteCommand

            SQLconnect.ConnectionString = "Data Source=" & hs.GetAppPath & "\bin\Jon00DBChart\jon00dbcharting.db3;"
            SQLconnect.Open()
            SQLcommand = SQLconnect.CreateCommand
            SQLcommand.CommandText = "UPDATE DBtable3 SET DateAdded = date(DateAdded)"
            SQLcommand.ExecuteNonQuery()

            Catch Ex As Exception
            hs.writelog("Error",Ex.ToString)
            End Try

            End Sub

            Comment


              Many thanks, jhirschhorn. Exactly what was needed. The graph looks a lot nicer today.

              You can see the difference when you compare yesterday's record to the other ones. I will modify my events so the table is processed daily, just before the graph is produced.
              Attached Files

              Comment


                Not sure if I see the solution here.. I update my power consumption just before midnight as you guys do and like you I see the entry for yesterday's consumption (Sept 11) as Sept 12th. In the example shown, if I read this correctly, I see your previous day's consumption also showing as today's date.
                I too would like to see the charting until the end of the day and not the next.

                Do I understand this solution?

                Robert

                EDIT: Taking second look, I see that the data is stored in the database on the right day and when you select the V for the dynamic charting, that the date is correct. So it might be a .NET thing...
                Last edited by langenet; September 12, 2017, 10:02 AM.
                HS3PRO 3.0.0.500 as a Fire Daemon service, Windows 2016 Server Std Intel Core i5 PC HTPC Slim SFF 4GB, 120GB SSD drive, WLG800, RFXCom, TI103,NetCam, UltraNetcam3, BLBackup, CurrentCost 3P Rain8Net, MCsSprinker, HSTouch, Ademco Security plugin/AD2USB, JowiHue, various Oregon Scientific temp/humidity sensors, Z-Net, Zsmoke, Aeron Labs micro switches, Amazon Echo Dots, WS+, WD+ ... on and on.

                Comment


                  Originally posted by langenet View Post
                  Not sure if I see the solution here.. I update my power consumption just before midnight as you guys do and like you I see the entry for yesterday's consumption (Sept 11) as Sept 12th. In the example shown, if I read this correctly, I see your previous day's consumption also showing as today's date.
                  I too would like to see the charting until the end of the day and not the next.

                  Do I understand this solution?

                  Robert

                  EDIT: Taking second look, I see that the data is stored in the database on the right day and when you select the V for the dynamic charting, that the date is correct. So it might be a .NET thing...
                  When I log the consumption for the current day I want to do it as close to midnight as possible. If you look at my first post the 7,9 value looks like it's logged the 11th. It's actually logged at 23:59 the 10th and represent the consumption for the 10th.

                  With jhirschhorn trick the time is reset to 00:00 (for all records in the table). In the example above the logged value will get a time stamp of 00:00 on the 10th which make the graph look correct if you look at my second post.

                  Does this make sense?

                  Comment


                    Jon00,
                    For a very long time, every couple of days, the graphing program seems to just stop(my event does not update the graph). I'm on Win10 and using HS3 Standard Edition 3.0.0.318 (Windows) and the latest version of your program-1.1.3 (except for this problem, it has worked flawlessly for several years).

                    Normally a reboot of the PC is required to fix but recently I found if I shutdown your .exe(in Task Manager) and restart it that seems to fix.

                    How could I troubleshoot this?

                    BobSpen

                    Comment


                      Unless there are errors in the errorlog.txt file, this would be difficult to pinpoint. If I had a hunch, it would be HS3 losing connection with the plugin for some reason.
                      Jon

                      Comment


                        Well it is happening again, just noticed it this afternoon.
                        Error log shows alot of these.

                        2017-10-06 15:00:00.357 Error in DataDifferential Block 2: System.NullReferenceException: Object reference not set to an instance of an object.
                        at System.Runtime.Remoting.Messaging.LogicalCallContext.Propaga teIncomingHeadersToCallContext(IMessage msg)
                        at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(Mess ageData& msgData, Int32 type)
                        at HomeSeerAPI.IHSApplication.GetINISetting(String section, String key, String default_val, String FileName)
                        at Jon00DBChartingHS3.Form1.�啎㘃氷몢搎띞䘔()

                        Also just found out one of my graphs is really messed up. Here is a screen grab and a zip file with as much info as I thought you may need.

                        Thanks,
                        Tim
                        Attached Files
                        FB Page - https://www.facebook.com/pages/Capt-Tim/209398425902188

                        HSTouch Layouts - https://www.facebook.com/media/set/?...5902188&type=3

                        Comment


                          What version of HS3 are you running? Rich has been playing around with ini file handling so if you are running a beta, it must be on the latest version https://forums.homeseer.com/showpost...&postcount=164
                          Jon

                          Comment


                            No betas, still on 318.
                            Here is what I did later on.
                            Killed the graphing exe with task manager, manually restarted your exe, went to the Chart style setup, did the Manual Update to the lake stage graph.
                            Checked the graphs with the device manager page and all my graphs were blank.
                            Waited about 2 hours and went and checked them again and they started to fill out the data correctly.
                            Weird...
                            Checked this morning and they all look normal.

                            I am running 1.0.0.0
                            Downloaded again from your site last night and it says 1.0.0.0 also.

                            Thanks,
                            Tim
                            FB Page - https://www.facebook.com/pages/Capt-Tim/209398425902188

                            HSTouch Layouts - https://www.facebook.com/media/set/?...5902188&type=3

                            Comment


                              FYI, the version is displayed in the HS Log at startup, not the properties of the exe.
                              Jon

                              Comment


                                Jon,

                                Having the same problem as BobSpen. Have not found any log errors so as you say, hard to pinpoint. Process is still running in task manager but icon no longer in the tray and charts not updating.

                                My work around was to create a script that looks at the last change date of one of the charts and if it gets stale (more than 2hrs) then it sends me a text msg that DBCHARTS are not being updated. Then I go remove process from task manger and restart the process from the desktop. It could run another couple of days or another couple of weeks before it happens again. No particular pattern. One question I have is whether virus software (like Norton) could cause a problem. I know that several of your EXEs and dlls get quarantined due to "not being used by very many usres". I recover them and exclude them and seems fine for awhile. It almost always happens when I load a new version. But also sometimes out of the blue. This is not always the cause however.

                                Comment

                                Working...
                                X