Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

Extracting date and time components in MySQL – Querychat

MySQL has several native functions that allow us to extract different date and time components from a DATE, DATETIME, or TIMESTAMP field. Let’s see a list of these functions and how they work.

Common Functions in MySQL for Date and Time fields

CURRENT_DATE()

CURRENT_TIME()

CURRENT_TIMESTAMP()

DATE()

DAY()

DAYNAME()

DAYOFWEEK()

DAYOFYEAR()

MONTH()

YEAR()

EXTRACT()

In this article, we illustrate the following functions in MySQL to extract the date and time components from DATE, DATETIME, and TIMESTAMP fields.

CURRENT_DATE()

As its name says, this function returns the current date. The format in which the date is returned is YYYY-MM-DD.

Examples

SELECT CURRENT_DATE();

Result:

2019-05-14 (if today is May 14, 2019)

We can also add or subtract days to this query as follows:

SELECT DATE_ADD(CURRENT_DATE, INTERVAL 5 DAY)

Result:

2019-05-19

SELECT DATE_SUB(CURRENT_DATE, INTERVAL 5 DAY)

Result:

2019-05-09

Note: When we add or remove days, the function returns the result in the format YYYYMMDD.

CURRENT_TIME()

Similar to CURRENT_DATE() but this function returns the current time instead of the date. The format the function uses to return time is HH-MM-SS, in a 24-hour time format.

Examples

SELECT CURRENT_TIME();

Result:

09:30:34

SELECT DATE_ADD(CURRENT_TIME, INTERVAL 10 MINUTE)

Result: Current time plus 10 minutes

09:40:34

SELECT DATE_SUB(CURRENT_TIME, INTERVAL 10 MINUTE)

Result: Current time minus 10 minutes

09:20:34

CURRENT_TIMESTAMP()

We can consider this function as the union of CURRENT_DATE and CURRENT_TIME functions because returns the date and time together.

Examples

SELECT CURRENT_TIMESTAMP();

Result:

2019-05-14 09:30:34

SELECT DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 10 DAY)

Result:

2019-05-24 09:30:34

SELECT DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 10 DAY)

Result:

2019-05-04 09:30:34

DATE()

This function returns the date from a timestamp.

Example

SELECT DATE('2019-05-14 09:30:34');

Result:

2019-05-14

DAY()

This function returns the day from a date field or timestamp.

Examples

SELECT DAY('2019-05-14 09:30:34');

Result:

14

SELECT DAY('2019-05-14');

Result:

14

DAYNAME()

This function returns name of the day in letters from a date.

Example

SELECT DAYNAME('2019-05-14 09:30:34');

Result:

Tuesday

DAYOFWEEK()

This function returns a number that represents a day of the week. The numbers go from 1 to 7, where 1 is Sunday and 7 is Saturday.

Example

SELECT DAYOFWEEK('2019-05-14 09:30:34');

Result:

3 (Equivalent to Tuesday)

DAYOFYEAR()

This function returns a number that represents a day of the year. The numbers go from 1 to 366, where 1 is January first and 366 is December 31 in a leap year.

Example

SELECT DAYOFYEAR('2019-05-14 09:30:34');

Result:

134

MONTH()

This function returns the month from a date field or timestamp

Example

SELECT MONTH('2019-05-14 09:30:34');

Result:

5

YEAR()

This function returns the year from a date field or timestamp

Example

SELECT YEAR('2019-05-14 09:30:34');

Result:

2019

EXTRACT()

MySQL has a more robust function to extract sections from a date or timestamp. We are talking about the EXTRACT function.

How does MySQL EXTRACT function work?

EXTRACT function receives two (2) parameters, the name of the section you want to extract is the first parameter and the second is the field, date or timestamp from which it will extract.

Examples

SELECT EXTRACT(DAY FROM '2019-05-14 09:30:34')

Result:

14

SELECT EXTRACT(WEEK FROM '2019-05-14 09:30:34')

Result:

19

SELECT EXTRACT(MONTH FROM '2019-05-14 09:30:34')

Result:

5

SELECT EXTRACT(YEAR FROM '2019-05-14 09:30:34')

Result:

2019

We can also extract combined sections as follows:

SELECT EXTRACT(HOUR_MINUTE FROM '2019-05-14 09:30:34')

Result:

930

SELECT EXTRACT(DAY_HOUR FROM '2019-05-14 09:30:34')

Result:

1409

Instant livechat to an Expert!

Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service! 

Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc