MySQL Date and Time Functions (original) (raw)

Handling **date and time data in MySQL is essential for many database operations, especially when it comes to handling timestamps, scheduling tasks, or generating time-based. MySQL provides a variety of date and time functions that help users work with date values, perform calculations, and format them as needed.

These functions allow developers to perform calculations, extract specific parts of a date, or even format the output for better readability. In this article, we will explore the most commonly used MySQL date functions, explain their syntax, provide examples, and walk you through how to effectively use them

Understanding MySQL Date and Time Data Types

When working with dates in MySQL, it can be tricky for beginners, especially because the format of the date in the database must match the format of the input data during insertion. In many cases, instead of just using a simple date, we may need to store both a date and time, depending on the use case. This is where the **DATETIME**and **TIMESTAMP**data types come into play.

MySQL provides the following data types for storing date and time values:

**1. DATE:

• **Format: YYYY-MM-DD
• Used for storing only the date (year, month, and day), without any time component.

2. **DATETIME:

• **Format: YYYY-MM-DD HH:MM:SS
• Stores both the date and the time, making it useful when you need to capture specific times along with the date.

3. **TIMESTAMP:

• **Format: YYYY-MM-DD HH:MM:SS
• Similar to DATETIME, but TIMESTAMP also includes time zone support. It automatically updates to the current timestamp when a record is modified, making it ideal for tracking changes to records over time.

4. **YEAR:

• **Format: YYYY or YY
• Used for storing just the year, which can be useful for applications where only the year is needed, such as tracking birth years or fiscal years.

MySQL Date and Time Functions

Now, let’s dive into the MySQL date functions that you can use to manipulate and query date and time data effectively.

**1. NOW()- Get Current Date and Time

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

**Query:

SELECT NOW();

**Output:

**2. CURDATE() – Get Current Date Only

If we only need the current date without the time portion, we can use CURDATE(), which returns the date in YYYY-MM-DD format.

**Query:

SELECT CURDATE();

**Output:

**3. CURTIME() – Get Current Time Only

The CURTIME() function returns the current time in **HH:MI:SS format, excluding the date.

**Query:

SELECT CURTIME();

**Output:

**4. DATE() – Extract the Date Part from a DATETIME Value

The DATE() function extracts only the date part from a DATETIME or TIMESTAMP value, discarding the time.

**Example: For a table called users:

**Query:

SELECT Name, DATE(BirthTime)
AS BirthDate FROM Test;

**Output:

**5. EXTRACT() – Extract Specific Date Parts

EXTRACT() is used to extract specific parts of a date, such as the day, month, or year. This function can be especially helpful when analyzing or comparing different parts of a date. Several units can be considered but only some are used such as **MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, etc. And ‘date’ is a valid date expression.

**Syntax:

EXTRACT(unit FROM date);

**Query to Extract Day:

SELECT Name, Extract(DAY FROM
BirthTime) AS BirthDay FROM Test;

**Output:

**Query to Extract Year:

SELECT Name, Extract(YEAR FROM BirthTime)
AS BirthYear FROM Test;

**Output:

**Query to Extract Seconds:

SELECT Name, Extract(SECOND FROM
BirthTime) AS BirthSecond FROM Test;

**Output:

**6. DATE_ADD() – Add Intervals to a Date

The **DATE_ADD() function allows you to add time intervals (e.g., days, months, years) to a date or DATETIME value.

**Syntax:

DATE_ADD(date, INTERVAL expr type);

Example: For the below table named ‘Test‘

**Query to Add 1 Year to a Date

SELECT Name, DATE_ADD(BirthTime, INTERVAL
1 YEAR) AS BirthTimeModified FROM Test;

**Output:

**Query to Add 30 days to a Date

SELECT Name, DATE_ADD(BirthTime,
INTERVAL 30 DAY) AS BirthDayModified FROM Test;

**Output:

**Query to Add 4 Hours to a Date

SELECT Name, DATE_ADD(BirthTime, INTERVAL
4 HOUR) AS BirthHourModified FROM Test;

**Output:

**7. DATEDIFF() – Find the Difference Between Two Dates

This Function returns the number of days between two dates.

**Syntax:

DATEDIFF(interval,date1, date2);

interval – minute/hour/month/year,etc

date1 & date2- date/time expression

**Query to Find the Difference Between Two Dates

SELECT DATEDIFF(day, '2017-01-13', '2017-01-03') AS DateDiff;

**Output:

**8. DATE_FORMAT() – Foramt Date and Time

DATE_FORMAT() allows us to format a **DATE, **DATETIME, or **TIMESTAMP value into a custom format using placeholders.

**Syntax:

DATE_FORMAT(date,format);

the date is a valid date and the format specifies the output format for the date/time. The formats that can be used are:

• %a-Abbreviated weekday name (Sun-Sat)
• %b-Abbreviated month name (Jan-Dec)
• %c-Month, numeric (0-12)
• %D-Day of month with English suffix (0th, 1st, 2nd, 3rd)
• %d-Day of the month, numeric (00-31)
• %e-Day of the month, numeric (0-31)
• %f-Microseconds (000000-999999)
• %H-Hour (00-23)
• %h-Hour (01-12)
• %I-Hour (01-12)
• %i-Minutes, numeric (00-59)
• %j-Day of the year (001-366)
• %k-Hour (0-23)
• %l-Hour (1-12)
• %M-Month name (January-December)
• %m-Month, numeric (00-12)
• %p-AM or PM
• %r-Time, 12-hour (hh:mm: ss followed by AM or PM)
• %S-Seconds (00-59)
• %s-Seconds (00-59)
• %T-Time, 24-hour (hh:mm: ss)
• %U-Week (00-53) where Sunday is the first day of the week
• %u-Week (00-53) where Monday is the first day of the week
• %V-Week (01-53) where Sunday is the first day of the week, used with %X
• %v-Week (01-53) where Monday is the first day of the week, used with %x
• %W-Weekday name (Sunday-Saturday)
• %w-Day of the week (0=Sunday, 6=Saturday)
• %X-Year for the week where Sunday is the first day of the week, four digits, used with %V
• %x-Year for the week where Monday is the first day of the week, four digits, used with %v
• %Y-Year, numeric, four digits
• %y-Year, numeric, two digits

Query to Format a Date

SELECT DATE_FORMAT('2025-04-10 12:34:56', '%W, %M %d, %Y') AS formatted_date;

**Output:

Best Practices for Working with Date and Time in MySQL

1. **Always Use Proper Date Formats

When inserting or updating dates, ensure that you follow MySQL’s expected formats (YYYY-MM-DD, YYYY-MM-DD HH:MI:SS). This ensures that your queries return expected results without errors.

2. **Time Zone Considerations with TIMESTAMP

While DATETIME does not store timezone information, TIMESTAMP does. If your application is time zone sensitive (for example, in international applications), consider using TIMESTAMP for date-time fields that need to account for different time zones.

3. **Handling Date Ranges in Queries

For filtering data based on date ranges (e.g., retrieving all records from a specific month or year), ensure you use proper date comparisons in the WHERE clause. Example:

SELECT * FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31';

4. **Performance Considerations

When working with date and time functions, especially in large datasets, be mindful of the impact on performance. Avoid using functions like NOW() or CURDATE() in the WHERE clause, as they can slow down queries when dealing with large tables.

Conclusion

**MySQL’s date and time functions are essential tools for **handling and **manipulating temporal data within your databases. By mastering functions like NOW(), CURDATE(), DATE_ADD(), DATEDIFF(), and DATE_FORMAT(), you can easily work with time-based calculations, **comparisons, and **formats. The key to effectively using these functions is to understand their syntax, when and how to use them, and their performance implications in large datasets. With this knowledge, you’ll be able to build more efficient, accurate, and scalable queries for all your date and time-related needs in MySQL.