What is the trick to ordering messages by date?

I have tried every way I can think of to get the most recent messages to appear on the bottom of the list. I am writing the date time using formula DATETIME(FORMAT_DATETIME_LOCAL(NOW(),“YYYY-MM-DD HH:MM:SS”))

It writes to database as a string.

When I try to retrieve the messages in Appgyver using order by date “descending,” nothing shows up. If I remove the ordering, all the messages show up but of course not in the correct order.

What is the trick? When I set the data field in the connector to a datetime format instead of a string, it will not create the message to begin with. It just does nothing. The create record does not work.

Since it is a string, is there a way to tell it to look for a string and convert it to date time in the ordering formula?

It would be something like this:
Messages = ORDER(Messages, item.DateTime, “desc”)

assuming your DateTime value is in a usable time format this should work. What I always do though is when im storing messages i always just store them raw date/time values in the database, such as NOW(""), and then do all my formatting when displaying them. It makes them easier to work with for sorting.

2 Likes

So I should change the field type in the data connector to date/time format instead of string? I have not been able to figure out how to write that in a rest http request to firebase. Although I set it to that formula it always writes it as a string even though it is the datetime format. Oh, but on web it won’t even do that. It always writes the document with date as null.

Technically the value that is created from NOW("") is actually a string, so you shouldnt have to change it to date/time format if you dont want unless you just want to be explicit, but the actual value of NOW("") is an ISO format that works better with SORT.

When it came to managing dates I used timestamp, which is an integer, ms since 1970. It made life so much easier, for many things - adding filters, ranges, before, after etc. And for display purposes you then just convert it.

It shows up like this in my database. 2022-04-11T22:04:58Z

Are you saying that instead of “DATETIME(FORMAT_DATETIME_LOCAL” I should just use NOW(’’) ?

Ok that does looks like a standard format. The ORDER function should work with that.

What @Phil_Evans suggested would work well too, sorting an integer value.