CODEWITHSUNDEEP
phpmysql
Mr Dansk
Mr Dansk

Reputation: 786

MySQL compare dates in a column to today

I have a table of around 6000 records with a date column amongst other columns which represent the deadline for a query. I need to compare the date in the column to todays date which I understand is done something like:

SELECT DATEDIFF(DATE_TO_COMPARE, CURDATE());

However, I then have another comlumn I want to set to that date difference. So for each date, I need to compare, insert the difference in the column difference_in_days, iterate to the next date and repeat.

I am also invoking this function whenever a certain page on my site is loaded using AJAX and PHP/PDO

My SQL knowledge isn't that extensive, how can I achieve this.

Table is kinda of like

    field 1, field2, field 3, date_to_compare, field 4, field 5, difference_in_days       
    |       |       |       | 2016-04-20      |        |       |                   |
    |       |       |       | 2016-04-25      |        |       |                   |
    |       |       |       | 2016-04-22      |        |       |                   |
    |       |       |       | 2016-04-27      |        |       |                   |
    |       |       |       | 2016-04-29      |        |       |                   |

Upvotes: 0

Views: 1186

Answers (1)

Steven Moseley
Steven Moseley

Reputation: 16325

Sonds like you want to do an update?

UPDATE table_name
SET difference_in_days = DATEDIFF(date_to_compare, CURDATE());

This will update every record in the table to the diff of the current date.

However, this will require you running the update every day, if you want that column to maintain relevance.

Alternative Approach:

If you're not querying this a lot, you may be better off using a view, which will update real-time every time you query it.

CREATE VIEW diff_view_name AS
SELECT *, DATEDIFF(date_to_compare, CURDATE()) AS difference_in_days
FROM table_name;

Then you could query it using:

SELECT * FROM diff_view_name;

Upvotes: 3

Related Questions