How to use DATE_FORMAT() to reformat dates in MySQL

Most SQL databases store dates in the datetime format. This is really useful because MySQL, and other SQL dialects, make it very easy to convert datetime objects to a wide range of other date and time formats.

If you’re a data scientist working with MySQL data, you may wish to just stick to the datetime format and reformat the dates in Pandas, however, it’s very easy to reformat dates in MySQL too. Here’s a quick guide to getting started.

Setting up your MySQL server

I run my MySQL server in a Docker container and have created a test database based on the widely used Online Retail Dataset. I’ve created a couple of step-by-step guides that you can follow to create your own MySQL Docker container and import a MySQL database so you can follow along on the very same data. It only takes a few minutes.

Install and load the packages

Once you’ve got your MySQL server setup, open up a Jupyter notebook and install the SQLAlchemy and PyMySQL Python packages. We’ll be using these to connect to MySQL and pass in database queries using Pandas. Once you’ve installed the packages using the Pip package manager, import pandas and the create_engine module from sqlalchemy.

!pip3 install sqlalchemy
!pip3 install pymysql

import pandas as pd
from sqlalchemy import create_engine

Create a database connection

Next, we need to configure the connection to our MySQL server by creating a connection string to pass to the create_engine() function. This will vary according to your own setup. I’m connecting to a MySQL server with the PyMySQL driver, using the username root and the password ***** on port 3306 of the IP address 172.17.0.3. The final argument tells create_engine() to connect to the ecommerce database.

engine = create_engine('mysql+pymysql://root:*****@172.17.0.2:3306/ecommerce')

SELECT FROM an SQL table

To test the connection to the MySQL server we’ll first write a simple MySQL SELECT statement to return everything from the order_items table.

df = pd.read_sql_query("SELECT * FROM order_items", con=engine)
df.head()

df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 541909 entries, 0 to 541908
Data columns (total 9 columns):
 #   Column       Non-Null Count   Dtype         
---  ------       --------------   -----         
 0   invoice      541909 non-null  object        
 1   sku          541909 non-null  object        
 2   description  540455 non-null  object        
 3   qty          541909 non-null  int64         
 4   order_date   541909 non-null  datetime64[ns]
 5   unit_price   541909 non-null  float64       
 6   customer_id  406829 non-null  float64       
 7   country      541909 non-null  object        
 8   line_price   541909 non-null  float64       
dtypes: datetime64[ns](1), float64(3), int64(1), object(4)
memory usage: 37.2+ MB

How to use DATE_FORMAT() to reformat a date

The DATE_FORMAT() function makes it very easy to reformat a datetime column in MySQL into another format, including a complex string representation of a date, such as “Wednesday, April 20th, 2022”. The DATE_FORMAT() function takes two arguments - the MySQL column name containing the datetime you wish to reformat and a date modifier argument, such as %Y, which will convert a datetime to a four-digit year. Here’s a quick example.

query = """
SELECT 
    invoice, 
    order_date,
    DATE_FORMAT(order_date, '%Y') AS year
FROM order_items
"""

df = pd.read_sql_query(query, con=engine)
df.head()

MySQL DATE_FORMAT arguments

All you need to do to reformat a datetime to another date format is pass in one or more MySQL date formatter or “specifier” arguments, and MySQL will parse the date and reformat it as desired. There’s a huge selection of date formatters or specifiers that can be used either on their own or together to create a whole myriad of different date representations.

Reformat a date to MySQL YYYY-MM-DD format

One common problem is that you may want to reformat a datetime string to a MySQL date string, which are stored in the YYYY-MM-DD format, with no time value at the end. To do this you need to select the correct three date specifiers from the table above and then separate the values with hyphens, so %Y-%m-%d will return 2010-12-01 from a datetime value of 2010-12-01 08:26:00.

query = """
SELECT 
    invoice, 
    order_date,
    DATE_FORMAT(order_date, '%Y-%m-%d') AS mysql_date
FROM order_items
"""

df = pd.read_sql_query(query, con=engine)
df.head()

Get the day from the date

There are various date formatters you can apply to DATE_FORMAT() to return the day from a date in MySQL. The %W specifier will return the day name, i.e. Wednesday; the %a specifier will return the abbreviated day name, i.e. Wed; the %w specifier will return the day of the month with a leading zero, i.e. 01; the %d specifier will return the day of the month as a numeric value without a leading zero, i.e. 10, while the %j specifier will return the day of the year, i.e. 335.

query = """
SELECT 
    invoice, 
    order_date,
    DATE_FORMAT(order_date, '%W') AS day_name, 
    DATE_FORMAT(order_date, '%a') AS abbreviated_day_name, 
    DATE_FORMAT(order_date, '%w') AS day_of_week,
    DATE_FORMAT(order_date, '%d') AS day_of_month,
    DATE_FORMAT(order_date, '%e') AS day_of_month_numeric,
    DATE_FORMAT(order_date, '%j') AS day_of_year
FROM order_items
"""

df = pd.read_sql_query(query, con=engine)
df.head()

Get the month name and number from the date

There are three DATE_FORMAT() specifiers you can use to convert a date into month name or month number representations. The %M specifier will return the month name from a date, i.e. December; the %b specifier will return the abbreviated month name from a date, i.e. Dec for December, while the %m specifier will return the month number from the date, i.e. 12 for December.

query = """
SELECT 
    invoice, 
    order_date,
    DATE_FORMAT(order_date, '%M') AS month_name,
    DATE_FORMAT(order_date, '%b') AS abbreviated_month_name,
    DATE_FORMAT(order_date, '%m') AS month_number
FROM order_items
"""

df = pd.read_sql_query(query, con=engine)
df.head()

Create the written form of a date

As we saw above with the YYYY-MM-DD MySQL date example, you can also mix your own strings to the date specifiers to create more complex date formats. Here’s an example of a written version of a date and time that converts a standard datetime to a more verbose written format.

query = """
SELECT 
    invoice, 
    order_date,
    DATE_FORMAT(order_date, '%W, %M %D, %Y, at %r') AS mysql_date
FROM order_items
"""

df = pd.read_sql_query(query, con=engine)
df.sample(5)

Matt Clarke, Tuesday, April 19, 2022