Friday, May 4, 2012

Unix timestamp in PHP and MySQL

In PHP and MySQL, we often need to compare time. As there are a lot of time formats, it is better to compare time using Unix timestamp..  Unix timestamp is the number of seconds between the Unix Epoch (January 1 1970 00:00:00 GMT) and the time specified.

 PHP function mktime returns unix timestamp, the detail is as follows:
int mktime (int $hour, int $minute, int $second, int $month, int $day, int $year, int $is_dst )

Another similar function in PHP is: int strtotime ( string $time ), which parses about string datetime description into a Unix timestamp.
$date1='2009-08-18 00:02:00'; 
In MySQL, UNIX_TIMESTAMP(time) is used to get Unix timestamp. the column variable time should have  the format: 2009-08-18 00:02:00 (y-m-d h:m:s)

To change Unix timestamp back to time string format, the PHP function date can be used. Example:
$start_date = date("Y-m-d H:i:s", $_POST['start_date']);
where  $_POST['start_date']  is Unix timestamp format and $start_date   is normal string time format.
Below is the example to select time interval in HTML form to narrow down MySQL selection.
global $db;
$search_date = mktime(0,0,0,intval($_POST['s_m']),intval($_POST['s_d']),intval($_POST['s_y']));
$search_edate = mktime(23,59,59,intval($_POST['e_m']),intval($_POST['e_d']),intval($_POST['e_y']));
$time_filter .= ' AND UNIX_TIMESTAMP(last_accessed) >= '.$search_date.' AND UNIX_TIMESTAMP(last_accessed) <= '.$search_edate;

$sql = "SELECT *, SEC_TO_TIME(duration) AS time FROM edu_member_track
WHERE member_id=$member_id AND course_id=$module_id
ORDER BY last_accessed";
$result = mysql_query($sql, $db);

No comments:

Post a Comment