How to organize sortable date fields which can include CE and BCE dates?

[excalibud]

I’d like to start using dataview fields / YAML keys / properties for dates that include the possibility of the date being BCE, such that these dates are easily sortable into chronological order in dataview. However as far as I can tell, dataview’s support for the ISO 8601 calendar format does not apply to BCE dates, and various schemes for writing dates in plain text format that I’ve tried out (e.g. BCE 300 vs CE 1500, or -300 vs 1500 or +1500, etc.) don’t yield text-based sorting results that correspond to proper chronological sorting.

This seems like the sort of thing someone out there must have figured out, but I haven’t been able to find anything on this with forum or internet searches.

[gino_m]

Someone’s gonna have a good idea.

In the meantime a mediocre one: take the greatest number among the BCE ones and add that number to all numbers (or plus 1 if it should be problematic to have a value on zero; might as well make it ten thousand). Then when you have the fields, make them static, substract the number back on all and display them.

[excalibud]

Thanks for the idea. If it’s just year I want to organize by, a simple enough solution would be to just use negative numbers for BCE and positive for CE (with the understanding that 0 would not be a valid year since CE starts at year 1). But it gets more complicated if I want to have the option of including month and day in the same field (e.g. to show the birth date of Julius Caesar as July 12, 100 BCE).

[AlanG]

Dataview (and Obsidian) can use MomentJS for formatting and displaying dates.

You can use Moment’s YYYYYY year format for parsing and displaying “expanded years” (ECMAscript docs) which include BCE years.

Store your dates in YAML in the format provided in the docs:

-001957-10-21 (21st October 1958 BCE)
+002021-01-02 (2nd January 2021 AD)

These dates are sortable as-is. When you’re formatting the date for display with Dataview, parse the date using Moment’s YYYYYY format for the years: (my YAML field here is date_test)

```dataviewjs
dv.table(['Note','Date'], 
  dv.pages()
    .where(page => page.date_test)
    .sort(page => page.date_test, 'asc')
    .map(page => 
      [
        page.file.path, 
        moment(page.date_test, 'YYYYYY-MM-DD').format('D MMMM y NN')
      ]
    )
)
```

Which will output a sortable list:

[excalibud]

Amazing! I tried it out and in my vault and it almost works, although it doesn’t seem to correctly sort dates within the same year for both BCE and CE years. For instance, it sorts the following 4 dates in this order:

• 1 July 101 BCE
• 1 January 101 BCE
• 2 July 101 BCE
• 2 January 101 BCE

I thought this might have been due to some weirdness due to the negative timestamp, but if I flip the signs it still doesn’t sort correctly:

• 1 July 100 AD
• 1 January 100 AD
• 2 July 100 AD
• 2 January 100 AD

[AlanG]

Works fine for me. Are you sure you’re using the correct date format? -000100-07-02

And make sure you’re sorting on the date BEFORE you parse it

It should be in your Dataview query after getting your pages:

  dv.pages()
    .where(page => page.date_test)
    .sort(page => page.date_test, 'desc')

[AlanG]

It looks like Moment itself can handle the era year correctly and also can output AD/BC:

moment(page.date_test, 'YYYYYY-MM-DD').format('D MMMM y NN')

I’ll edit my previous post to remove the custom function as it’s not needed.

[excalibud]

After your edit, within-year sorting works perfectly on my end! However, it doesn’t sort across years correctly for BCE dates, e.g. it gives the following sort order:

• 1 January 101 BC
• 2 January 101 BC
• 1 December 101 BC
• 1 July 201 BC

where of course 201 BC should be before 101 BC.

And just to confirm the exact date syntax I’m using for these:

-000100-01-02
-000200-07-01
-000100-01-01
-000100-12-01

[AlanG]

I think that might be an issue with Dataview not knowing how to handle them.

Instead you can use standard Javascript sorting: please note the very important .array() to turn it from a Dataview object to a standard Javascript array

  dv.pages()
    .where(page => page.date_test)
    .array()
    .sort((a, b) => moment(a.date_test, 'YYYYYY-MM-DD').format('x') - moment(b.date_test, 'YYYYYY-MM-DD').format('x'))
    .map(page => 
      [
        page.file.path, 
        moment(page.date_test, 'YYYYYY-MM-DD').format('D MMMM y NN')
      ]
    )

Swap (a, b) to (b, a) to sort in the reverse direction.

[excalibud]

Works like a charm! Thanks so much!

For posterity, here’s the full code block as I have it:

```dataviewjs
dv.table(['Note','Date'], 
  dv.pages()
    .where(page => page.date_test)
    .array()
    .sort((b, a) => moment(a.date_test, 'YYYYYY-MM-DD').format('x') - moment(b.date_test, 'YYYYYY-MM-DD').format('x'))
    .map(page => 
      [
        page.file.path, 
        moment(page.date_test, 'YYYYYY-MM-DD').format('D MMMM y NN')
      ]
    )
)