Skip to content

Calculate a person's age in a MySQL query -- continued

The serendipity cache blew up on me for this article so I had to split it into 2 parts.

DATE_ADD to the rescue


MySQL knows how to work with the Gregorian calender, and I believe it is easier to let MySQL do the work, rather than trying to figure out how to implement the different cases. I chose to implement the "last day of the month" technique. In this case, we'll do this by creating a mysql date based on March 1st of the current year, and subtract one day from it to get to the last day of February.

If you read my article on "Finding next monday" using mysql date functions, you would have seen how DATE_ADD() can be used to help solve a lot of problems, even though the name can be misleading when you are actually using it to subtract. Using it with the following test dates proves that mysql has fully implemented the leap year logic accurately:

Continue reading "Calculate a person's age in a MySQL query -- continued"

Calculate a person's age in a MySQL query

Recently a question was posed on the Phpfreaks.com forums, as to the best way for someone to store a user's birth date using the MySQL database. One person suggested using a varchar as they were most familiar with string functions. My answer was to use the MySQL date type.

I provided a quick "advantages of using date" comparison list:
Storing as a DATE
•A MySQL Date requires 3 Bytes of storage
•You can do Date arithmetic inside mysql (search for dates within ranges) and use mysql functions to calculate values directly in a query
•It intrinsically will only store valid dates
•You can format it in numerous ways

Storing as a Varchar
•A string will require minimum 8 bytes, or 10 with separators
•can't do any form of efficient native range queries
•can't reformat it easily in SQL
•will allow completely invalid dates

I thought a great proof of the benefit of this approach was to show that you could have MySQL calculate the person's current age in a query, using their birthday. Certainly with a string, using PHP for example, you would typically query the database to pull out the string and for each row, turn it into a PHP date and do some calculations in your code, and you'd arrive at the same place, but I wanted to show just how capable SQL -- and in this case MySQL can be especially when you use the native data types and some functions.
Continue reading "Calculate a person's age in a MySQL query"

SQL UPDATE for strings in MongoDB

MongoDB currently does not offer the equivalent of a SQL Update statement when you want to change the value of a string to something that is based on the current value. In my case I needed to find spaces and change them to underscores -- something done easily in php with str_replace(), and in MySQL with REPLACE.

If you had a table named foo with a column named "mystr" you could do this sort of update like this:


UPDATE foo SET mystr = REPLACE(mystr, ' ', '_');
 


MongoDB does have collection.update() that works with a series of "modifier" operations, but none of them are useful for doing modifications to the current value of a document field when that field is a string.

MongoDB does however support javascript, and the javascript replace() function implements a regular expression search and replace. I was able to accomplish the update using the forEach() method to process the resulting documents one at a time. While not as efficient as having mongo do this internally with a specially built update modifier, at least this method only needs to operate on documents that are known to require modifications, and could be run in the mongo interactive client easily.

Again, assuming the collection is named "foo" and the document field is named "mystr"


db.foo.find({ mystr: /[ ]+/ }).forEach( function(u) { u.mystr = u.mystr.replace(/[ ]/g, "_"); db.foo.save(u); } );
 


An existing patch promises to add a new update() modifier but until such a time as it exists, this approach can be used when you need to update strings in a collection, referencing the existing values in the transformation.
Defined tags for this entry: , , , , , ,

Mysql Update: Null + 1 is Null!

You can't add to Null


Here's something about mysql create table definitions that can easily catch you if you aren't careful. Consider this table definition:




mysql> CREATE TABLE screenshots (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, votes INT);      
Query OK, 0 rows affected (0.09 sec)
 


What the user wanted was a simple table to keep track of user submitted screen shots. Of course the real table had a submission date, and name column but I've omitted those in order to focus on what can happen when you allow a numeric column to have NULL values.

In the application in question, when a user votes for the screen shot they like, the system should "count" the vote, by increasing the values in the "votes" column by one. Initially the developer working on this application was trying to read the value from the database, and in a PHP web script, they would increment this value and take the result and set "votes" to be equal to it in an UPDATE statement. I explained that this could cause lost votes, because if two or more users were voting at nearly the same time, each would overwrite the value of the "vote" column. In fact there are scenarios far worse than that --- a user with a cached page could vote and set the vote count back days or weeks. I didn't bother to mention the possibility that someone might recognize what was going on in the web form, and start tampering with it, since it was plainly evident that the form was passing the current number of votes.

One of the many benefits of using a relational database is built in concurrency. In an UPDATE statement, you can add to the value of the column without having to know what its original value is, just as computer languages allow assignment to a variable that references the variable's current value (ie. $a = $a + 1, $a++).

  1. UPDATE screenshots SET votes = votes + 1 WHERE id =  


All that's needed is to have the serverside language provide a value for a particular "id" and the votes will be tallied and updated correctly. Even more importantly, mysql will serialize the updates, insuring that no votes are lost.

However, given the original Mysql CREATE TABLE statement , what will happen if our code embeds the UPDATE statement provided? Continue reading "Mysql Update: Null + 1 is Null! "

Exploring Mysql CURDATE and NOW. The same but different.

Sometimes I see people attempting to use VARCHARS or CHARS to store dates in their MySQL database application. This is really fighting against MySQL, which has a variety of interchangeable date types. Internally MySQL is storing dates as numbers, which allows it to do all sorts of nice arithmetic and comparisons with very little effort on your part. For example, when you use a mysql DATE column to store a date, you don't have to worry about sortation, or comparing that date to another DATE, because MySQL already understands how to do those things internally. A lot of people also don't realize that they can output a DATE column in just about any way they choose using the DATE_FORMAT function. This causes people to shy away from using DATE, DATETIME, TIME, or TIMESTAMP columns, when they really should.

Continue reading "Exploring Mysql CURDATE and NOW. The same but different."