Date and time values are some of the most important datatypes in an RDBMS. From tracking order dates to payroll hours, DATE and DATETIME datatypes are used in all types of applications. At times as a Developer, you may need only certain portions of a DATE or DATETIME value. In MySQL, the EXTRACT() function can provide you with a specific component of a DATE or DATETIME value depending on which INTERVAL is given as a parameter. Continue reading to see example EXTRACT() queries for understanding…
OS, Software, and DB used:
- OpenSuse Leap 15.1
- MySQL 8.0.20
Self-Promotion:
If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!
I’ll use the SET command and assign a session variable the CURRENT_TIMESTAMP value to that of the date this post’ examples were written:
1 | MySQL localhost:33060+ ssl learning SQL > SET @a_date = CURRENT_TIMESTAMP; |
1 2 3 4 5 6 7 | MySQL localhost:33060+ ssl learning SQL > SELECT @a_date; +---------------------+ | @a_date | +---------------------+ | 2020-05-30 09:09:23 | +---------------------+ 1 row in set (0.0008 sec) |
| 2020-05-30 09:09:23 |
EXTRACT() accepts one of several available temporal-interval parameters, returning either the DATE or DATETIME value depending on the target datatype.
EXTRACT‘s basic syntax is:
1 | EXTRACT(some_interval FROM date_or_datetime_value) |
Need only the 4-digit year value? Simply pass in the YEAR temporal-interval as the parameter:
1 2 3 4 5 6 7 | MySQL localhost:33060+ ssl learning SQL > SELECT EXTRACT(YEAR FROM @a_date) AS yr; +------+ | yr | +------+ | 2020 | +------+ 1 row in set (0.0003 sec) |
For a digit month, use the same-named MONTH interval:
1 2 3 4 5 6 7 | MySQL localhost:33060+ ssl learning SQL > SELECT EXTRACT(MONTH FROM @a_date) AS mnth; +------+ | mnth | +------+ | 5 | +------+ 1 row in set (0.0006 sec) |
For the day of the month, there is the DAY interval:
1 2 3 4 5 6 7 | MySQL localhost:33060+ ssl learning SQL > SELECT EXTRACT(DAY FROM @a_date) AS dy; +----+ | dy | +----+ | 30 | +----+ 1 row in set (0.0006 sec) |
Since the @a_date session variable holds data derived from CURRENT_TIMESTAMP, we can also access hours, minutes, and seconds values with EXTRACT().
All three keywords are named exactly the same as the unit of time returned from EXTRACT() when providing any one of them as the parameter with a DATETIME datatype:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | MySQL localhost:33060+ ssl learning SQL > SELECT EXTRACT(HOUR FROM @a_date) AS hr; +----+ | hr | +----+ | 9 | +----+ 1 row in set (0.0006 sec) MySQL localhost:33060+ ssl learning SQL > SELECT EXTRACT(MINUTE FROM @a_date) AS mn; +----+ | mn | +----+ | 9 | +----+ 1 row in set (0.0005 sec) MySQL localhost:33060+ ssl learning SQL > SELECT EXTRACT(SECOND FROM @a_date) AS sec; +----+ | mn | +----+ | 23 | +----+ 1 row in set (0.0005 sec) |
Although hour, minute, and second intervals are available in a DATETIME value, they are not found in a DATE value.
I’ll assign another session variable to the CURRENT_DATE (as of this draft):
1 2 3 4 5 6 7 8 9 | MySQL localhost:33060+ ssl learning SQL > SET @z_date = CURRENT_DATE; Query OK, 0 rows affected (0.0234 sec) MySQL localhost:33060+ ssl learning SQL > SELECT @z_date; +------------+ | @z_date | +------------+ | 2020-05-30 | +------------+ 1 row in set (0.0005 sec) |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | MySQL localhost:33060+ ssl learning SQL > SELECT EXTRACT(HOUR FROM @z_date) AS hr; +----+ | hr | +----+ | 0 | +----+ 1 row in set, 1 warning (0.0011 sec) Warning (code 1292): Truncated incorrect time value: '2020-05-30' MySQL localhost:33060+ ssl learning SQL > SELECT EXTRACT(MINUTE FROM @z_date) AS mn; +----+ | mn | +----+ | 20 | +----+ 1 row in set, 1 warning (0.0010 sec) Warning (code 1292): Truncated incorrect time value: '2020-05-30' MySQL localhost:33060+ ssl learning SQL > SELECT EXTRACT(SECOND FROM @z_date) AS sec; +-----+ | sec | +-----+ | 20 | +-----+ 1 row in set, 1 warning (0.0008 sec) Warning (code 1292): Truncated incorrect time value: '2020-05-30' |
Attempting to extract timestamp values from a DATE value, we can see from the above warning issued Truncated incorrect time value: '2020-05-30'.
More Options
Many of the interval values return year-kin types of values.
WEEK returns the number week in the year it is:
1 2 3 4 5 6 7 | MySQL localhost:33060+ ssl learning SQL > SELECT EXTRACT(WEEK FROM @z_date) AS wk; +----+ | wk | +----+ | 21 | +----+ 1 row in set (0.0006 sec) |
QUARTER provides which quarter (1-4) a DATE belongs:
1 2 3 4 5 6 7 | MySQL localhost:33060+ ssl learning SQL > SELECT EXTRACT(QUARTER FROM @z_date) AS qtr; +-----+ | qtr | +-----+ | 2 | +-----+ 1 row in set (0.0006 sec) |
The ‘@a_date’ session variable is of type TIMESTAMP, and has hours, minutes, and seconds values. We can access many of them as a unit with EXTRACT().
1 2 3 4 5 6 7 | MySQL localhost:33060+ ssl learning SQL > SELECT EXTRACT(HOUR_MINUTE FROM @a_date) AS hr_min; +--------+ | hr_min | +--------+ | 909 | +--------+ 1 row in set (0.0009 sec) |
HOUR_SECOND returns ‘HOURS:MINUTES:SECONDS’:
1 2 3 4 5 6 7 | MySQL localhost:33060+ ssl learning SQL > SELECT EXTRACT(HOUR_SECOND FROM @a_date) AS hr_sec; +--------+ | hr_sec | +--------+ | 90923 | +--------+ 1 row in set (0.0007 sec) |
1 2 3 4 5 6 7 | MySQL localhost:33060+ ssl learning SQL > SELECT EXTRACT(DAY_HOUR FROM @a_date) AS day_hr; +--------+ | day_hr | +--------+ | 3009 | +--------+ 1 row in set (0.0007 sec) |
DAY_MINUTE returns ‘DAYS HOURS:MINUTES’:
1 2 3 4 5 6 7 | MySQL localhost:33060+ ssl learning SQL > SELECT EXTRACT(DAY_MINUTE FROM @a_date) AS day_min; +---------+ | day_min | +---------+ | 300909 | +---------+ 1 row in set (0.0007 sec) |
Resources
Be sure and visit these official documentation links on EXTRACT() and Temporal Intervals for more information:
In what ways have you used EXTRACT() in your workflow? Tell me about it in the comments below.
Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Have I mentioned how much I love a cup of coffee?!?!
To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.