Timezone system field and importing data errors

[tylerstright]

During the process of building our Water Temperature dataset we decided it was best to include a field to capture Timezone. Since there was a pre-existing system field in CDMS, we decided to try to use it. I’ve been testing this, and I’m encountering some issues while importing. Here’s what I’ve found:

First, the values for Timezone are established in the config.js file. I think this might be the first indicator this may be an incomplete feature.

Second, I came to the conclusion the Timezone field was causing issues because when I removed those values from the import file, the import was successful (the field is not required).

ENTER DATA APPROACH When I go via the “Enter Data” route and create a single activity with one detail record, I am able to successfully include the Timezone. The log file shows the Timezone as what I think is a JSON object?…

and the INSERT statement to go with that….

2021-11-29 13:22:08.8846|DEBUG|services.Controllers.CDMSController|INSERT INTO Activities (LocationId, ActivityDate, DatasetId, UserId, SourceId, ActivityTypeId, CreateDate, InstrumentId, Timezone) VALUES(1599,'11/14/2021 7:00:00 AM',4378,3207,3,1,'11/29/2021 1:22:08 PM',73,'{"Id":0,"Name":"Pacific Standard Time (GMT-08:00)","Description":"(GMT-08:00) Pacific Standard Time (US & Canada)","TimezoneOffset":-28800000}');SELECT SCOPE_IDENTITY(); 2021-11-29 13:22:08.8956|DEBUG|services.Controllers.CDMSController|INSERT INTO ActivityQAs (ActivityId, QAStatusId, QAComments, EffDt, UserId) VALUES (64405,6,'','11/29/2021 1:22:08 PM',3207); 2021-11-29 13:22:08.9115|DEBUG|services.Controllers.CDMSController|INSERT INTO WaterTemperature_Header (ActivityId,ByUserId,EffDt,comments,downloaded_by,end_date,start_date,instantaneous_watertemp) VALUES (64405,3207,'11/29/2021 1:22:08 PM','Testing 1','Tyler','11/14/2021 7:00:00 AM','11/7/2021 6:00:00 AM',4.6)

This Enter Data approach successfully saves the activity.

IMPORT DATA APPROACH For the import data approach, I include all fields within the CSV file - Activity Date, Location, Instrument, Timezone, etc. Having all fields in the file essentially 'skips' the data entry page you can land on if you don't include all fields. I originally included the value “Pacific Standard Time (GMT-08:00)” as my Timezone thinking this would properly associate in CDMS. However, this is not the case. When I try to import a CSV where the Timezone = "Pacific Standard Time (GMT-08:00)", I get an error (no import) and the log file shows….

2021-11-29 13:28:46.4359|DEBUG|services.Controllers.CDMSController|INSERT INTO Activities (LocationId, ActivityDate, DatasetId, UserId, SourceId, ActivityTypeId, CreateDate, InstrumentId, Timezone) VALUES(1605,'6/14/2011 12:00:00 AM',4378,3207,3,1,'11/29/2021 1:28:46 PM',68,'');SELECT SCOPE_IDENTITY(); 2021-11-29 13:28:46.4359|DEBUG|services.Controllers.CDMSController|INSERT INTO ActivityQAs (ActivityId, QAStatusId, QAComments, EffDt, UserId) VALUES (64407,6,'','11/29/2021 1:28:46 PM',3207); 2021-11-29 13:28:46.4359|DEBUG|services.Controllers.CDMSController|INSERT INTO WaterTemperature_Header (ActivityId,ByUserId,EffDt,end_date,Timezone,start_date) VALUES (64407,3207,'11/29/2021 1:28:46 PM','6/14/2011 12:00:00 AM',Pacific Standard Time (GMT-08:00),'7/15/2009 12:00:00 AM')

Three things here:

1. When importing, CDMS is trying to capture Timezone in BOTH the Activities and Header tables. This is not the case when I used the “Enter Data” approach where it only captured Timezone in the activity (which should be correct for a system field).
2. Activities is not receiving the value for Timezone when attempting an import. It’s not associating ‘Pacific Standard Time (GMT-08:00)’ and is receiving an empty string.
3. WaterTemperature_Header Timezone is receiving the Timezone value I inserted, but not surrounded by single quotes which means the SQL statement shouldn’t work. Additionally this shouldn't live in the header table, as it's a system field. This didn't happen when going through "Enter Data"

[nowinski]

Thank you for reporting this issue and for the detailed test documentation. I think this is essentially the same issue we encountered with Instruments, which is that the multiple activity importer was designed to handle ActivityDate and Location fields and not other fields in the Activity table. This is probably because CTUIR's imports hobo logger files (i.e. single activities) and did not anticipate files containing data from multiple locations and/or loggers. CDMS redirects to the data entry page when importing a hobo logger file and Timezone saves correctly after selecting it from the dropdown list. If NPT needs to import water temp files containing data from multiple locations/loggers, it's definitely possible to add Timezone to the multiple activity importer. Happy to look into that or refer to CTUIR...

[tylerstright]

I think if a field is considered important enough to be a system field it should have the same functionality as the other system fields. Having a system field break the ability to import a single-activity file with all fields filled in seems problematic.

[tylerstright]

To counter my previous response, we have decided here at NPT there is not an urgent need for this to be fixed. This behavior is primarily an issue when importing legacy water temperature data into CDMS. The workflow moving forward should be single activity imports.

As such, this is a low priority, or perhaps no longer an issue at all.