Journey Times error

[HughCraig]

I uploaded the attached CSV Rome Sydney Rome v5 all 2022 all December.csv to GHAP1 and got a correct Journey Routes view https://www.tlcmap.org/view/journey.html?line=route&load=https://www.tlcmap.org/ghap/publicdatasets/378/json but an erroneous Journey Times view https://www.tlcmap.org/view/journey.html?line=time&load=https://www.tlcmap.org/ghap/publicdatasets/378/json (missing segment Bondi Beach to Sydney Harbour Bridge). Journey Times for all different whole years worked as expected; Journey Times with dates with all different months within a year worked as expected; but Journey Times with a mixture of December dates and others, like Journey Times with all December dates (example above) gave unexpected results. I surmise that there is unexpected behaviour in Journey Times with within-December dates.

[BillPascoe]

This narrows a bug down very well. There is a similar problem with all the Explorer journeys being set up by JCU. I thought I logged it but I mustn't have. I think I have narrowed down the root of the problem. The viewer modifies the json URL by appending ?line=time to the URL, like this: https://www.tlcmap.org/ghap/publicdatasets/378/json?line=time and adds that as another layer in the ArcGIS JS code. When that URL Is called, the GHAP php code queries the database to get the dates and coordinates and sorts the results at that time. The problem is most likely in the sorting before it outputs the JSON. In the DatasetController.php file, around the following line is probably where to fix it: $dataitems = $dataitems->sortBy('datestart')->values()->all(); For some reason, the sort is not in the correct order. It doesn't look like a problem with US formatting of dates, because in this case all the days are before 12th (which is what usually gets mixed up with months). It is probably to do with no zeros in the dd part, which makes '10' come before '4' because it is sorting alphabetically, not numeric. If so, how to fix? We should allow up load of single digit days to make it convenient for user. Option 1: at the time of input into the database we correct them. Some are already in there now, so we'd have to do a bulk modify of existing db entries. Option 2: whenever dates are output, or sorted, we handle yyyy-m-d formatted dates and transform to yyyy-mm-dd. For a quick fix now, we can just loop through these dataitems, modify d to be dd and m to be mm, and re-sort them and carry on. Please make this fix in production too, as there are now a lot of Journey Time layers.

[yangli0516]

It looks like the datestart and dateend columns are in text type in the database, which causes the results are sorted by text instead of the date value. It would be idea these are stored as datetime.

[yangli0516]

Have added the accessor and mutator on the dataitem model to normalise the date string when it's in the format of yyyy-mm-dd, which seems solve the issues mentioned above.

[IanMcCrabb]

Hugh, if you might test and close if OK.

[BillPascoe]

FYI, the dates need to be stored as text instead of date time to ensure we can handle any date, without the restrictions of software, which usually have problems like years must be 4 digits only, cannot be negative, cannot be before some arbitrary date like 1900, cannot be before unix epoch etc etc. For this reason we need to store dates as text and handle transformations to datetype etc, in app. It is important to humanities projects to allow any date, so in this case we can't go 'Oh well, at least we can handle most dates, and never mind about the others.' Eg: for Australian culture it is essential to be able to show Aboriginal presence 60,000 BC.

[HughCraig]

OK, it sounds as though this is working at the moment, we may not have the ultimate solution yet.

[BillPascoe]

Pretty sure this was fixed.