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"
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"