MySQL Date Functions

MySQL provides a variety of date functions that allow you to manipulate and query date and time values. These functions are crucial for working with date-related data, whether you're formatting dates, extracting parts of a date, or calculating intervals.

1. Commonly Used MySQL Date Functions

1.1 CURDATE()

The CURDATE() function returns the current date in YYYY-MM-DD format.

SELECT CURDATE();

✅ Output:
Returns the current date, e.g., 2025-01-30.

1.2 NOW()

The NOW() function returns the current date and time in YYYY-MM-DD HH:MM:SS format.

SELECT NOW();

✅ Output:
Returns the current date and time, e.g., 2025-01-30 12:34:56.

1.3 DATE()

The DATE() function extracts the date part from a datetime or timestamp.

SELECT DATE('2025-01-30 12:34:56');

✅ Output:
Returns only the date, e.g., 2025-01-30.

1.4 DATE_ADD()

The DATE_ADD() function adds a specific time interval to a date.

SELECT DATE_ADD('2025-01-30', INTERVAL 5 DAY);

✅ Output:
Returns the date 5 days after January 30, 2025, e.g., 2025-02-04.

1.5 DATE_SUB()

The DATE_SUB() function subtracts a time interval from a date.

SELECT DATE_SUB('2025-01-30', INTERVAL 5 DAY);

✅ Output:
Returns the date 5 days before January 30, 2025, e.g., 2025-01-25.

1.6 EXTRACT()

The EXTRACT() function extracts a part of the date (like year, month, day) from a given date.

SELECT EXTRACT(YEAR FROM '2025-01-30');

✅ Output:
Returns the year part, e.g., 2025.

You can extract other parts like MONTH, DAY, HOUR, MINUTE, etc.

1.7 DAYNAME()

The DAYNAME() function returns the full name of the weekday (e.g., Monday, Tuesday) from a date.

SELECT DAYNAME('2025-01-30');

✅ Output:
Returns the name of the day, e.g., Thursday.

1.8 DAYOFWEEK()

The DAYOFWEEK() function returns the weekday index (1 to 7), where 1 is Sunday and 7 is Saturday.

SELECT DAYOFWEEK('2025-01-30');

✅ Output:
Returns the weekday index, e.g., 5 (Thursday).

1.9 DAYOFMONTH()

The DAYOFMONTH() function returns the day of the month (1 to 31) from a given date.

SELECT DAYOFMONTH('2025-01-30');

✅ Output:
Returns the day of the month, e.g., 30.

1.10 MONTH()

The MONTH() function returns the month part (1 to 12) of a given date.

SELECT MONTH('2025-01-30');

✅ Output:
Returns the month, e.g., 1 (January).

1.11 YEAR()

The YEAR() function returns the year part of a given date.

SELECT YEAR('2025-01-30');

✅ Output:
Returns the year, e.g., 2025.

1.12 MONTHNAME()

The MONTHNAME() function returns the full name of the month from a given date.

SELECT MONTHNAME('2025-01-30');

✅ Output:
Returns the full name of the month, e.g., January.

1.13 TIME()

The TIME() function extracts the time part (HH:MM:SS) from a datetime value.

SELECT TIME('2025-01-30 12:34:56');

✅ Output:
Returns the time part, e.g., 12:34:56.

1.14 TIMESTAMP()

The TIMESTAMP() function converts a date or datetime value into a timestamp.

SELECT TIMESTAMP('2025-01-30 12:34:56');

✅ Output:
Returns the timestamp for the given datetime.

1.15 STR_TO_DATE()

The STR_TO_DATE() function converts a string into a date based on a specified format.

SELECT STR_TO_DATE('30-01-2025', '%d-%m-%Y');

✅ Output:
Converts the string to a date, e.g., 2025-01-30.

1.16 UNIX_TIMESTAMP()

The UNIX_TIMESTAMP() function returns the Unix timestamp (seconds since '1970-01-01 00:00:00').

SELECT UNIX_TIMESTAMP('2025-01-30 12:34:56');

✅ Output:
Returns the Unix timestamp, e.g., 1735722896.

1.17 FROM_UNIXTIME()

The FROM_UNIXTIME() function converts a Unix timestamp into a datetime value.

SELECT FROM_UNIXTIME(1735722896);

✅ Output:
Converts the timestamp to datetime, e.g., 2025-01-30 12:34:56.

2. Date Arithmetic and Interval Manipulation

2.1 Add/Subtract Days, Months, or Years

You can perform date arithmetic by adding or subtracting intervals to/from dates:

SELECT DATE_ADD('2025-01-30', INTERVAL 2 MONTH);  -- Adds 2 months
SELECT DATE_SUB('2025-01-30', INTERVAL 1 YEAR);   -- Subtracts 1 year

2.2 Add/Subtract Days, Hours, Minutes

You can also add or subtract smaller units like days, hours, minutes, etc.:

SELECT DATE_ADD('2025-01-30', INTERVAL 5 DAY);  -- Adds 5 days
SELECT DATE_SUB('2025-01-30', INTERVAL 3 HOUR); -- Subtracts 3 hours

3. Conclusion

MySQL provides a powerful set of date functions to handle date and time manipulation. Whether you're working with current date and time, extracting parts of a date, or performing date arithmetic, these functions will help you manage and analyze date-related data effectively.

💡 Use the right date function based on your requirements, whether it’s extracting a part of the date or manipulating intervals.

Souy Soeng

MySQL Date Functions