Query to calculate days between dates in database and today

[Miotx]

Hello all,

I was hoping to get some help with a workflow issue I have.

Step 1 is where I query my retool db for the ID and DATE as shown below:

Step 2 is where I want to loop through each dates from the response of Step 1 and calculate the number of days. I have some code that works but it only calculates between a date I manually put in (in this example: "2024-03-10"). How can I edit the code to loop through the dates from the responses in step 1.

function dateDiffFromToday(date) {
    // One day in milliseconds
    const oneDay = 1000 * 60 * 60 * 24;

    // Get the current date
    const today = new Date();
    
    // Convert both dates to milliseconds
    const todayMs = today.getTime();
    const dateMs = date.getTime();

    // Calculate the difference in milliseconds
    const diffMs = Math.abs(todayMs - dateMs);

    // Convert back to days and return
    return Math.round(diffMs / oneDay);
}

// Test the function
let myDate = new Date("2024-03-10");
let diffDays = dateDiffFromToday(myDate);
return(diffDays);

Any help would be greatly appreciated.

Thank you!!

[MikeCB]

Hi @Miotx,

You can actually do this right in your query, no need for the extra step in JS.

You're using the Retool DB, so the query in query1 would just need to change to:

select id, date, CURRENT_DATE - date::date days_between from facilities_rfp

This will give you a new column called days_between with the number of days you're looking for.

[jg80]

+1 to just getting the date difference directly in the DB, but if you do want to do this in a loop, you just need to reference the value object that is created in the loop. In your case:

...
// Test the function
let myDate = new Date(value.date); 
...

I mocked it up quick as an example (in my case, I reference value.start_of_week as the variable):

[Miotx]

Awesome!! Thanks so much! Worked like a charm!!

[Miotx]

Thanks for your reply, truly appreciate it!!