Today is January 22, 2023 (PST around 9am, if that matters). I logged some habits in a table, and the date is showing as today (“1/22/2023”) in the column. But when I use the filter bar to select “today” or “this week”, it does not recognize the entry as being today. I use Monday as the week start, if that matters. (Though I would still expect it to recognize today as today.)
Link to screen capture: Screen Recording 1-22-2023 ... (video link has gone over its view limit - see test doc link below)
I was messing around with this further, and it seems to be something weird with the date formatting. The larger context here is that I use an iOS shortcut to trigger a webhook in Make (Integromat) which then updates a row in Coda. The date field is updated in that process. I had been just sending the date “as is” from Make, and it was working absolutely fine up until yesterday, but it doesn’t work now - creating the problem I referenced above.
However, when I go into that date field within Coda and I change the date by hand, then the filter bar (or any filter) does recognize Today properly. I also went into Make and instead of sending the date “as is” to Coda (Make stores it in the format “01/22/2023 11:46 AM”), I changed it to send a formatted date to Coda (MM/DD/YYYY). Doing that makes Coda understand that 1/22/2023 is Today.
What I don’t understand is why Coda was using the date properly for sorting, but not for filtering. If you go in to the test doc I linked to, you can sort by ascending or descending date and that works just fine. Coda did previously seem to understand that “01/22/2023 11:46 AM” is a date - for example, it was previously stripping that leading zero from the month - but for whatever reason, it now doesn’t recognize it as a date for all purposes. Weird.
Anyway, I have my particular situation sorted out, but I thought I would let you know what I discovered along the way.
Three words - Rube Goldberg Machine. Automations with lots of parts create problems.
UTC is the likely explanation. Systems typically assume UTC dates/times unless otherwise specified. This would explain why it has worked and suddenly did not. You had not worked with any data artifacts whose UTC dates were offset enough to place them in a different day.
Perhaps, but they also plug the holes in inadequate or overly restrictive systems. I wouldn’t have to resort to this kind of stuff if Coda’s webhook automation limits were rational.
Perhaps you can look at the test doc I posted - you can see that the date is properly displayed (ie it’s not showing 1/23/2023, it is showing 1/22/2023). It’s not super clear to me why Coda would interpret UTC one way for display and a different way for filtering. Do you have any insight on that?
It is, and it’s puzzling. I made a copy of the table and it continued to show this behavior. But as soon as I changed the date format to ISO (2023-01-22) it started to work as I expected. So then I changed the format back to your original format (1/22/2023) and the search and filters began to work properly.
Try those steps - maybe you found a date formatting corruption bug?
I think the issue is that Make inserts not just a date, but a date-time value (i.e. not exactly at 12 AM). It doesn’t matter that the column type is set to date — what gets inserted into a cell is the original date-time value. Here are the true values:
That’s why matching against Today() doesn’t work — none of the timestamps strictly equals Today(). That’s also why “between yesterday and today” works:
When @Bill_French changed the format around, Coda did convert those values to just-the-dates. It’s only when one changes the column type, Coda tries to convert values to the new data type.
Two options for you:
Set up Make to not send the dates as is but apply a formula e.g. formatDate(date; YYYY-MM-DD)
Save those timestamps into a separate column, and then make the date column a formula to strip the value down to a date, either timestamp.DateTimeTruncate("day") or simply timestamp.ToDate()
P.S. Just noticed that the timestamps are in UTC (the letter Z in the timestamp gives that away), so if you go with option 1 that should include timezone conversion argument: formatDate(date; YYYY-MM-DD; America/Los_Angeles). Or go the 2nd option, no change required there.
I kinda agree that to make it more intuitive, Coda could’ve just assumed that “equals to Today()” should automatically strip the timestamp and/or match on all timestamps from the day. It seems like Coda doesn’t do that even if I change the column type to Date/Time — it should’ve assumed the range of all times within that day. On the other side, this here shows how important it is to be strict with your data types and mindful about whether what you’re inserting actually matches that type.
And despite doing that, it’s not a remedy, right? Make will continue to plow new incompatible dates into this table which will be dysfunctional until another date format change will re-align the new rows. It’s not a one-and-done fix.
I like to blame Make and Zapier for just about everything.
That makes sense, sort of - at least from a coding mindset. Garbage in, garbage out and all.
I had already done this, although I hadn’t added the timezone conversion (though I just added it this morning). Clean data in, clean data out.
Yeah, for a service which wants to be “no code” or “low code”, I would expect Coda to be a little more friendly and “vernacular” - a coder might know that Today doesn’t always mean today, but a first-timer just trying to keep track of data in a table might not.
Anyway, at least this thread will be here for them when they get stuck and go looking for answers!
PS. Maybe a note should be added somewhere in the Coda documentation (here, perhaps: Coda formulas, formula library, formula cheat sheet - Coda ???) about how the formula works with dates that have times - and a caution about UTC or something like that?
