How to sort dataview queries on inline-fields: a mix of dates (some year only, some year-month only)?

Maybe there’s any easy way to do what I’m looking for, but at present my dates in the user-defined inline-fields aren’t sorting as I expect.

I have in document A:

summary:: Great Flood of 1927 begins

and in B:

summary:: Gubernatorial Election overshadowed by Senate race.

Sometimes I have year only. Sometimes I have year+ month. Sometimes I have a day of the month, also. When I sort by date, my query returns:

Start Date — Label and Summary
May 01, 1926 — Great Flood of 1927 begins
1907 — Gubernatorial Election overshadowed by Senate race.
1927 — Incumbent was defeated in gubernatorial race

I tried adding zeros, but those get put at the very bottom.

Start Date — Label and Summary
May 01, 1926 — Great Flood of 1927 begins
1907 — Gubernatorial Election overshadowed by Senate race.
1927 – Incumbent was defeated in gubernatorial race
1882-00-00 – Election results were questioned.

Is there a way, without setting up intermediate variables, to get:

May 1, 1926…

For dataview to consider it a date, it needs to be at least year-month, YYYY-MM, see Data Types - Dataview

Only a year is not considered a date, and as such can’t be sorted (or handled) as a date. You could enforce everything to become strings, and then sorting would be correct, but then you would need to convert back to date every now and then if that would be necessary.

Thanks holroy – good to know what is possible and what isn’t. I will start writing something custom for my purpose. If it’s useful, I will post here.

I made a test file with some dates, which might help you on your way:

- 2023
- 2023-05-03
- 2021-07

TABLE WITHOUT ID start-date, sDate
FLATTEN dates as start-date
FLATTEN choice(start-date.week, 
              date(start-date + "-01-01")) as sDate
SORT sDate desc

First of all, in your script you’d ignore the FLATTEN dates and the WHERE ..., as those are just there to keep my test in one file.

This query outputs the following:

In the first column we the actual value of the start-date, and we see that if it’s a date in the format YYYY-MM like for 2021-07, it actually shows the first of that month. For the year, it does just produce the year, indicating it doesn’t see that as a date.

In the second column, I’ve done some magic. If the field is a date, it got fields within the object, like the the .week, which is a truthy value. If it’s not a date, just a string, then that would be a falsy value.

Utilising this fact, I’m making a choice depending on whether the .week part is there or not. So if the .week part is present, aka a truthy value, we simply use the date as is. However, if the .week part is not there, we assume the value is just a year, and try to generate a date from it. More exact we generate the 1st of january of that year. This date is now stored in sDate.

And now your script can intermingle which value it wants to use. Either the start-date as the ‘original’ value for display purposes, or the sDate as a computed value useful for sorting (and similar), as seen in the query result.

Hope this gives you some ideas on your quest!

Thanks very much holroy!

My dataviewjs version is so untidy and verbose and clunky I’m embarrassed to put it here. But nonetheless, here it is, in case helpful to somebody else. I am new to dataviewjs and javascript, so I have to make everything in really little steps.

var tempStartDateString= item["start-date"];
// start-date comes from my note text, for example: start-date::1904 

tempStartDateString = String(tempStartDateString)
// not sure why I'm making sure it's a string...

var [a,b,c] = []  // preparing an array...
[a,b,c] = tempStartDateString.split("-")
// hold either 1904 or 1904-05 or 1904-06-29 
if (typeof(b) !== 'string') { b="01"; } 
// if there was no month given, set month to be 01

if (typeof(c) !== 'string') { c="01"; } 
// if there is no day-number, set day to be 01
var constructedDate = a + "-" +  b + "-" +  c ; 
item.sortString = String(constructedDate)  );
// then when I run my query I sort by sortString thusly
//       .sort(k => k["sortString"], 'desc')
//but not display sortString. 

I will review yours and see what I can apply to my situation to make the code trimmer and easier to update. I tried writing a function to test if something was undefined or null, but somehow I couldn’t get it to work (on testing whether b and c existed). So my code reflects my confusions.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.