Timestamp best use. PHPFINA vs PHPTIMESERIES?

Emoncms
1

Following on from the SBFspot script I was wondering how best to use the timestamp information.

I think this will be a key to understanding something in the bulk-upload API feature, to add a new function to the script to upload historic data, which could be useful.

From looking at the .php files it seems that if a timestamp is passed with the API request, it is passed along with the data into the database rather than the ‘time of arrival’ at emoncms.

This implies that PHPFINA cannot be used? Is it PHPTIMESERIES only for timestamped information?

06
061412×542 51.9 KB

2

Are you referring to the “TimeStamp” field IN the frame of data or the timestamp OF the frame of data?

Do you mean add a new function to your own SBFspot script to upload data? Or are you suggesting changing emoncms to use a “TimeStamp” data field as THE timestamp (or vice versa) ?

To upload historic data (in chronological order) should be as easy as using the original timestamp instead of a current one. emoncms will accept data up to 4years old, but low-write emoncms will only append new data, it will not overwrite or insert data before the last entry so there is no way to “backfill” data, but if you post in chronological order it should work just fine. Providing you use the correct bulk input api time arg.

There are several different methods, most will adjust the time stamp on arrival, to have the greatest chance of posting data and keepng the original timestamp you must use full unix timestamps in each frame of data in the bulk upload and include &time=0 in the URL. This will ensure the raw timestamps are passed without “deliberate adjustment” based on arrival time.

However, the raw timestamp will still get truncated to the nearest whole second and then if using phpfina there will be further changes to the timestamp as the data will be saved into a fixed interval timeslot, effectively rounding down the timestamp to the nearest whole interval since the feed start time.

Also note that emoncms phpfins and phptimeseries are both 32bit float datatypes, so they only retain precision to 7 digits (not the 10 used in timestamps) so each timestamp will get rounded to the nearest 15mins (I think it was 15mins or there abouts) so the only way to record datastamps is to either use mySQL or to introduce your own offset, ie use timestamps “seconds since 01/01/2000 00:00:00” for example and subtract/add 946684800 from/to unix timestamps.

I have previously suggested a fix to the phpfina engine that would allow storing of larger values so that only the range of the saved data is restricted to 32bits rather than the overall size of the value. It was primarily intended for storing large kWh meter readings without losing Wh resolution, but it would work equally well here as you could add a “feed start value” of (say) 946684800 and (only if set) the feed engine would subtract that value from any incoming new values and add it to any outgoing queried data, but @TrystanLea wasn’t so hot on the idea.

[EDIT] - Using the above “start value” method would indeed allow full unix timestamps to be stored, but only for around 16 weeks (9999999 seconds) then the resolution would start to be fudged, rounded to the nearest 10s for around the next 149 weeks. So useful for debugging and for short projects but not a long term solution for storing full unix timestamps over a long period.

3

The timestamp in the frame of data, as shown in the attached image.

I meant a new function to my SBFspot_to_emonCMS script.

Oh dear god.

What the actual.

This makes sense.

A remaining question if you don’t mind… If the source data is timestamped information at irregular intervals, can PHPFINA be used? Or should the timestamp be rounded to the interval rate set for the PHPFINA feed? How does PHPFINA handle a timestamp that’s out of range of it’s interval setting?

4

Answer found. PHPFINA just rounds everything to the interval it seems. I can’t find the list of values in the database so can’t confirm, but it appears to be the case from viewing the Graph module outputs.

5

I don’t understand the reference to timestamp and precision being a problem. In the mysql database the time value is stored at a int(10). So the timestamp example above would work precisely if it were included in a http request string?

6

I’ve found a clue as to how time information could be used, but it related to curl input/post in input_methods.php
I don’t know if this is relevant for http GET requests as I’m using in the SBFspot_to_emonCMS script.

Possibility: Change the script to use curl POST, add extra “timestamp” variable within an elseif at around lines 105 to 110.

  // If JSON, check to see if there is a time value else set to time now.
  // Time set as a parameter takes precedence.
                if ($param->exists('time')) {
                    $log->info("Time from parameter used");
                } elseif (array_key_exists('time',$jsondataLC)){
                    $inputtime = $jsondataLC['time'];
7

No, it effectively truncates (rounds down only) to the nearest multiple of fixed interval since the feed start time.

PHPFINA and PHPTIMESERIES data are not kept in the mysql database, there is a single ref to each feed in the feeds table, but the actual data is in a file in the location/path specified in the settings.php.

Be VERY wary of what you see in the graphs module (or via any feed api driven visualization) when looking at this level of detail. The timestamps you are looking at in the graph module are the feed datapoints found closest to each of the searched timestamps which are derived from the graph search parameters eg start time, end time, interval and the size of the graph, ie maximum points it can display. The timestamps shown are the search points not the found datapoints, they may or may not align, but you must know you are not looking at actual raw timestamps you are looking at the requested timestamps.

But the PHPFINA and PHPTIMESERIES are not MYSQL and the value is stored as a 32bit float.

No, it would appear to work because the machine and the emoncms php code work with 64bit float precision so on screen all looks great, but when the value is saved it is saved as a 32bit float and therefore the last 3 digits of the timestamp are lost. You won’t notice that until you view the data as the current feed value is held in redis and the last value displays fine on the inputs page, feed page and possibly even feedvalue widgets (I don’t recall for sure from when I tried all this) will all display the 64bit float held in redis. But when you view a graph there will be large flats and steps as every timestamp value for 1000 seconds will be the same, then step up 1000 in a single datapoint. If posting a unix timestamp at 10s intervals you will end up with 100 identical datapoints

timestamp value
1537607422 1537607000
1537607432 1537607000
1537607442 1537607000
1537607452 1537607000
1537607462 1537607000
1537607472 1537607000
1537607482 1537607000
1537607492 1537607000
1537607502 1537608000
1537607512 1537608000
1537607522 1537608000
1537607532 1537608000
1537607542 1537608000
1537607552 1537608000
1537607562 1537608000
1537607572 1537608000

If you use a MYSQL feed type (not PHPFINA or PHPTIMESERIES) that will faithfully store the timestamps and you will be able to see the data in the mysql tables.

8

Ah I understand now. Almost what I meant… Rounding down and truncates.

Oh course. Normally /var/lib/ for some reason… Although they’re not libraries.

I gathered this was the case… It looked too tidy!

It’s just the most recent values like you say… What type of database is it? “feed_7.MYD” i see in a PHPTIMESERIES folder for example.

Ok thanks for the clarity.

Interesting… I’ll have to experiment further.

9

A quick look at unix time suggests a 32bit unsigned int is all that is needed.
Is the problem because it’s a 32 bit float?

10

Yes, well actually the problem is because it’s a 32 bit float not because it’s specifically a float, it doesn’t need to be an int, a 64bit float would be fine too.

@TrystanLea has said he would like to see a 64bit phpfina introduced, but I don’t think that’s in the pipeline yet.

11

I see. I does seem like less of a jump to go to 32bit unsigned int rather than 64bit float. I don’t know how timestore works under the hood though and can’t really comment.

I see the benefits of 64bit values for large and precise numbers generally…

12

What confuses me is how PHPFINA can work at all… if an interval of 10s is used and the database rounds things to 16.6667 minutes how does it work?

Is there an offset value stored with each entry?

I’m still unsure as to the answer of my original answer, how best to use the timestamp data point available from the SMA inverters… I think I’ll have to have a play around at some point.

13

But it would have a very limited range of use since it cannot store decimal unless another “decimal places” meta field was created and it also won’t do negative numbers either.

I would agree that there is a need for “other datatype/size” phpfina/timeseries feeds, but if there is only another one or 2 as opposed to a whole range, then a 64bit version of what we already have has to the be favorite for all round ability.

I’m sorry I’m really not sure what you are asking there, but it sounds like you are confusing the actual timestamp and the value, which in this instance is also a timestamp value.

a phpfina feed file is a string of end to end 4byte values, each 4byte location is a datapoint. bytes 0-3 are the first datapoint (datapoint[0]) and 4-7 are the second (datapoint[1]) and so on.

datapoint[6] (for example) is the seventh datapoint and those 4bytes give you the value (32bit float)

the timestamp for datapoint[6] is 6 x the interval (from the feeds meta file) plus the feed start time (also held in the meta file)

so if the feed start time was 1537708275 and the interval was 10s, then datapoint[6] would be (6x10)+1537708275 = 1537708335.

The value however, (lets say for example you were posting data with a matching timestamp and value) would be something like 1537708000 due to the limited precision of the 32bit float, but it’s location in the file would tell you that values timestamp is 1537708335.

I can see no benefit to passing that timestamp to emoncms as a value for saving to a feed.

If you are getting data from an inverter and it is telling you this data applies to this timestamp, then any other timestamp is irrelevant and you should be using only that timestamp from the inverter as the actual timestamp for the data array being sent to emoncms.

If the inverter is saying this is the current data and also this is the current time, then the inverter timestamp has little or no value other than to check the time is right on the inverter.

If you believe that the sample period and data the inverter reports is not always current, then you might benefit from using the raw inverter timestamp, or potentially as a way to confirm the data coming from the inverter is current, if you are getting the same timestamp every 10s for an hour that would suggest your inverter is not updating the data, but currently you would need to store this as mysql, not as phpfina or phptimeseries.

Otherwise, if you are using phpfina you are much better off taking control of the timestamp and polling interval etc and dictating when the data is updated eg like my harmonized timestamps method, otherwise you are just polling the inverter and getting a timestamp over which you have no control and sending it to emoncms which will do what it can to place that data in the appropriate/closest time slot.

The only time I really see it is worth saving a timestamp is to test emoncms and/or other applications is to see if they are behaving or to test reporting accuracy etc ie if you send identical timestamp and values. are they coming out identical when graphing and downloading etc. It has no real world value to any energy data to have a timeseries database of timestamps. you are literally recording what time it was at the time it was recorded, that’s why it’s only real use is to check they are the same for debugging purposes.

14

I would say if you want to save the incoming data from the SMA inverter according to the timestamp the SMA inverter gives you then PHPTIMESERIES should be your engine of choice.

Alternatively if you’re reading current/ near realtime data from the SMA inverter and knowing the exact time of the reading matters less to you (e.g your happy with a potential timestamp error of the interval length) than disk space (PHPFina uses half the disk space of PHPTIMESERIES for a given interval - if the data is regular) then use PHPFINA. :slight_smile:

1 Like
15

@pb66

Indeed, the conversation has been confusing over the interpretation of ‘timestamp’. I posted the image of the data coming from the inverter and this is the timestamp I meant, however, we got there in the end and its been informative.

I now understand the value of a start time offset option as has been discussed here before.

I have the option of picking the timestamp from the sbfspot database and including it in the http request.

@TrystanLea
Yes I’m happy with phpfina :slight_smile:
As we can see, I’ve been curious out of interest about what to do with the timestamp value coming from the sma inverter.

A question about timeseries…
Does PHPTIMESERIES store the full timestamp, or again is it a calculated time value from a start time?
If I included the timestamp from the inverter in the http request formatted to be compatible with the input api timestamp formats, will this timestamp be stored in the phptimeseries db?

From the input api page.

Description: Set the input entry time manually
Method: GET
Example: https://emoncms.org/input/post?time=1537875164&node=1&csv=100,200,300

If I include the sma timestamp here am I limited to csv formats?

I can just go ahead and experiment in my own time so if you’re busy no problem.

@pb66 do you have any doco on your phpfina harmonised timestamps?

16

Yes, it stores the full timestamp, here’s the full description of how it works: https://learn.openenergymonitor.org/electricity-monitoring/timeseries/Variable-interval

Yes.

No, that will work with all input/post formats.

17

OK great thanks.
I’m guessing the api timestamp method is completely ignored for PHPFINA, even during feed creation?

18

I made this for fun.
PHPFINA.

PHPFINA-01
PHPFINA-011089×506 12.3 KB

19

Seeing as I made the previous before seeing the learn page, I thought I’d knock together this one too.
Use as desired if they’re helpful.

PHPTIMESERIES-01
PHPTIMESERIES-011089×506 11 KB

20

Nice diagrams @danbates, it would be great to add these to the learn pages!

1 Like