Jump to content

Calculating date and time


taz321

Recommended Posts

Hi

 

I was wondering if anyone had an answer to my question.

 

I have 4 fields in my table called -

 

dateactioned

timeactioned

 

datecompleted

timecompleted

 

The format of the date is  - e.g Thursday, 12, March 2008

AND the time is - 22:10

 

 

I require a calculation which works out the difference between the two sets of dates and time.

 

Thank you in advance for any help

Link to comment
https://forums.phpfreaks.com/topic/97820-calculating-date-and-time/
Share on other sites

Okay thanks, im going to go through my system now and place the date and time in one field, once iv done that i will come back lol

 

Cheers

 

Make note of the FORMAT of the DATETIME field. It accepts YYYY-MM-DD HH:MM:SS as the value, so you will need to convert and combine you existing data in order to get it to fit. This is definitely worth the effort.

No, mysql expects data inserted into a datetime field to be in the following format:

YYYY-MM-DD HH:MM:SS

 

Where HH is 24-hour format.

 

To find the difference between 2 datetime fields, you want to use TIMEDIFF and DATEDIFF (assuming you only want days, hours, minutes, seconds, also assuming >= MySQL 4.11)

 

SELECT
  CONCAT(
   DATEDIFF(`completed`, `submitted`), " days(s) ",
   HOUR( @a := TIMEDIFF(`completed`, `submitted`) ), " hour(s) ",
   MINUTE(@a), " minute(s) ",
   SECOND(@a), " second(s) "
  ) as `difference`
FROM `table`

 

If you want to go into months and years, it gets a little more complex.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.