(Lost) How to add calculated field on Database > API response ?

Lequentz

Hi everyone !

I'm a new Xano user and i use an api to return a simple database request :

• In my table i have several entries named "Tasks", each task is linked to a "Person".
• The request i use in API call return all Tasks related to this person.
• I use the database function "Query all Records from" and return the result :

[

0: {

  "id": 2 "created_at": 1693256023830 
  "blueprints_id": 21
  "last_date": "2023-09-01"

  "next_date": "2023-09-30"

  "name": "Task 1"

  "person_id": 2

}

1: {

  "id": 4 "created_at": 1694370471507 
  "blueprints_id": 3 
  "last_date": "2023-09-01"

  "next_date": "2023-09-30"

  "name": "Task 2"

  "person_id": 2

}

]

Question: I would like to add a new field called "since" to each item in the array and calculate the number of days from "next_date" to today's date. I've tried different methods but none seem to solve my problem. Can you help me to find the most efficient in order to accomplish this within Xano ? 🙏

Expected result :

[

0: {

  "id": 2 "created_at": 1693256023830 
  "blueprints_id": 21
  "last_date": "2023-09-01"

  "next_date": "2023-09-30"
  "since": -18

  "name": "Task 1"

  "person_id": 2

}

1: {

  "id": 4 "created_at": 1694370471507 
  "blueprints_id": 3 
  "last_date": "2023-09-01"

  "next_date": "2023-09-30"
  "since": 2

  "name": "Task 2"

  "person_id": 2

}

]

Thanks a lot for your help,

Quentin

arturosanz

You could also use evals in your QueryAllRecords function to add a new calculated field on the fly.

Here is a quick example I made using the created_at field as your next_date field. The millisecs calculated field contains the created_at - now in millisecons. Just need to be careful when dealing with timestamps because they are not integers, even though they look similar.

Here is the output ordered by millisecs ascending…

Ray Deck

Data augmentation is a great use case for the function stack. After running the query all records, you can make a "for-each" loop that goes over the results from that table. Then you can "update value" the "item.since" based on the calculation of the next_date vs today.

So yes, it's doable, and its pretty fast!

We work on data manipulation regularly as part of our focus on the hardest 5% in our recorded office hours on State Change. As you find more complex questions in this regard, might be worth checking out!

Lequentz

Hi !

Sorry for my late reply guys ! Thanks a lot for your help :)

I tried both and the second solution was the lightest for this use case ! Thanks !

Edit : @arturosanz i still have a challenge : with this method i can only calculate the time between now and a date in the futur. Because when i do it with a past date, i got "null" as result (I expected to have a negative number). I'm still trying to solve this problem :)

arturosanz

@Lequentz maybe you can do this trick to deal with past dates.

Apply the filter timestamp_add_years with a big enough number to make all possible field values future values before applying the epochms_substract_seconds filter to now in days. Afterwards, apply the timestamp_substract_years filter with the same number of years you've added before. Last, add the timestamp_epoch_day filter to obtain the negative days as you expected for past dates, and positive days for future dates.

Just keep in mind that by converting milliseconds to days could get you in trouble when the difference between date and now is less than 24 hours, because the result will always be 0 no matter if date is past or future.