Community
OpenEnergyMonitor

Community

Timestamp Issue when Importing CSV Exported data from emoncms.org FEEDS list


(Michael John Rutland) #1

The following data sample is taken from the Excel file created on 26th June after carrying out
Node:IotaWatt: Solar Used kWh CSV export
200756_26-06.csv (192 Bytes)
However, when the process is repeated on 9th July the Excel file data shown below is provided
200756_Bit1.csv (192 Bytes)
I will therefore be grateful if someone would explain what is causing the start and end timestamp of 00:00:00 on both requested export csv files to change to 23:00:00 on the resulting later/second export file.


(Robert Wall) #2

I’m not an emonCMS expert, it probably needs somebody like @TrystanLea to help you.

But if this relates to data that was created by an Iotawatt, then you should probably ask at The IoTaWatt forum That is specifically about the operation and use of IoTaWatt. That is managed by Bob Lemaire, its creator, and he and the other users there are more likely to have the specific knowledge to help you.


(Trystan Lea) #3

Hello @OddJob, is there any chance that you changed your timezone between both exports? Testing here its returning midnight for me if I try a daily export.


(Michael John Rutland) #4

Thank you Robert and Trystan for your respective suggestions of avenues to explore - I offer the following by way of feedback.

The only information held in emoncms.org relates to data from an IoTaWatt, and this amounts to many months continuous and successful recording. During this time the IoTaWatt has received Major updates only, coped with a few power outages and also a low voltage (224.2V) period on the 13th of April lasting about 1.5 hours.

I have not knowingly changed the time zone or anything else for that matter.

My last series of successful of many CSV file exports from the FEEDS LIST was made on 26/06/2018 when the timestamp of the requested export and the actual data exported matched, with both showing 00:00:00. I still have copies of theses downloaded files.

However, all my attempts to carry out the same operation since the 6th of July has resulted in a timestamp mismatch. As normal, the Export request is set up with the timestamp showing 00:00:00. However, the exported data has a timestamp of 23:00:00! This mismatch situation is the same regardless of whether I repeat an export for an earlier data batch or some new i.e. recent emoncms.org held information. It is noted that the exported kWh data associated with the 23:00:00 timestamp is not the same as the earlier kWh data download when the timestamp remains at 00:00:00.

The odd thing is, if I carry out a requested export with the timestamp set at 01:00:00 for emoncms.org held data prior to 26th June, the resulting exported timestamp is 00:00:00 and the exported kWh values are the same as the earlier kWh exported figures! The 01:00:00 altered timestamp also works for the latest held emoncms.org data.

I appreciate that my engineers logic may be wrong, but it seems to me that something has changed at some point after the 26th of June and before the 6th of July. I would therefore appreciate any support from Trystan to help diagnose and solve this issue.


(Robert Wall) #5

Putting those together, my engineer’s logic firmly points the accusing finger at Timezones / Daylight Saving Time. Has there been an update and it’s not now applied, or applied twice? Or confusion between whether UTC and local time is being used? It certainly smells of something like that.

Does the Ioatwatt timestamp the data before transmission, or is that done as normal on receipt in emonCMS?


(Michael John Rutland) #6

The last IoTaWatt Major update occurred in early March. In case it may be useful, I offer the following information which is taken from the IoTaWatt message log

  • Real Time Clock is running
  • Local time zone: 0
  • timeSync: service started
  • timeSync: adjusting RTC by -2 (RTC is checked continuously throughout the log).

I’m afraid I do not know the answer. However, if someone is prepared to carefully guide me to an appropriate information source, I will willingly make it available.

The only emonCMS change that I can think of that has occurred after the 26th of June and before the 6th of July is the introduction of ‘Live Billing.’ But how and why the addition could possibly be connected to this problem is well beyond me.


(Trystan Lea) #7

Hello @OddJob, could you try setting your timezone setting in Setup > MyAccount to your relevant timezone? Where are you based?


(Michael John Rutland) #8

I am located within the UK. The current timezone setting shown in 'My Profile 'is UTC.


(Inactive) #9

IoTaWatt timestamps every data packet with UTC time. The internal clock is checked and corrected hourly using pool.ntp.org. While there is a local time setting in the configuration, it is not used to adjust timestamps to Emoncms or influxDB.


(Brian Orpin) #10

Would this time data not be better as an integer UNIX timestamp (to avoid confusion) or at least a recognised format that includes the UTC timezone? Without that, other systems will probably interpret it as local time.


(Trystan Lea) #11

Hello @OddJob, If you change your timezone in Setup > MyAccount to Europe/London that should sort it.

image

PS: I removed your postcode from your post.


(Michael John Rutland) #12

'Afternoon Trystan,

After changing the timezone to Europe/London I have exported CSV files for dates prior and post 26th June with the correct timestamp - thank you.
That said, I would like to offer the following observation and subsequent query.
Until today, MyAccount timezone has been set to UTC ever since the emonCMS account was set up at the beginning of November 17 (according to the screen dump I took at the time). Furthermore, this UTC setting did not affect the ability to export data from the FEEDS LIST after the change from GMT to BST.
So, with the timezone now set to Europe/London, will future Daylight saving changes mean that MyAccount timezone needs to be amended?


(Inactive) #13

Hi Brian,
From my reading of the input API documentation, myI take is that the only form of timestamp is UNIXtime. My understanding is that Unixtime is UTC by definition. So I think it is possibly redundant for me to have said that IoTaWatt timestamps with UTC, but the discussion here had touched on timezone as a possible cause of error, so belt and suspenders.

I’m not following your argument about other systems misinterpreting a protocol specific to Emoncms. Could you suggest a scenario where that would be of concern so I can better understand your critique?

,


(Trystan Lea) #14

Sounds like a simple misunderstanding on terminology UTC time = UNIX time, I helped implement the encrypted API for IotaWatt (the emoncms part) so can confirm that the timestamps are UNIX time which are UTC :slight_smile:

The timezone setting takes into account daylight saving and will work as expected, no further ammendments needed.


(Inactive) #15

I suppose for all intents and purposes that’s a fair representation. UTC is a time standard, UNIXtime is an epoch.


(Michael John Rutland) #16

Thank you once again.


(Brian Orpin) #17

@overeasy UNIX time and UTC are synonymous in that UTC is a date time string that has a zero timezone offset. UNIX time is seconds since the epoch (at GMT) and has no timezone element to it. Date and time expressed as a string without a timezone is ambiguous. You might intend it to be a UNIX timestamp, but it could be interpreted as local time. Using a format such as ISO 8601 makes it clear.

I’m also slightly confused as the input API only (currently) takes an integer yet the data output was a date time string. The fact changing the timezone solved the issue indicates there is something not quite right here. How is the output generated?


(Inactive) #18

I think you may have that backwards Brian. Timezones have an offset from UTC. UTC is the standard. Don’t even get me started on sidereal time::grin::

dunno, I think the question was why the same data had a different timestamp when retrieved at a later date. It’s all within Emoncms. As I get it the original data was posted to Emoncms with an IoTaWatt. When subsequently retrieved it had a correct timestamp. Later, when the same data was retrieved again, it had a different timestamp. The “date-time” strings come from the CSV generation within Emoncms. Maybe you should suggest that be made unambiguous.


(Brian Orpin) #19

Yes but what I said was that UTC has a zero offset as in 2018-07-17T08:06:21+00:00 in ISO 8601. The +00:00 is important as it makes it unambiguous.

Ah OK, that was what I wondered. @TrystanLea could you point me to where this data output is generated please? It would be better generated as a recognised format so this issue should not happen again.


(Trystan Lea) #21

We are going offtopic on this original support question thread by @OddJob :slight_smile: I have opend this as a github issue on the emoncms graph module https://github.com/emoncms/graph/issues/23 if it needs discussing further.