JIYIK CN >

The SQL datepartfunction extracts a part of the datetime data type and is used to filter or aggregate table fields in SQL databases. However, it is not directly available in MySQL.

DatepartFunction alternatives in MySQL

datepartThere are two ways to achieve similar results in MySQL .

1. Usage Extract(datetime-part FROM datetime)method.
2. Usage datetime-part(datetime)method.

Recall that the datetime data type is a combination/concatenation of the typical date and time data types available in MySQL. Dates and times usually have their own formats: YYYY-MM-DDand HH:MM:SS.XXXXXX.

Therefore, the datetime data type has YYYY-MM-DD HH:MM:SS.XXXXXXthe format , where .XXXXXXrepresents the 6-digit fractional seconds precision (fps) available in MySQL.

Now, let's create a simple registration_systemdatabase to implement 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 table we created.

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

This is the view created on the users table.

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

Use the MySQL Extract(datetime-part FROM datetime)method to extract part of the date and time

SELECTThis method is combined with the ubiquitous statement.

datetime-partThe argument can be any of the following datetimeparts: 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 for these options for additional reference.

Let's take an example. We will fetch the details of users who registered before 30th 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 tells you two things: Extractthe function changes the output view and filters the results.

Many applications can then use this by writing very advanced queries that manipulate datetimes. For additional reference on this feature, check out the official documentation.

Use the MySQL datetime-part(datetime)method to extract part of the date and time

This method is similar to the previous one, but has fewer parameters. In addition, combined with SELECTthe statement, this method can filter columns based on the required datetime part.

Likewise, let's get the users who registered before January 30th.

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 be further improved by using Datefilters, as shown in the following example.

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 provides the same result. However, Extractmethods can more clearly describe the actions performed.

This approach is preferred for code readability and debugging.