Difference in two time/dates

[tmcdonnell]

Hi,

 

If I insert a date/time into a mysql database, then insert another date/time in another field,

how can i calculate the difference between them?

 

I presume I should use unix timestamps also, or will a full date/time work?

 

Thanks for reading

 

Tim

 

 

[abdfahim]

I assume you insert the date in the database in normal format (e.g m/d/y or y-m-d etc), because if you insert as timestamp, then a simple subtraction would work.

 

if you want to get exact difference in seconds, you should go for

$sec_diff=strtotime($date1)-strtotime($date2);

 

then of course you could convert this seconds to months/days/hours etc.

[Caesar]

Depends on the approach and what you're comparing. That being said, if it is a small to medium sized database, I will use both. One field/column for a timestamp and a second field with the date (Format - 2008-1-31). The timestamp will give me the hour of the day and an exact time, if I ever need it...and I won't ever have to question how to compare dates. I can also query using the "Date" formatted column. Makes life easy.

[PFMaBiSmAd]

For standard DATE or DATETIME data types, the msyql DATEDIFF() function will return the number of days between the dates.

[Caesar]

Just want to emphasize that if at any point you'll want exact stamps to the minute/hour, it's quite helpful to have a timestamp stored.

[discomatt]

Storing a stamp and a readable date side-by-side is very situation dependent. If it's a huge DB it could be very inefficient, especially considering that the mysql function UNIX_TIMESTAMP() is quite efficient and quick.