Making new column by subtracting date column from current date after joining tables in SQL

Question

I have two tables that I have joined and I want to add to the result of that join another column. This column will be the result of the current date minus the column date called start_date. I would like to call the new column days because the difference would be in days.

Here is how my join looks like:

SELECT t.kind,
         t.start_date,
         t.user,
         t.user_email,
         t.completed,
         g.name
FROM d_trans AS t
JOIN d_gar AS g
    ON t.rest_id = g.id
WHERE t.completed = false
        AND t.kind = 'RESTING';

My SQL coding skills are limited. I usually just work with R after importing the data. This would be simple in R but I am not sure how to do this in SQL. I looked online for similar questions and I saw things like ALTER TABLE ADD to add a new column and the function GETDATE() to get the current date but everything I tried did not work. I am working in Athena from AWS I don't know if my problems are due to the limitations of Athena.

Answer 1

Use date_diff('day', current_date, date_col) function:

SELECT   t.kind,
         t.start_date,
         t.user,
         t.user_email,
         t.completed,
         g.name,
         date_diff('day', cast(t.start_date as timestamp), current_timestamp)  as days
FROM d_trans AS t
JOIN d_gar AS g
    ON t.rest_id = g.id
WHERE t.completed = false
        AND t.kind = 'RESTING';