Sorting pages chronologically in dataview with different kinds of time-sensitive data

What I’m trying to do

I have an apple collection and want to sort varieties in dataview from early to mid to late ripening varieties. Ripening is a chronological property so it should be possible to sort it in that way. The data I have on them are varied:

  • Some varieties I only know broadly if they are early, mid or late.
  • Some varieties I know roughly which month they ripen.
  • Some varieties I know whether they ripen at the beginning, middle or end of a particular month.

Ideally, I would like to be able to use the data I have in a way that dataview can still understand in a chronological way.

My priority with dataview queries and properties is to use as simple code as possible for two reasons: I’m not a programmer and I want my notes to stay as close as possible to being legible by humans.

Things I have tried

I searched the forum for keywords like ‘dataview months sorting’. I’ve considered to use a number format (1-12) to indicate months, but that then excludes the possibility of using more rough data like ‘early ripening’ (which can mean any variety that ripens in july or august) and also excludes the possibility of using more fine-grained data like ‘beginning of October’.

I’ve used text data before, but dataview doesn’t understand that chronologically. It doesn’t know that ‘early’ comes before ‘late’.

I’ve tried looked at the field type date, but don’t understand how I could use this. Maybe someone is more creative or clever than me.

1 Like

I think you might need/want to use date ranges to indicate when it’s ripe.

Ideally you’d also want something like a natural languages plugin which could return those date ranges, so that you could get it to respond to phrases like early august, mid december… There is such a plugin for singular dates, but I don’t think it can return date ranges.

You could possibly make your own function where

  • month and:
    • early meant 1-15
    • mid was 8-22
    • late was 16-28/31
  • Only the month was the entire month
  • only early, mid or late meant some given date ranges.
  • (And possibly if a double indication was given it used the min/max variety, so mid august/late september would return the dates 2024-08-15 through 2024-09-30)

Do this seem like it would cover your needs?
Which date ranges would be appropriate for early, mid and late?

Yes, I think that could work. I understand your logic. I don’t know how I make my own function and concretely type it into dataview and properties.

Early could be Late July to August
Mid could be September
Late could be October to early November

I’ve been a little busy lately, but I’ve not forgotten this request, and will hopefully be able to code something (sketchy?) before or during the weekend. :slight_smile:

1 Like

The weekend came, and passed, but here is finally an attempt at solving this using slightly experimental extensions to the DQL language. Toss the following stuff into a note of its own, and play around with it and see if it matches your request:

## Defining `isRipe()` and `ripeRange()`


const dve = dv.evaluationContext.functions

/* isRipe(isoDateTxt, ripeField) return whether the
 *   isoDateTxt indicates this is "unripe", "ripe" or "overripe"
 *   according to the ripe period from the ripeField
 * A few examples
 *   isRipe("2024-08-08", "september") => "unripe"
 *   isRipe("2024-08-08", "early aug") => "ripe"
 *   isRipe("2024-08-08", "jun/jul") => "overripe"
 dve.isRipe = (ctx, isoDateTxt, ripeField) => {
  const [start, end] = dve.ripeRange(ctx, ripeField)
  const beforeEnd = isoDateTxt <= end
  const afterStart = isoDateTxt >= start
  // console.log(isoDateTxt, " vs ", start, " - ", end, beforeEnd, afterStart)

  if (beforeEnd && afterStart) return "ripe"
  else if (beforeEnd) return "unripe"
  else if (afterStart) return "overripe"

/* ripeRange(ripeField) gives start and end period
 * for that ripeField value
 * Internally it only uses the first three characters of 
 * any period/date indication, and it should understand:
 *  "jan" - "dec" : All the months
 *  "early" : July 16th through August 31st
 *  "mid"   : The month of september
 *  "late"  : October 1st through November 15th
 *  "early <mon>" : From 1st through 14th
 *  "mid <mon>"   : From 8th through 24th
 *  "late <mon>"  : From 16th through end of month
 * Combinations are also allowed using "/" between the parts,
 * and then the minimum and maximum dates are returned, so
 *  "mid jan/mar" would return "Jan 8" through "Mar 31"
 * Note the function returns the isoDate strings, not actual dates
dve.ripeRange = (ctx, ripeField) => {

  const periods = {
    "jan": [ "-01-01", "-01-31"],
    "feb": [ "-02-01", "-02-28"],
    "mar": [ "-03-01", "-03-31"],
    "apr": [ "-04-01", "-04-30"],
    "may": [ "-05-01", "-05-31"],
    "jun": [ "-06-01", "-06-30"],
    "jul": [ "-07-01", "-07-31"],
    "aug": [ "-08-01", "-08-31"],
    "sep": [ "-09-01", "-09-30"],
    "oct": [ "-10-01", "-10-31"],
    "nov": [ "-11-01", "-11-30"],
    "dec": [ "-12-01", "-12-31"],
    "ear": [ "-07-16", "-08-31"],
    "mid": [ "-09-01", "-09-31"],
    "lat": [ "-10-01", "-11-15"],
  const ripePeriods = ripeField.trim().split("/")
  let dates = []
  for (const period of ripePeriods ) {
    const parts = period
      .split(" ")
      .map(a => a.trim().slice(0, 3))
    const year = new Date().getFullYear().toString().padStart(4, "0")
    // console.log(parts, parts.length, typeof parts)
    if (parts.length == 1) {
      const dateParts = periods[parts]
      dates.push( year + dateParts[0] )
      dates.push( year + dateParts[1] )
    } else {
      let dateParts = periods[parts[1]]
      // A bit lazy here, but
      //  ear is 01 - through 14
      //  mid is 08 - 24
      //  lat is 16 - 28/30/31
      // Correct start and end of month according to
      //   ear/mid/lat 
      if (parts[0] == "ear" )
        dateParts[1] = dateParts[1].slice(0, 4) + "14"
      else if (parts[0] == "mid" ) {
        dateParts[0] = dateParts[0].slice(0, 4) + "08"
        dateParts[1] = dateParts[1].slice(0, 4) + "24"
      } else  if (parts[0] == "lat" )
        dateParts[0] = dateParts[0].slice(0, 4) + "16"
      dates.push(year + dateParts[0])
      dates.push(year + dateParts[1])
  return [dates.reduce((min, val) => min < val ? min : val),
    dates.reduce((max, val) => max > val ? max : val) ]

## Testing various ripe values
  join(period, " — ") as Range, 
  isRipe("2024-08-06", ripeField) as "isRipe(aug 6th)",
  durationformat(date(period[0]) - date("2024-08-06"), "d") as "daysSince Aug 6th"
WHERE file = this.file
  "early", "mid", "late",
  "janAuaRY", "FEBru", "march", "apricot",
  "maybe", "june", "july",
  "early august",
  "mid september",
  "late octopus",
  "late november / early december") as ripeField
FLATTEN list(ripeRange(ripeField)) as period
SORT period[0], period[1]

## Sorting according to "ripeness"
Here I'm assuming that each apple has its own note, and that there is a field called `ripe` indicating when that variety is supposedly ripe.

TABLE ripe, join(ripePeriod, " – ") as Period
WHERE ripe
FLATTEN list(ripeRange(ripe)) as ripePeriod
SORT ripePeriod[0], ripePeriod[2]

The example note consists of three parts: The function definitions, a test query displaying the various options for defining ripeness, and a final query sorting according to ripeness.

The function definitions

Let me start of by stating that these functions do work, but we’re tapping into slightly unchartered territories of Dataview usage, so they might break at any point in time. But they shouldn’t be destructive in any ways, so as long as they work, they work… :smiley:

I defined two functions in this section, and this code block section needs to be included in every place you want to use those functions (or at least the code block needs to have been executed/run before used in an ordinary DQL query like the two other code blocks shown).

The isRipe() functions takes an ISO8601 date string, i.e. “2024-04-07”, and a ripe field text. It then returns whether that date text related to the ripeness period is “unripe”, “ripe” or “overripe”. It uses the ripeRange() function to find the period.

The ripeRange() function takes a text paramter, the ripe field text, and returns two ISO8601 date strings (not dates). If you want them to be dates for duration calculation or similar, you need to do date(...) on either start or end of that period. See example in the middle query where I calculate days since start of the ripe period, which can be used to show when to expect it to be ripe or similar.

Test examples

For some examples on both the ripeRange() and isRipe() function see the result of the middle query, which should show something like:

Note how I’m just using the three first letters of any date indications, and that they’re lowercased as well. So you could opt to use “january”, “jan”, “JAN”, “jansdfsdlkfjsdf” which would all match for the January entry. I’ve not included any error handling for date indications not already defined, so if you write “unknown” or some other random text, the result is indeed unknown.

Sorting according to ripeness

In the test section I’ve exploded a manually defined list of ripe periods, for ease of reference and coding and just for the purpose of the example. In the latter code block I’ve included a sample query which would look more normal to your every day use case. This query is untested but hopefully you’ll understand how to adapt to your own case.

TABLE ripe, join(ripePeriod, " – ") as Period
WHERE ripe
FLATTEN list(ripeRange(ripe)) as ripePeriod
SORT ripePeriod[0], ripePeriod[2]

The most “critical” part of this is the FLATTEN list(ripeRange(ripe)) as ripePeriod, this will extend your data set to include two values: ripePeriod[0] which is the date text for the start of the period, and ripePeriod[1] which is the date text for the end of the period.

The two first lines is just to display some values and to limit the query to only list notes (aka apples) which have the field ripe present. This you’d replace with any query of yours which limits it to show only the apple notes, and the field name you’ve used to indicate the ripe period.

The last line does the actual sorting, and it’s using two sorting parameters. First it sorts by the start of the period, and if that’s equal it then uses the end of the period to give a natural sorting according to the ripeness period.

If you’d want you could also do stuff like:

WHERE isRipe(dateformat(date(today), "YYYY-MM-DD"), ripe) = "ripe"

To check which apple variants are supposedly ripe today.

Well, hopefully this will help you in organising and/or working with your apple varieties, and that it is somewhat understandable how to use this.

1 Like

Thanks a lot for the effort holroy. It will take me some time to translate to my language and property data, but I will go through it and see if I can make it work and get back to you probably with questions then.