The Datepart Function Alternatives in MySQL

Victor A. Oguntuase Feb 09, 2022
  1. the Datepart Function Alternatives in MySQL
  2. Extract Parts of a Datetime With the Extract(datetime-part FROM datetime) Method in MySQL
  3. Extract Parts of a Datetime With the datetime-part(datetime) Method in MySQL
The Datepart Function Alternatives in MySQL

The SQL datepart function extracts parts of a datetime datatype for filtering or aggregating table fields in a SQL database. However, it is not directly available in MySQL.

the Datepart Function Alternatives in MySQL

There are two ways to achieve a similar result as datepart in MySQL.

  1. Using the Extract(datetime-part FROM datetime) method.
  2. Using the datetime-part(datetime) method.

As a recall, the datetime datatype is a combination/concatenation of the typical date and time data types available in MySQL. The date and time typically have the respective formats: YYYY-MM-DD and HH:MM:SS.XXXXXX.

Hence, the datetime datatype has the YYYY-MM-DD HH:MM:SS.XXXXXX format where .XXXXXX represents the 6-digit fractional seconds precision (fps) available in MySQL.

Now, let us create a simple registration_system database for implementing the two datetime extraction methods.

-- Initializing
CREATE DATABASE registration_system;
USE registration_system;

-- CREATING TABLES
CREATE TABLE registered_users (
	id INT AUTO_INCREMENT UNIQUE,
    username VARCHAR (255) NOT NULL,
    email VARCHAR(255),
    registered_on DATETIME(6),	-- YYYY-MM-DD HH:MM:SS.XXXXXX
    PRIMARY KEY(id)
);

Output:

1 row(s) affected
-----------------------------------------------------------------------------------------
0 row(s) affected
-----------------------------------------------------------------------------------------
0 row(s) affected

We can simulate a set of registered users by inserting values into the created table.

-- POPULATING THE TABLE WITH SAMPLE REGISTRATION DATA: 3 users
INSERT INTO registered_users(username, email, registered_on) Values
	('Clint Rotterdam', 'clint_rotterdam@trialmail.com','2022-01-03 09:25:23.230872'),
    ('David Maxim', 'maxim_david@testmail.com','2022-01-05 10:30:24.046721'),
	('Vin Petrol', 'vin_not_diesel@crudemail.com','2022-01-30 14:05:03.332891');

Output:

3 row(s) affected Records: 3  Duplicates: 0  Warnings: 0

Here is a view of the created table of users.

SELECT * FROM registered_users;    -- Checking the table

Output:

id	username			email								registered_on
1	Clint Rotterdam		clint_rotterdam@trialmail.com		2022-01-03 09:25:23.230872
2	David Maxim			maxim_david@testmail.com			2022-01-05 10:30:24.046721
3	Vin Petrol			vin_not_diesel@crudemail.com		2022-01-30 14:05:03.332891
-----------------------------------------------------------------------------------------
3 row(s) returned

Extract Parts of a Datetime With the Extract(datetime-part FROM datetime) Method in MySQL

This method combines with the ubiquitous SELECT statement.

The datetime-part argument can be any of the following datetime parts: MONTH, MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND, HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE, DAY_MICROSECOND, DAY_SECOND, DAY_MINUTE, DAY_HOUR, YEAR_MONTH.

Here is the official documentation of these options for extra reference.

Let us take an example. We will grab details of users registered before the 30th of January from the database.

SELECT id AS 'USER_ID', username AS 'NAME', EXTRACT(DAY FROM registered_on) AS 'Day Registered'
FROM registered_users
WHERE EXTRACT(DAY FROM registered_on) < 30;

Output:

USER_ID		NAME				Day Registered
1			Clint Rotterdam		3
2			David Maxim			5
-----------------------------------------------------------------------------------------
2 row(s) returned

This output informs of two things. The Extract function has changed the view of the output and filtered the result.

Many applications are then available by writing significantly advanced queries that manipulate datetime. For extra reference on this function, check out the official documentation.

Extract Parts of a Datetime With the datetime-part(datetime) Method in MySQL

This method is similar to the former but with fewer arguments. Also, in conjunction with the SELECT statement, this method can filter columns based on the desired datetime part.

Again, let us fetch users registered before the 30th of January.

SELECT id AS 'USER_ID', username AS 'NAME', DAY(registered_on) AS 'Day Registered'
FROM registered_users
WHERE DAY(registered_on) < 30;

Output:

USER_ID		NAME				Day Registered
1			Clint Rotterdam		3
2			David Maxim			5
-----------------------------------------------------------------------------------------
2 row(s) returned

This result can further improve, as in the following example, by using the Date filter.

SELECT id AS 'USER_ID', username AS 'NAME', DATE(registered_on) AS 'Date Registered' FROM registered_users
WHERE DAY(registered_on) < 30;

Output:

USER_ID		NAME				Date Registered
1			Clint Rotterdam		2022-01-03
2			David Maxim			2022-01-05
-----------------------------------------------------------------------------------------
2 row(s) returned

Either method offers the same results. However, the Extract method gives better clarity on the executed operation.

This approach is preferred for code readability and debugging.

Victor A. Oguntuase avatar Victor A. Oguntuase avatar

Victor is an experienced Python Developer, Machine Learning Engineer and Technical Writer with interests across various fields of science and engineering. He is passionate about learning new technologies and skill and working on challenging problems. He enjoys teaching, intellectual discourse, and gaming, among other things.

LinkedIn GitHub

Related Article - MySQL Function