Well, Here We Go Again - Time Problems

What I’m trying to do

I have a Daily Notes template in which Templater on creation of a Daily Note puts the creation date of the file retrieved from the Linux bash date command using Templater user functions.

It looks like this:

creation-date: <% tp.user.systemdate() %>
modification date: <% tp.file.last_modified_date("YYYY-MM-DD HH:mm:ss")%>

The systemdate function looks like this:

date '+%Y-%m-%d %H:%M:%S'

The result looks like this:

creation-date: 2024-01-23 21:29:02
modification date: 2024-01-23 21:29:02

Note that the creation-date field is a TEXT object, NOT a DATE object. I’m not sure how to create a date object from the text output of the system command.

I have a Weekly Notes folder contains one note for each week which uses the same format. In each weekly note I have a query that I want to run which does the following:

List all the Daily Notes that are for the same year, same month, and same week as the weekly note.

Things I have tried

Absolutely everything for the last 12 hours.

Here is the problem:

In the WHERE clause, I can easily convert the creation-date object from each Daily Note into a date object and extract the year or the month or the week for comparison purposes.

What I can’t seem to figure out is how to convert or otherwise format the text property of the Weekly Note file into a format that will compare to either the text string of the incoming Daily Note file or the converted date object of the incoming Daily Note file.

I have tried doing date conversion of the Weekly Note creation-date field and it always tells me it can’t parse the date string. I’m trying to separate out the year, month and day with three separate compares and it just doesn’t work.

I originally tried using this query:

WHERE date(creation-date,"yyyy-MM-dd hh:mm:ss").year = this.file.ctime.year
AND date(creation-date,"yyyy-MM-dd hh:mm:ss").month = this.file.ctime.month
AND date(creation-date,"yyyy-MM-dd hh:mm:ss").week = this.file.ctime.week
SORT title ASC
Limit 300

That worked fine - until I had to reinstall the OS, and restore Obsidian from the backup. Now ctime - which is almost useless because most of the Linux filesystems do not recognize ctime as a valid date - is set to 1969. Template’s file creation date function picks up the wrong date as a result, which is why I’m using the bash system date which looks to the right system function to produce a correct “birth date” (as it’s called.)

So I have to use the creation-date in the YAML front matter to do comparisons. But I can’t figure out how to reference the creation-date field in the Weekly Note so that I can compare it to the creation-dates in the Daily Notes.

I’m beginning to think I can’t really use any of these “built-in” or “plugins” at all because they invariably require a day or more of research and figuring out how the functions, literals, etc. of Dataview fit together is a nightmare.

Which means please don’t suggest that I do anything with JavaScript or or Dataviewjs queries because I’m not going to spend days learning JavaScript right now.

i want a reasonably simple solution that simply compares to string dates. Something like:

WHERE incoming.file.creation-date.year = this.file.creation-date.year

Which unfortunately is not valid syntax - which is the problem.

Any help appreciated. Thanks in advance.

Herein lies your main problem, that this string is not recognised by Dataview as a date, since it’s lacking a T between the date and the time part. Thusly it’s considered as just a string, and you can’t really use it in its current form for anything date related.

You’ve already discovered though that you can use the date(text, format) function to reconvert this back to a date, but you would make life a lot easier for yourself if you inserted that T into the string in the first place.

Listing notes of the current week

Assuming that your weekly note has some field like week: 2024-W08, you should be able to do the following:

WHERE this.week = dateformat( 
  date(file.creation-date, "yyyy-MM-dd hh:mm:ss"),

If you don’t have the week field defined, you could use dateformat( this.file.creation-date, "yyyy-MM-dd hh:mm:ss"), "yyyy'-W'WW") instead of it. The main point here is that this.* refers to the current file’s variables and fields, but without it you’re referring to the information in the file you’re currently querying.

I’m sorry you feel like this, but I believe it’s a matter of start up hickups, and once you pass the first few hurdles, you’ll get the hang of it, and it’s easier to do stuff like this.

As stated already, the best way forward would be to actually change your creation-date to be proper dates by inserting that missing T between the date and time part, so it would read: date '+%Y-%m-%dT%H:%M:%S'. Then you wouldn’t need to do date(text, format) stuff like we’ve done above, and you could have used the following instead:

WHERE creation-date.year = this.creation-date

or for comparing the week:

WHERE dateformat(creation-date, "yyyy'-W'WW") = dateformat(this.creation-date, "yyyy'-W'WW")

Hope this helps, and please ask if something is still a little unclear related to this date handling.

1 Like

So if I read you right, you’re saying that the string returned by the system date command can be converted by Dataview into a date object by placing a “T” between the two sections of the string returned by the command.

I have the creation-date property defined as a text property. Would I have to change that to a datetime property? No problem if so. I think the only reason I defined it as a string property in the first place is because when the date was put in there it defaulted to a different format than “YYYY-MM, etc” which IIRC is a problem with date properties.

I will attempt what you’ve suggested and report back. Might be a little while today as my day is messed up.

Thanks for the assist.

OK, I adjusted the date command to output the string with the “T” and changed the creation-date property to a datetime property rather than a string.

I then tested this by simply trying to list the year of the creation date field: as follows:

TABLE creation-date.year
FROM "Periodic Notes/Daily Notes"
SORT title ASC
Limit 300

I get this error message:

Dataview: Every row during final data extraction failed with an error; first 34:

 - string indexing requires a numeric index (string[index])
- string indexing requires a numeric index (string[index])
- string indexing requires a numeric index (string[index])

It seems to still be treating the property as a string? If I remove the “.year”, it creates the table with no problem and prints the creation-date.

I thought according to the Dataview documentation that any date object has the ability to extract the parts of the date: https://blacksmithgu.github.io/obsidian-dataview/annotation/types-of-metadata/#date

However, it also says they have to be in ISO8601 format, which a date property is not by default.

So I still have to do:

date(creation-date,"yyyy-MM-dd hh:mm:ss").year

in order to get the year portion. “this.creation-date.year” does not work at all, gives me the string index error.

But if I do this on the TABLE line (as a test):

this.date(creation-date,"yyyy-MM-dd hh:mm:ss").year

I get this error message:

 - Cannot call type 'null' as a function
- Cannot call type 'null' as a function
- Cannot call type 'null' as a function

OK, I solved that problem by doing this:

date(this.creation-date,"yyyy-MM-dd hh:mm:ss").year

My problem before was that I was trying “this.creation-date.year” and that doesn’t work, the “this” date has to be converted first and then it can extract the year.

I’ll play around some more, but I think the problem may be solved.

1 Like

I am not aware of the Templater details, but if it depends on bash to generate the date string, then the command date '+%Y-%m-%dT%H:%M:%S' will immediately yield the date-time string in the correct format with T inserted.

You do need to go through your existing notes, and add the “T” into the creation-date files that you’ve already created.

LIST creation-date
WHERE contains(creation-date, " ")

It shouldn’t be necessary to change the type, but if you’ve done it it’s no harm, and it should show a warning triangle on all notes still having the older variant.

In other words, after changing the system user functions as I suggested by adding the T to it in my previous post, and changing the existing properties to include the T you should be able to use the easier syntax of always it being a date.

I was thinking about linking that article as the proof of why you do need the T to be in there. The ISO8601 doesn’t require it, but dataview does require it. What can be left out by dataview is any of the latter parts of a fully specified date/time.

You’re right. I do have to put the T in the file otherwise comparisons don’t work. I discovered that just now when I tried to use the same query on my Weight Tracker notes. Yesterday’s note did not have the T whereas today’s did - and the creation-date comparison failed. The query itself worked for every other date but today’s note.

Great - now I have to figure out how to insert a T in the dozens if not hundreds of other notes that were created with creation dates. That’s not going to be easy since I’m not that familiar with some of the Linux command line text processing tools.

I don’t see that warning triangle you mentioned, however.

Well, inserting the T broke the query because I was using the date command to convert the creation-date and… I don’t know what the hell I’m doing now.

The query works if I change the property type back to string but then it only shows files with the T in them. If I leave the property type to datetime then the query only shows the earlier files without the T and not the files with the T - which means I have to change hundreds of files outside of Obsidian with some Linux text manipulation program I don’t know how to do.

I have two choices: dump this entire project or spend days converting hundreds of files with creation dates that are in the wrong format.

I give up. Date processing is just impossible. It’s just too much nit-picking research and then having to debug queries.

I’m going to dump this project. I can’t make it work without too much nit-picking.

God, I hate programmers. And that’s from someone who used to do database development.

This could possibly be done using a query, and actually be easier done using a query.

One question though; we’ve talked a lot about the creation-date, but what’s the deal with the modification date? Does that also miss the T? And why did it have a space in the middle of its key? And finally, what/who is updating its value?

Please don’t. Tomorrow I could build a query which will unify your values to the better standard, and then the you could you use the simpler queries in the future.

As a programmer, I’m sorry for being the bearer of bad news. But you’ve got a choice here (besides giving up on the project): Either keep the non-optimal date format and use queries where you you need to convert it like we’ve talked about, or change to a better date format which will make future queries a little simpler.

I can aid you in converting away from the older date format, if you would want me to do that.

This is what I’ve decided to do. I’m going to take the system date and remove the T. That produces a string date in the format “YYYY-MM-DD HH:MM:SS”.

Now I can do “date(creation-date,“yyyy-MM-dd hh:mm:ss”).year = date(this.creation-date,“yyyy-MM-dd hh:mm:ss”).year” and that works perfectly fine.

Now I don’t have to change anything, I just have to remember to convert any dates used for comparisons with the date command.

The queries work fine now. Problem solved.

The modification date remains in the string format like the creation date. I’m unlikely to ever want to compare that. In fact, I’m not sure I will ever even need it, but I might as well keep it just in case. It can be handled the same way.

None of this would be necessary if the bloody property date format was able to be reformatted into my preferred format. Then I could have simply left it as a date object and formatted to look like a correct ISO8601 format. Then I could use it directly as a date object in comparisons.

That’s the reason I hate programmers - they forced the display format on properties to be one format only. I believe that’s an outstanding change request IIRC. In other words, like most users I want my cake and eat it, too - and programmers constantly do not understand that. Give people the OPTION! If there is more than one way to do something, give the user the option to do any of them.

Sorry for suggesting a more compliant way, but I can understand your reasoning, and that is also why I not so long ago made it possible to use date(text, format) so as to give people like you an option.

But I do feel I’ve also been clear that you’ve always got the options presented to you.

A little sad you hate me as a programmer, but that is your prerogative…

Does date(text, format) work in properties? That is my issue - that properties defaults to only one form of display.

Dataview in general doesn’t work within the frontmatter. You can store queries there, and they’ll sometimes get executed when rendered, but I wouldn’t rely on that aspect.

And date(text, format) is not used to format a date for presentation. It’s used to pull a date out of a pre-formatted text which holds date parts accordingly to the format. In other words, if you’ve got a date you use dateformat(myDate, format) to present it in another style. If you only got a text string, but need a date, you use date(text, format) to extract a date from it according to the format specified.

Does that make sense?

I’m not sure which date format string is used when Obsidian presents dates in the properties section. I’m wondering if it follows the current locale, or something like that, but not sure.