Page 1 of 1
Mysql Date Function
Posted: Wed Sep 01, 2004 8:34 am
by Etherguy
Okay,
This one is driving me a little batty

So any help would be appreciated!
I am trying to get the elapsed time from a date in the past to the current date. When I run this query
Code: Select all
select (CurDate() - project_startdate) from projects where pid=1;
I get a response like this - "330". Now being the rocket scientist that I am

I am able to see that it is 3 months and 30 days between dates.
What I want to do is get this in a format that can easily be outputed without writing a thousand different php statements!
Posted: Wed Sep 01, 2004 8:41 am
by markl999
I get a response like this - "330". Now being the rocket scientist that I am icon_wink.gif I am able to see that it is 3 months and 30 days between dates.
I'd check that. What if it returned 110? Is that 1 month and 10 days or 11 months and 0 days ?
curdate() returns a date in the format YYYYMMDD so by subtracting another number from that i don't think you'll get the time difference between the 2.
You might need to use another function ( see
http://dev.mysql.com/doc/mysql/en/Date_ ... tions.html ) such as datediff()
Posted: Wed Sep 01, 2004 8:53 am
by Etherguy
Mark,
Through the magic of update, I changed the date. This is what I get...
Code: Select all
project_startdate = 2004-05-01
select (CurDate() - projects.project_startdate) from projects where pid=1;
Outputs 400 (4months 00 days).
trying that same query less the minus and using datediff returns errors!
Posted: Wed Sep 01, 2004 9:08 am
by markl999
select datediff(curdate(), '2004-05-01');
= 123 days
I'm not sure how you worked out that 400 = 4months and 00 days ?
If it returned 100 how would you interperate that? 1 month and 0 days or 10 months and 0 days ?
The 400 won't be 4months and 00days as mysql would never return anything in that format, if anything, 400 will be 400 days (though in this case i suspect the 400 is meaningless).
Posted: Wed Sep 01, 2004 9:26 am
by Etherguy
Mark,
I thank you for your help. I ended up using
Code: Select all
TO_DAYS(NOW()) - TO_DAYS(project_startdate)
And got the same result. Although for the sake of good conversation, I played around with the startdate on the other query and the out come was always correct. For example changing the start date to :
2003-05-01 - 10400 (1 year 04 months 00 days)
2004-08-01 - 100 (1 month 00 days)
Whether it is correct or not, I think it is pretty darn neat.
Again, thanks for your help!