Posts tagged mysql

In this article Mauricio shows us some examples of how to use the date/time features with MySQL and PHP including the UNIX timestamp and the PHP date_diff() function.Some time ago working with dates and times was a pain for me. In several of my projects I had to calculate a person’s age based on their birth date. I created a members area that allowed the administrators to set the maximum amount of time users can have a password before they must change it and remind each one how many days left before the change. I also had to create time-bomb accounts that worked for several days after its creation and then became useless.

Digging into the PHP and MySQL documentation I found and then worked with several functions that made my life easier. Now I want to share some code tips that allow better manipulation of the time and date in your projects and everyday needs.

The UNIX timestamp is the number of seconds elapsed since January 1, 1970 at 00:00:00. I use the timestamp mostly, it’s better for doing arithmetical operations as it is just a number.

For me it’s easier to add or subtract thousands of seconds than doing the same with a date string. I’m going to use both timestamps and date strings although prefer the first.

Printing the current timestamp is as simple as . This function alone doesn’t do much, the result is a number with more than nine figures, almost unreadable for the average user including me. I’m not going to enter into details about this topic as it has been covered in previous articles. Let’s go to the examples…

Calculating days, hours and seconds

PHP lacks date operation functions, but it provides the tools to do what we want to do. Say we want to know how many days, hours and seconds exist between two given dates, following is the function:

// The parameters of this function are the dates to be compared.
// The first should be prior to the second. The dates are in
// the form of: 1978-04-26 02:00:00.
// They also can come from a web form using the global $_POST['start']
// and $_POST['end'] variables.
function date_diff($str_start, $str_end)
{

$str_start = strtotime($str_start); // The start date becomes a timestamp
$str_end = strtotime($str_end); // The end date becomes a timestamp

$nseconds = $str_end – $str_start; // Number of seconds between the two dates
$ndays = round($nseconds / 86400); // One day has 86400 seconds
$nseconds = $nseconds % 86400; // The remainder from the operation
$nhours = round($nseconds / 3600); // One hour has 3600 seconds
$nseconds = $nseconds % 3600;
$nminutes = round($nseconds / 60); // One minute has 60 seconds, duh!
$nseconds = $nseconds % 60;

echo $ndays.” days, “.$nhours.” hours, “.$nminutes.” minutes, “.$nseconds.”
echo “seconds
“;

}

// Test the function with several values
date_diff(“1978-04-26″, “2003-01-01″);
date_diff(“1984-10-24 15:32:25″, “2003-01-01″);
date_diff(“2001-10-28 17:32:25″, “2003-01-01 12:00:18″);

?>

We could implement more options such as date validation etc., but I will add that in future articles.
This function does not need a lot of explanation, just multiplication and division. Now, let’s seeanother example.

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.

Suppose that you have a members only area, each user has a password but you want the user to change his or her password every two weeks.

The first thing you have to do is add a new column to your users table; this column will store the timestamp of the exact date when the user changed his or her password.

Remember, again, that the timestamp is an unsigned integer, so keep this in mind when creating the table.

The code for this approach should look like this:

$ndbconn = mysql_connect(“localhost”, “user”, “password”);
mysql_select_db(“mysql”);

$n_start_date = 1062521254; // Timestamp for 2003-09-02 11:47:34 taken from your DB

// The query adds fourteen days to the given date
$squery = “SELECT DATE_ADD(FROM_UNIXTIME($n_start_date), INTERVAL 14 DAY)”;
$nresult = mysql_query($squery);
$s_new_date = mysql_result($nresult, 0, 0);

// Free the result
mysql_free_result($nresult);

// Disconnected
mysql_close($ndbconn);
echo “Your password will expire on $s_new_date”;

?>

The main function here is DATE_ADD(), this MySQL function perfectly adds any number of seconds, minutes, hours, days or years to a specific date.

In this example we used it to calculate the exact time fourteen days after the selected date. You can use this function with no risk, it even takes care of leap years.
For example:

mysql> SELECT DATE_ADD(’2004-02-28 23:50:00′, INTERVAL 10 MINUTE); #2004 is a leap year
+—————————————————–+
| DATE_ADD(’2004-02-28 23:50:00′, INTERVAL 10 MINUTE) |
+—————————————————–+
| 2004-02-29 00:00:00 |
+—————————————————–+

Subtraction is done with the DATE_SUB() function in the same way:

mysql> SELECT DATE_SUB(’2004-03-01′, INTERVAL 1 DAY);
+—————————————-+
| DATE_SUB(’2004-03-01′, INTERVAL 1 DAY) |
+—————————————-+
| 2004-02-29 |
+—————————————-+

And don’t worry about December 31st and January 1st transition:

mysql> SELECT DATE_SUB(’2004-01-01′, INTERVAL 1 DAY);
+—————————————-+
| DATE_SUB(’2004-01-01′, INTERVAL 1 DAY) |
+—————————————-+
| 2003-12-31 |
+—————————————-+

My conclusion is there really is no conclusion, date and time are extremely important in real-world applications, for example, calculating the age of a person or calculating how many days left for a loan to expire or a bill to be due.

This is a huge topic that I’ve always been interested in. I said there’s no conclusion because I’ll be posting more articles about this topic with more robust and useful applications. Thanks for your time, I hope you enjoyed it.