Part of SQL data normalization means that you eliminate fields that can be calculated for HTML5 and CSS3 programming. In the hero database, data normalization meant that you don't store the hero's age, but his or her birthday instead. Of course, if you really want the age, you should be able to find some way to calculate it. SQL includes support for calculating results right in the query.
The original idea for the database was to keep track of each hero's age. This idea was bad because the age changes every year. Instead, you can store the hero's birthday. But what if you really do want the age?
SQL functions
It turns out SQL supports a number of useful functions that you can use to manipulate date and time data. Many more functions are available, but these functions are the most frequently used.
| Function | Description |
|---|---|
| CONCAT(A, B) | Concatenates two string results. Can be used to create a single entry from two or more fields. For example, combine firstName and lastName fields. |
| FORMAT(X, D) | Formats the number X to the number of digits D. |
| CURRDATE(), CURRTIME() | Returns the current date or time. |
| NOW() | Returns the current date and time. |
| MONTH(), DAY(), YEAR(), WEEK(), WEEKDAY() | Extracts the particular value from a date value. |
| HOUR(), MINUTE(), SECOND() | Extracts the particular value from a time value. |
| DATEDIFF(A, B) | Frequently used to find the time difference between two events (age). |
| SUBTIMES(A, B) | Determines the difference between two times. |
| FROMDAYS(INT) | Converts an integer number of days into a date value. |
Typically, you use a programming language, such as PHP, to manage what the user sees, and programming languages tend to have a much richer set of functions than the database. Still, it's often useful to do certain kinds of functionality at the database level.
When to calculate virtual fields
You calculate data in these situations:
You need to create a single field from multiple text fields. You might need to combine first, middle, and last name fields to create a single name value. You can also combine all the elements of an address to create a single output.
You want to do a mathematical operation on your data. Imagine that you're writing a database for a vegetable market and you want to calculate the value from the costPerPound field plus the poundsPurchased field. You can include the mathematical operation in your query.
You need to convert data. Perhaps you stored weight information in pounds and you want a query to return data in kilograms.
You want to do date calculations. Often, you need to calculate ages from specific days. Date calculations are especially useful on the data side because databases and other languages often have different date formats.