The Datepart Function Alternatives in MySQL
-
the
Datepart
Function Alternatives in MySQL -
Extract Parts of a Datetime With the
Extract(datetime-part FROM datetime)
Method in MySQL -
Extract Parts of a Datetime With the
datetime-part(datetime)
Method 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.
- Using the
Extract(datetime-part FROM datetime)
method. - 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 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