Archived Forum

This turned out to be really simple :)

Couple of questions, I am sure I have read somewhere that there is a limit to the number of data points returned.  Have I remembered that correctly, and if so, how do you know how to get the maximum if you are requesting it by UNIX Timestamp (start and end with unknown gap between data points) and then how to request the next batch?

The other query is that, if I do a basic API call to Timestore, I get a result [[1394459145000,10.984908]] which is time and temp (in this case).  However, Unix timestamp right now is only 10 digits and this is 13.  To convert this I need to remove the right 3 digits.  Is there a reason for this of have I missed something? Also, the precision of the temperature seems a bit off as well! :)

I'll post what I have done once I have tidied it up, but as a proof of concept it seems to work.

Can anyone shed light on the timestamp data format please?

unixtime in milliseconds. So 1394459145000 equates to Mon, 10 Mar 2014 13:45:45 GMT

To convert Unix time to excel time format use the following

=(Unixtime/86400)+Date(1970,1,1)

add 1/24 when bst

Ah Milliseconds - yes that explains it.

Ok so you want to export data from Timestore to excel? This does it the other way round and gets Excel to pull in the data. Just load these into a module in the VBA, edit as appropriate (you will need an APIkey at least), and off you go.

Remember the dates are in miliseconds in Timestore so the normal Unix Timestamp needs to be multiplied or divided by 1000. I had trouble with overflow so I trimmed the string!

Pretty quick and dirty with no error catching but proves the concept.

Sub Ge​tEmonCMSData()

    Dim URL As String

    'edit the URL to suit or add values from cells
    URL = "http://192.168.7.220/emoncms/feed/data.json?id=16&start=1394400000000&end=1394446158000&dp=1000&apikey="
    Dim TimestoreData As String
    Dim XMLHttpRequest As XMLHTTP

    Set XMLHttpRequest = New MSXML2.XMLHTTP

    XMLHttpRequest.Open "GET", URL, False
    XMLHttpRequest.send
    TimestoreData = XMLHttpRequest.responseText
   
    Call SplitData("\[(\d+)\,(\d+\.\d+)\]", TimestoreData)

    Set XMLHttpRequest = Nothing
End Sub

Sub SplitData(myPattern As String, myString As String)
   'Create objects.
   Dim objRegExp As RegExp
   Dim objMatch As Match
   Dim itemMatch As SubMatches
   Dim colMatches   As MatchCollection
  
   ' Create a regular expression object.
   Set objRegExp = New RegExp

   'Set the pattern by using the Pattern property.
   objRegExp.Pattern = myPattern

   ' Set Case Insensitivity.
   objRegExp.IgnoreCase = True

   'Set global applicability.
   objRegExp.Global = True

   'Test whether the String can be compared.
   If (objRegExp.Test(myString) = True) Then

        'Get the matches.
        Set colMatches = objRegExp.Execute(myString)   ' Execute search.
        Dim rowcnt
        rowcnt = 0
       
        For Each objMatch In colMatches   ' Iterate Matches collection and place in active sheet
            rowcnt = rowcnt + 1
            ActiveSheet.Cells(rowcnt, 1) = FromUnixTime(Int(Left(objMatch.SubMatches(0), 10)))
            ActiveSheet.Cells(rowcnt, 2) = objMatch.SubMatches(1)
        Next
   End If
End Sub

'http://tcsoftware.net/blog/2012/01/converting-to-and-from-vb6-date-to-unix-posix-time/
Function FromUnixTime(UnixTime As Long) As Date
    FromUnixTime = DateAdd("s", UnixTime, DateSerial(1970, 1, 1))
End Function

Function ToUnixTime(time As Date) As Long
ToUnixTime = DateDiff("s", DateSerial(1970, 1, 1), time)
End Function