[SOLVED] Mysql Date Function

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Etherguy
Forum Commoner
Posts: 70
Joined: Fri Nov 01, 2002 9:09 pm
Location: Long Island, New York

Mysql Date Function

Post 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 :wink: 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!
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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()
User avatar
Etherguy
Forum Commoner
Posts: 70
Joined: Fri Nov 01, 2002 9:09 pm
Location: Long Island, New York

Post 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!
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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).
User avatar
Etherguy
Forum Commoner
Posts: 70
Joined: Fri Nov 01, 2002 9:09 pm
Location: Long Island, New York

Post 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. :lol:

Again, thanks for your help!
Post Reply