'Added today' group remains empty

[trueclou]

Hi,

since some time, I recognize a false behavior for the 'added today' smart group. I din't exactly know when it stopped working, but I guess it began with one of the recent updates. The SQL code for the group is unchanged:

dateAdded > datediff( now(), '01/01/1904 00:00:00', 'second' ) - (1 * 24 * 60 * 60)

It certainly should provide some results, but it doesn't. Did the SQL syntax change in some ways?

Best, Christoph

[Jon]

At some point that probably coincided with an OS upgrade the date parts delimiter varies with the system language setting. I've only had support requests about this from German users, so it may just apply to them. In any case, try using a - instead of a /. Like this

dateAdded > datediff( now(), '01-01-1904 00:00:00', 'second' ) - (1 * 24 * 60 * 60)

Jon
Sonny Software

[trueclou]

Thanks for the quick reply. The idea with the wrong date format was correct, but the solution are dots:

dateAdded > datediff( now(), '01.01.1904 00:00:00', 'second' ) - (1* 24 * 60 * 60)

This works.

Additional question: why don't you simply work with unix timestamps?

Best,
Christoph

[Jon]

Because Bookends has been around long enough that some dates are saved in the old macOS epoch, which began in 1904, not 1970. At some point we will convert them.

The solution for now will be to ignore locales and have the database recognize the slash (/) as the date part separator so that the same SQL will work for everyone, regardless of local macOS settings. That will be done in the next update. When that is released, you should change the SQL to the factory default: dateAdded > datediff( now(), '01/01/1904 00:00:00', 'second' ) - (1* 24 * 60 * 60)

Jon
Sonny Software

[GJPhD]

I follow this thread with interest as I have been having the same problems for about two years.

viewtopic.php?t=5784

New Bookends versions and MacOS versions don't fix it, nor does clean installs or creating new libraries. And now unfortunately also not using the dots.

For now, I just have a SQL folder with "recID <> 0 order by dateAdded DESC limit 120", so I at least have an overview of recently added articles.

Happy to help out at some point in resolving the issue, but it is not a showstopper for my Bookends workflow.

[Jon]

Have you tried a dash? 01-01-1904 ?

Assuming your system language isn't English, please try temporarily setting it to English. See if that helps.

Jon
Sonny Software

[GJPhD]

Thanks, John. Both of these worked. As a non-English speaker, I am going to stick with the dashes but wonderful to have this Smart Folder working again

[Jon]

Don't forget, in the next update dashes and periods in that SQL date part-search will not work, but slashes will work for everyone. You can still use whatever you want in the database date field, however.

Jon
Sonny Software

[DrJJWMac]

Please reconsider to have Bookends adopt an ISO 8601 standard YYYY-MM-DD format for SQL date searches. https://en.wikipedia.org/wiki/ISO_8601

[Jon]

This thread deals only with searches for dates established programmatically (e.g. date added and date modified). If you mean dates imported or entered manually in the Date field, they are free-form.

Jon
Sonny Software

[DrJJWMac]

OK. Thanks.