Practical Date/Time examples with PHP and MySQL - How old are you? (exactly) (
Page 3 of 5 )
Using the date_diff() function that we've just created it's very easy to calculate
your exact age by how many years, days, hours, minutes and seconds since you were
born:
// Calculating my exact age
$str_birthday = "1978-04-26"; // My Birth Day
$str_today = date("Y-m-d H:i:s"); // The exact time
date_diff($str_birthday, $str_today);
?>
As you see I'm using the same function but changing the parameters a little.
First $str_birthday is a fixed string, in this example my birthday.
You can, for example, ask the user through a form and then call the function
like this:
The trick here is the date() function. With its given parameters prints the exact
date of today in the format YYYY-MM-DD hh:mm:ss.
The code returns:
9024 days, 17 hours, 29 minutes, 57 seconds
How many days until…?
Again, using the same function that we created at the beginning and changing
the parameters appropriately we can calculate how many days until a given date.
There is a problem though; the UNIX timestamp only works for dates between January
1st, 1970 and December 31st, 2037. Dates outside that range will give an unpredictable
result.
Now, hoping that we'll come up with a solution for this in the next thirty years
let's do the math:
$str_future_date = "2028-04-26"; // I'll be fifty this day!
date_diff(date("Y-m-d H:i:s"), $str_future_date);
?>
At this point you should be familiar with the function, just tweaking the parametersa
little. In this case the first parameter is the exact date of today and the second
is the future date that we want to know how many days until.{mospagebreak title=Dates
with MySQL&toc=1} MySQL has several great date and time functions that are
very useful when you are working with dates. These functions are worth another
article which I'll be writing after this one.
Meanwhile I'll show the ones that I think are the more useful and interesting.
Also, forcing MySQL to do the date operations by itself saves a lot of time,
a lot of code and increases the performance of your application.
Personally, every time that I need to do some date calculations involving dates
contained in a database I take a look at MySQL documentation to see which function
can help me and let it do the job for me.
In all the following examples the dates can be taken from the database itself,
your code or user input.
Anyway, if you are working with databases it's obvious that at least one of the
dates came from there.
Take care when using these functions as most of them work only on MySQL 3.22
and later. If you have doubts refer to the documentation.
The UNIX timestamp revisited
Programmers seem to like the UNIX timestamp very much because you can see it
anywhere you go. In the case of MySQL you can use it like this:
SELECT UNIX_TIMESTAMP();
This will return the current timestamp.
mysql> SELECT UNIX_TIMESTAMP('1978-04-26 02:12:59');
+---------------------------------------+
| UNIX_TIMESTAMP('1978-04-26 02:12:59') |
+---------------------------------------+
| 262422779 |
+---------------------------------------+
mysql> SELECT UNIX_TIMESTAMP('1978-04-26');
+------------------------------+
| UNIX_TIMESTAMP('1978-04-26') |
+------------------------------+
| 262414800 |
+------------------------------+
This will print the timestamp of the given date.
Be careful when creating the structure and definitions for this kind of table.
MySQL returns the UNIX timestamp as an unsigned integer, so keep this in mind
when creating your database.
Now, imagine you have a database with a column that contains a UNIX timestamp,
you can also convert it to a 'human-readable' date using MySQL like this:
mysql> SELECT FROM_UNIXTIME('262422779');
+----------------------------+
| FROM_UNIXTIME('262422779') |
+----------------------------+
| 1978-04-26 02:12:59 |
+----------------------------+
With this value it's just a matter of two lines of code and to use the function
date_diff() that I created before:
// Working with mysql
$ndbconn = mysql_connect("localhost", "user", "password");
mysql_select_db("mydb");
// The query
$squery = "SELECT FROM_UNIXTIME('262422779'), NOW()";
$nresult = mysql_query($squery);
$s_given_date = mysql_result($nresult, 0, 0);
$s_curr_date = mysql_result($nresult, 0, 1);
// Free the result
mysql_free_result($nresult);
// Disconnected
mysql_close($ndbconn);
// The date difference
date_diff($s_given_date, $s_curr_date);
In this example I used MySQL to obtain the dates and then I calculated the time
elapsed between both using my - now 'famous' - date_diff() function.
The UNIX timestamp can be any you have in your database, I selected the timestamp
262422779 that is exactly my 'birth time' and stored it in the $s_given_date variable.
Then I selected the current time with the NOW() function and stored it in the
$s_curr_date variable. The rest is old news, I already explained how the function
works.
/ 74 
