MySQL

MySQL Datasource Connector Details

MySQL Overview

MySQL is a flexible database for transaction and analytical workloads. ChartSQL can connect to any MySQL database like MySQL Server, MariaDB, Planetscale, and any other MySQL compatible wire protocol database.

Chart Data Type Casting

ChartSQL detects four types of columns: Date, Datetime, String, Numeric. It maps all MySQL types to one of these four chart data types.

If you need to manualy specify the type, you can follow these examples

Date

1. Using CAST:

SELECT CAST('2021-01-01' AS DATE);

2. Using STR_TO_DATE for custom formats:

SELECT STR_TO_DATE('01-01-2021', '%d-%m-%Y');

Datetime

Using CAST:

SELECT CAST('2021-01-01 13:25:20' AS DATETIME);

Using STR_TO_DATE with custom formats:

SELECT STR_TO_DATE('01-01-2021 01:25:20 PM', '%d-%m-%Y %r');

String

Using CAST:

SELECT CAST(column_name AS CHAR) FROM table_name;

Using implicit conversion with concatenation:

SELECT column_name + '' FROM table_name; 
-- MySQL treats + as string concatenation in this context, implicitly converting column_name to a string

Or by concatenation with the CONCAT function:

SELECT CONCAT(column_name, '') FROM table_name; 
-- CONCAT function automatically converts and concatenates the values as strings

These methods work for converting numbers or dates to strings. When converting dates or times to strings, you can also format them using the DATE_FORMAT function:

SELECT DATE_FORMAT(date_column, '%Y-%m-%d %H:%i:%s') FROM table_name; -- Formats a date/datetime value as a string

Use the DATE_FORMAT function if you need specific formatting for your date or datetime values.

Numeric

To cast strings or other types of data to numbers in MySQL, you can use CAST or CONVERT. Here are examples of how to cast to integer (SIGNED) or decimal (DECIMAL) numbers:

Casting to an integer (whole number):

SELECT CAST(column_name AS SIGNED) FROM table_name;

Casting to a decimal (floating-point number):

SELECT CAST(column_name AS DECIMAL(10,2)) FROM table_name; -- Where 10 is the precision and 2 is the scale

These functions are appropriate for converting from string representations of numbers or from real or floating-point data types to integers or decimals. If your string has non-numeric characters or improper formatting, the conversion will result in a zero (0) in CAST functions.

For an implicit conversion, you can use an arithmetic operation such as addition or subtraction with a number, which will coerce the string to a numeric type:

SELECT column_name + 0 FROM table_name; -- Converts to a number by adding zero

This method will work best when you are confident that the string can be converted directly to a number without the need for specifying the exact numeric type (integer or decimal).

MySQL Data Tips

Visualization tips for working with MySQL.

Summarizing by Month

When you want summarize data by month, it is helpful to normalize your dates to the month floor (1st of the month) so that you can group all of your records into that month:

SELECT DATE_FORMAT(SaleDate, '%Y-%m-01') as MonthStart, sum(sales) as MonthSales
FROM SalesData
GROUP BY DATE_FORMAT(SaleDate, '%Y-%m-01')