date and time calculations

drag0n

I am using time() to store many of the dates/times in my db.
However I am a bit confused as to how to calculate/manipulate the times when I need to.
For example I want to calculate the time from the current time to when an item is due.
Do I just subtract the now time from the old time...? And then divide and do some math to get minutes/seconds/days? Are there any more efficient ways to do this? Any recommendations for any 3rd party classes maybe?

webdes03

You need to convert the MySQL time into PHP time to manipulate/calculate with PHP...

Take a look at strtotime().

NogDog

Also note that if are using MySQL, it has a number of "date arithmetic" and comparison functions: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

drag0n

Thanks for the replies. I ended up just making my own function/method to calculate the difference and return it as an array of days,hours,minutes. But I am curious; how do you guys usually store dates in the db? Do you use time() in php and then store it in a varchar field? Or how else do you do it? An example would be great. I just want to store all my dates as plain unix timestamps... nothing more.

NogDog

I normally store them as DATE or DATETIME types specifically so that the date/time functions are easily usable with them (or as TIMESTAMP if I want to make use of the auto-insert/update feature associated with it).

drag0n

If I make the field DATE or DATETIME; what type of date format do I use in the INSERT sql statement?

webdes03

If you're inserting current time you can just insert NOW(). Otherwise you'd want to specify it as YYYYMMDDHHMMSSmm ("mm" would be micro-seconds).

drag0n

Is now() the same as time()?

webdes03

Without looking it up, I believe time() is just the current time, whereas now() is a full timestamp including date.

NogDog

time() is a PHP function*. NOW() is a MySQL function.

If you want the current date/time in a MySQL query:

$sql = "INSERT INTO `table_name` (`date_col`) VALUES (NOW())";

If you have a PHP timestamp you want to enter into the field:

$sql = "INSERT INTO `table_name` (`date_col`) VALUES ('".date('Y-m-d H:i:s', $time)."')";

* Actually, there is a MySQL TIME() function, but it has nothing to do with getting the current time.