Sorting timestamps with null values

In Xano, when working with APIs and sorting data based on timestamps or dates, you may encounter situations where some records have null values for the date/time field. By default, null values are sorted first, which may not be the desired behavior if you want to prioritize the most recent non-null dates.

In this guide, we'll show you how to use the coalesce filter in Xano to replace null values with a specific value, ensuring that non-null dates are sorted correctly.

Step 1: Identify the Sorting Field

Let's assume you have a table called "connections" with a field called "last_message" that stores the timestamp of the last message received. Some records may have null values for this field.

Step 2: Create an Eval Field

1. Navigate to the API endpoint where you want to sort the results.
2. In the "Output" tab, click the "Add Eval" button.
3. Select the "last_message" field from the dropdown.
4. Give the eval field a name, for example, "sort_message".

Step 3: Use the Coalesce Filter

1. In the eval field configuration, click the "Add Filter" button.
2. From the list of filters, select "Coalesce".
3. The coalesce filter allows you to provide an alternative value for null values. In this case, enter "0" (zero) as the alternative value.

The coalesce filter will replace all null values in the "last_message" field with 0, effectively pushing them to the end of the sorted list.

Step 4: Sort the Results

1. In the "Output" tab, locate the "Sort" section.
2. Select the "sort_message" eval field you created.
3. Choose the "Descending" order to sort the most recent timestamps first.

Step 5: Run the Query

After completing the above steps, run the API query. You should now see the results sorted based on the "last_message" field, with the most recent non-null timestamps appearing first, followed by the null values (represented as zeros) at the end of the list.

By using the coalesce filter in Xano, you can effectively sort API results based on date/time fields, ensuring that null values are prioritized correctly according to your requirements.