Hello,

Thank you for reaching out to us!

Is the file you sent the same as the file from the screenshot? I just tried to reproduce the problem by

1. Changed my local timezone to match yours (CET)
2. Created a date based study
3. Change look and feel settings to include z (from the screenshot)
4. Create a dataset using that xls file
5. Data uploaded successfully

Let me know what you think, and please check if its the same.

Thanks,
Chet

Bonjour Chet,
Thx a lot for your time. I've no doubt that imports go well for you as it was with David's demos with the same files. The question is why it doesn't work for us?!
I tried again and again with other files and encountered the same exception this time with a ViralLoadPCR from David's demo files (attached).
I wonder what is exactly the meaning of this Labkey msg: "should be of type timestamp" when the field is already of the type datetime. Is this referring to the system date instead ? I beleive something involving the date in our system is set in a way different than in yours that doesn't match what is expected by Labkey and generates this timestamp and CEST exception. It seems that CEST is not interferring in your Labkey date while it does in our's. But I don't see where I can replace CEST to CET else than what I've already done? Maybe a screenshot of your setting to CET for comparaison could help?

Hello,

I believe that message is saying that whatever labkey received couldn't be parsed as a timestamp. We did some more digging on this yesterday and found that "European time zone codes "CET", "(CET)" and "(Europe/Paris)" all work fine - but the ones including the S or the date, like CEST, CEST 2021, (CEST) etc. are not being recognized." We have opened up a bug for this, which you can follow at https://www.labkey.org/home/Developer/issues/issues-details.view?issueId=41109

I am still working on a workaround for this to get you unblocked. I will let you know what I find.

Thanks,
Chet

Hello,

Unfortunately, I wasn't able to work out a straightforward workaround for this.

We do have a fix for this that will be available in a newer version of labkey. Originally we thought that the timezone was being specified in the file you were uploading, but after taking a closer look it's not there. Regardless, CEST (which java doesn't like) is getting appended to the data at some point causing labkey give you an error. Would it be possible for you to add "CET" for the timezone in your data?

Thanks,
Chet

Merci for your time Chet. As for your question suggestion :"to add "CET" for the timezone in your data", I'm not sure I understand exactly what you want me to do. I must say I've tried everything possible: even rewritten manually the dates in any possible format, American or French. The problème is Java overwrites the system date (CEST), whatever the files date format or the Study folder date format be. I just even tried to add a vba Module to a rejected excel file to force the CET time instead of the system CEST. Please see the Notepad file attached. No change in the outcome. Still CEST timezone is read. Same error msg with our own research data or Labkey demo data.
I should add that in my Study configurations I always change the CEST default time to CET, still the error CEST msg appears when submit to the import to the database !

The odd thing is that this Java behaviour with european time zone is not the same from file to file! Demographics.xls file with an identical Date field format is imported to Labkey server while the same structured file LabResults.xls produces the CEST error msg! Maybe in the first, the system is not parsing on date while in the second it does.
I'm thinking of abandoning Study feature and try other type of project types like Lists, but it's a pitty as we lose all the UI and prebuilt features for Study type (clinical research, cohorts, ...).
Would you please let me know if there's another French or Center Europe Labkey customer? I can maybe try to see how they deal with this specific problem.
Hopefuly this bug will be fixed in the coming version.
Panthéa

Hello Panthéa,

I apologize for the delayed response.

Hello Panthéa,

I wanted you to add "CET" as the timezone in your data. My hope there was that the system would just opt to use the timezone specified by the input file. But since it seems to use "CEST" regardless I don't think this will help. We actually opened up a bug for this which you can follow here https://www.labkey.org/home/Developer/issues/issues-details.view?issueId=41109.

After discussing this with Molly who spent some time on this issue, we think that including support for “CEST” still may not resolve the issue. We think that it may have more to do with how the date is being parsed. By that I mean whether your system is set to use “U.S date parsing” or “Non-US date parsing”. You can find this setting in Labkey under the Admin console → Look and feel settings. Our theory is that perhaps the parsing setting is not set appropriately for the data in the file. For example, if you are testing with files from our tutorials that assume US date parsing while you have Non US parsing set you will likely run into issues. To test this could you change your “Look and Feel” settings to use US based parsing and try out the tutorials files again? Be sure to change the setting back when you are done testing.

The reason you are seeing differences between the demographics file versus the LabResults is because the demographics file only contains one date 02/02/2020 which is the same if you were to parse it using US settings or even European settings. The LabResults file contains a range of future dates that may not be able to be parsed either way like the date in the demographics file.

Another strategy is to eliminate the complications of using the Excel/.xls file formatting - Excel may be displaying dates one way but the underlying date code might still be wrong. To eliminate this, you could use text files instead. For example, the attached .csv file contains the same data as the LabResults file attached to our tutorial, but formatted as comma separated values, essentially forcing 'year-first' date codes (without timezones) which will always be accepted. Could you try this file to see if it gets parsed appropriately?