0

I have a MySQL database with some columns and in two of them I have two different dates.

In the site I have the two dates in a two colums inside a table, but I need to calculate and reproduce in another column the number of days between that two dates.

I found this code:

?php
$date1 = date_create("2017-04-15");
$date2 = date_create("2017-05-18");

//difference between two dates
$diff = date_diff($date1,$date2);

//count days
echo 'Days Count - '.$diff->format("%a");
?

And it works but I need to change this dates and put the data inside my database. Jow can I solve this?

Im using this:

update client_invoices set x6 = datediff(x4, date_due)

as an event in mysql but everytime they updates the server the "Event scheduler status" is turned off.

How can i perform this directly in my sql without scheduling events?

Thanks, Ribas

RibAs
  • 11
  • 3
  • Please do not SHOUT when posting here. It won't get you answers any faster, it makes text harder to read, and it's really rather rude when you come here SHOUTING and DEMANDING attention. Thanks. – Ken White Nov 03 '18 at 22:06
  • You can read this post https://stackoverflow.com/questions/676824/how-to-calculate-the-difference-between-two-dates-using-php And this post how to put into DB https://stackoverflow.com/questions/12120433/php-mysql-insert-date-format – Ferdinando Nov 03 '18 at 22:12
  • 2
    Please update your question with some example data and table schema of the table you want to update. MySQL is capable of performing the calculation. Use MySQL instead of PHP, which will allow you to directly query the resulting desired values, instead. e.g. `UPDATE table SET days_column = datediff(date2, date1);` – Will B. Nov 03 '18 at 22:17
  • 1
    Possible duplicate of [How to get the number of days of difference between two dates on mysql?](https://stackoverflow.com/questions/2490173/how-to-get-the-number-of-days-of-difference-between-two-dates-on-mysql) – Will B. Nov 03 '18 at 22:25

1 Answers1

2

In MySQL, you would simply use datediff():

select t.*, datediff(day1, day2) as days_diff
from t;

If you want to update a column:

update t
    set diff = datediff(day1, day2);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    to avoid a negative value, be sure that you include the correct order of dates. since `datediff('2018-01-01', '2018-01-02');` will return `-1` – Will B. Nov 03 '18 at 22:20
  • Im using this: update client_invoices set x6 = datediff(x4, date_due) as an event in mysql but everytime they updates the server the "Event scheduler status" is turned off. How can i perform this directly in my sql without scheduling events? Thanks, Ribas – RibAs Nov 03 '18 at 22:54
  • @RibAs . . . You can just run the query through an interface to the database, such as the GUI tool that comes with MySQL. – Gordon Linoff Nov 04 '18 at 00:50
  • Can i use phpMyAdmin for that? Can you give me some more information about this? – RibAs Nov 04 '18 at 01:32