More Useful Queries

That was the gentle introduction. It's easy to get started with SQL, but real life queries are not always so easy. You will need to practice, explore, and practice some more before you become really confident.

Very simple queries like those above can only return data in its raw form, unchanged from the way it is stored in the database. Very often, you need to manipulate the data in some way.

Functions

Try this:

SELECT NOW()

It returns the current date and time. The () notation indicates a function that performs some action and returns a value. In this case there was no input to the function, only an output. Functions can have inputs too.

SELECT MONTHNAME(NOW())

This time we passed the output of the NOW function into the input of the MONTHNAME function, which returns the name of the month as its output.

Date and Time Functions

In databases, dates and times are usually held in a special format called a DateTime. We don't need to know how that format works inside the database, just that it is always converted to a human readable date on its way back to us. By default, the output format is "2020-07-28 15:10:37". That might not always be what we want, so SQL has a range of functions to convert dates and times to other formats. Here are some of them:

DATE_FORMAT() Format a date however you want CURRENT_DATE() Get the current date DATE_ADD() Add an interval to a date DATE_SUB() Subtract an interval from a date UNIX_TIMESTAMP() Convert a DateTime to a Unix timestamp FROM_UNIXTIME() Convert a Unix timestamp to a SQL date DATE() Extract just the date part of a DateTime

There are many more, but these will get you started. Let's have a look at a few examples.

Example 1

Let's say we have a table with columns "sdate" and "total_sales", where sdate is a "DateTime" column and num_sales is an "int" column, and we want to plot a graph of sales over time. A Plotalot line chart requires dates to be converted to Unix Timestamp format (the number of seconds since the 1st of January 1970), so to plot our chart we would need to use the query:

SELECT UNIX_TIMESTAMP(sdate), total_sales FROM my_table

Example 2

Dates and times are usually stored in databases using the DateTime column type, but not always. You might have a table where the dates are stored in Unix Time format. That would be perfect for drawing a Plotalot line chart, but what if we want to show the data as a readable table? You would need to convert the Unix dates to a readable format.

The solution is to first convert the Unix dates to SQL dates using FROM_UNIXTIME, and then send the output of FROM_UNIXTIME into the DATE_FORMAT function:

SELECT DATE_FORMAT(FROM_UNIXTIME(payment_date), "%d-%m-%Y") AS `Payment Date`

It's common to have to nest functions like this. You can lookup the syntax for the DATE_FORMAT function to get the date in the format you want. Google "mysql date_format" to find the full list.

In this example we also used the AS keyword to rename the output column to "Payment Date". If we didn't do that the table column title would be "DATE_FORMAT(FROM_UNIXTIME ... ".

Example 3

By default, chart axes are automatically adjusted to span the data being charted, but sometimes we want to force the axes to be larger than the dataset. Plotalot lets you use SQL queries to override the chart axis limits. So in a chart where the X axis represents dates, we could, for example, force the X axis to run from the beginning of a particular year to its end:

X axis start:

SELECT UNIX_TIMESTAMP(DATE('2020-01-01'))

X axis end:

SELECT UNIX_TIMESTAMP(DATE('2020-12-31'))

Or for the current year:

X axis start:

SELECT UNIX_TIMESTAMP(DATE_FORMAT(NOW(),'%Y-01-01'))

X axis end:

SELECT UNIX_TIMESTAMP(DATE_FORMAT(NOW(),'%Y-12-31'))

String Functions

SQL can also manipulate strings. There are many functions, but a few of the most useful are: CONCAT()Concatenate (join) strings together

FORMAT()Return a number as a string, formatted to a specified number of decimal places

SUBSTR()Return part of a string

Example 4

Create a title for a chart like "Hits Today (Wednesday)"

SELECT CONCAT("Hits Today (", DATE_FORMAT(CURDATE(),"%W"), ")")

Example 5

Truncate a text column to ten characters:

SELECT SUBSTR(title,1,10)

Example 6

Format a numeric column as a string:

SELECT FORMAT(price,2)