• Choose a category
• All
• Classic-Fiction

Figure 4-1: The hero table after normali-zation.

The original idea for the database, introduced in Table 3-1 in Chapter 3 of this minibook, was to keep track of each hero’s age. This idea was bad because the age changes every year. Instead, I stored the hero’s birthday. But what if you really do want the age?

Introducing SQL Functions

It turns out SQL supports a number of useful functions that you can use to manipulate data. Table 4-1 shows especially useful MySQL functions. Many more functions are available, but these functions are the most frequently used.

Table 4-1 Useful MySQL Functions

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.

Knowing 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.

Calculating Date Values

The birthday value is stored in the hero table, but what you really want to know is the hero’s age. It’s very common to have a date stored in a database. You often need to calculate the time from that date to the current date in years, or perhaps in years and months. Functions can help you do these calculations.

Begin by looking at a simple function that tells you the current date and time, as I do in Figure 4-2.

Figure 4-2: The NOW() function returns the current date and time.

The current date and time by themselves aren’t that important, but you can combine this information with other functions, described in the following sections, to do some very interesting things.

Using DATEDIFF to determine age

The NOW() function is very handy when you combine it with the DATEDIFF() function, as shown in Figure 4-3.

This query calculates the difference between the current date, NOW(), and each hero’s birthday. The DATEDIFF() function works by converting both dates into integers. It can then subtract the two integers, giving you the result in number of days.

Figure 4-3: The DATEDIFF() function determines the difference between dates.

You normally name the fields you calculate because, otherwise, the formula used to calculate the results becomes the virtual field’s name. The user doesn’t care about the formula, so use the AS feature to give the virtual field a more useful name.

Adding a calculation to get years

Of course, most people