Calculating date and time

[taz321]

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

[BlueSkyIS]

i suggest that you use 1 field for each date/time instead of 2. assuming you're using MySQL, i would use a single DATETIME field for actioned and one for completed.

 

this will make your calculations simpler as you only have to consider 2 fields instead of 4.

[discomatt]

Also, mysql has a broad range of date functions available.

 

Check them out here

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

[taz321]

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

[obsidian]

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.

[taz321]

I have changed my system so that the DATE-TIME is in this format -

 

$today = date("F j, Y, g:i a");                 // March 10, 2001, 5:16 pm

 

This is an okay format to use for what i need ?

 

Thanks

[taz321]

As i believe i have the correct Date/Time format, would anyone have an idea on how i can find the difference in time with the following fields -

 

Date/time submitted

 

AND

 

Date/time completed

 

Many Thanks in advance.

[discomatt]

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.