Error trying to upload sensor data due to datetime field

[RDmitchell]

Describe the bug I am getting an error when trying to upload sensor data

Expected Behavior I want to upload sensor data

Actual Behavior I have created a data logger and uploaded the meta data without incident However, when I add the actual sensor data by clicking "Upload Sensor Readings" I get this error

Steps to Reproduce Here are the files:

• Metadata sensor-metadata.xlsx
• Sensor readings -- I edited the headers to conform to the field names in the meta data Ehs-awair-001-Fixed.csv

Instance Information

Instance: dev1 SHA: Org: LBNL 404 PM Property ID 1311525

[RDmitchell]

@nllong / @haneslinger -- if you can look into this fairly soon that would be great, so that I can show how SEED works for real sensor data being collected for this school

[haneslinger]

@RDmitchell Working on this now

[RDmitchell]

@haneslinger -- if you figure out the problem, can you put this into Test rather than Closing it? Thx !

[haneslinger]

The first column must be named "timestamp" not "timestamps(LosAngelos)", also, the timestamps must be timezone aware. I am fixing the file now

[RDmitchell]

Perfect ! I suspected it was something about header formatting.

What does "timezone aware" mean? If the sensors don't actually report the data in that form, can SEED somehow not require that? It would be a drag to have to edit the data files by hand after the fact to get them into SEED.

[haneslinger]

Ehs-awair-001-timezoned.csv

datetimes must be in isoformat and timezone aware. Here's the wiki page on iso format.

@nllong, should we be using the configured timezone in seed?

[RDmitchell]

@nllong -- what if the sensors don't report the timestamp information in that format? The sensors from the first BIE school we have do not have their timestamp in that format and I wouldn't want to ask them to somehow translate every data file into that.

Seems like we might need to be more flexible.

???

[nllong]

We do need to handle different time stamps. Is should be relatively easy to update the timestamps in Excel. We need to do this otherwise if there are multiple buildings across multiple timezones, then the data will be correct, otherwise, all the data gets cast to Pacific Time.

[RDmitchell]

@nllong -- we can't ask the SEED users to reformat every data file that they get from all their sensors. Just not reasonable. Can we somehow let the user specify the time zone some other way, in meta data or ???

[RDmitchell]

@nllong -- It seems like SEED could get the location from the Zip Code field (which should be there from ESPM), make an assumption about Time Zone (displayed and editable so the user can edit it if need be), then use a standard library function to turn the existing timestamp format, whatever it is, into the Time Zone aware format behind the scenes.

The user definitely shouldn't have to edit the gazillion sensor files that they are going to be collecting -- this is what computers are for !!

[RDmitchell]

@nllong -- in the meantime, how does one make the translation in Excel? So that I can tell Rengie when I talk to her in an hour

[haneslinger]

idk, but here's what a quick google gave me.

https://stackoverflow.com/questions/27388761/how-to-convert-a-date-in-excel-to-iso-8601-format https://599cd.com/blog/display-article.asp?ID=2202

[RDmitchell]

The example file I gave you is from some indoor sensors, but here is a data file from (I think, I will verify this afternoon when talking to Rengie) a Purple Air outdoor sensor, and that timestamp is in UTC

So we're going to need to handle these different formats for date and time behind the scenes.

[RDmitchell]

Also, the original data sensor file had field names that didn't match the meta data definitions, so I changed them.

But what I want to do is make a new Meta Data file and define the field names as they are in the actual sensor data file, shown below.

So one question is whether SEED will be able to handle this field name: temp(°C)

[RDmitchell]

Just talked to Rengie, and here is our plan

• There are two different types of data loggers, one set for indoor and one set for outdoor (the Purple Air ones)
  • She is going to define two meta data files that have the exact headers in the data that those data loggers generate
• For now, if you can edit those data files (11 indoor, 2 outdoor) to fix the timezone format, then we can get them imported into our test account
• Then we will have a nice set of data for this school that we can show to the Bureau of Indian Education.

How does that sound? So not dealing with the timezone issue right now in SEED, as long as you can make the translation for the files that we have.

Also, we will be getting data for 2 more schools, one in NM and one in AZ. We will need to do the timezone translation for those also, but we don't have that data yet, so we will start with this Jemez school first.

Is it going to be possible for you to fix the timezones for those 13 files? I will share a gdrive folder with those files to be fixed.

Thanks !

[RDmitchell]

Here is a link to a gdrive folder called "Data_2022-04-11-TimeZoneFixed" with the 13 sensor data files. If you could translate the Timestamp field to have the correct format, then I will import those into the BIE SEED organization ... Thanks !!

https://drive.google.com/drive/folders/1luvYOD7oRQ-yTw9SrgIZXx1mH6_kGWHK?usp=sharing

[haneslinger]

@RDmitchell I can edit those files. are the "Jemez Day School" also in LA time?

[RDmitchell]

Actually, I believe those should be Mountain Time (!)

Let me check with Rengie before you do that.

[RDmitchell]

@haneslinger / @nllong -- Rengie said we should translate the timestamp info from Pacific (LA) to Mountain time when making the timezone aware change.

Then the next question is how to handle daylight savings time vs standard time. Sigh ...

Is there a standardized way to handle that? Maybe it is always in standard time and whoever uses the data has to make the translation to daylight savings time?

But it does seem that SEED should be displaying the time that the data was collected, so for April data in this case it would be Mountain Daylight Time. But maybe it is stored as standard time (???)

[haneslinger]

Rengie said we should translate the timestamp info from Pacific (LA) to Mountain time when making the timezone aware change.

Just to be clear: You are telling me that they are currently in LA time, but you want me to change it to Mountain time?

Then the next question is how to handle daylight savings time vs standard time. Sigh ...

I think daylight savings is handled by the time zone change, right?

But it does seem that SEED should be displaying the time that the data was collected, so for April data in this case it would be Mountain Daylight Time. But maybe it is stored as standard time (???)

I don't think I understand the question.

[RDmitchell]

It appears that the data is currently in LA (Pacific) time and it should be Mountain time (because the sensors are located in New Mexico, which is on Mountain Time)

I don't know about daylight savings time. That might be a function of how the sensor is set up. For now let's not worry about it and just do the translation from Pacific to Mountain time without any other manipulation

Ignore my last sentence, if we are going to just do a direct translation by time zone and not deal with daylight savings time. We can figure that out later if we determine whether the sensor is dealing with daylight savings time or not.

[haneslinger]

@RDmitchell I've uploaded new version of each file to google drive. the "EHs" ones I treats as if they were in Pacfic time and I moved them to MT. The "Jemez Day" where UCT time zoned, I moved those to MT as well.

[RDmitchell]

@haneslinger -- thank you !!!