MySQL - Date and Time Functions

MySQL: A Practical Guide to Date and Time Functions

MySQL provides a rich set of functions to handle DATE, TIME, DATETIME, and TIMESTAMP data. Mastering these functions is essential for everything from logging events and scheduling tasks to analyzing time-series data. This guide organizes the most common functions into practical categories with modern examples.

Best Practices for Storing Dates and Times

Use the Right Data Type: Choose DATE for dates only, TIME for times only, DATETIME for a combination, and TIMESTAMP for moments in time that need to be timezone-aware (it stores the value in UTC and converts it back to the session’s time zone on retrieval).
Store in UTC: For applications with users in multiple time zones, it is a critical best practice to store all TIMESTAMP and DATETIME values in Coordinated Universal Time (UTC). Handle conversions to the user’s local time zone in your application layer.
Use DEFAULT CURRENT_TIMESTAMP: For columns like created_at or updated_at, use DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP to let MySQL manage them automatically.

Category 1: Getting the Current Date and Time

These functions retrieve the current date and time from the server where MySQL is running.

Function	Description
`NOW()`, `CURRENT_TIMESTAMP()`	Returns the current date and time as a ‘YYYY-MM-DD HH:MM:SS’ value.
`CURDATE()`, `CURRENT_DATE()`	Returns the current date as a ‘YYYY-MM-DD’ value.
`CURTIME()`, `CURRENT_TIME()`	Returns the current time as a ‘HH:MM:SS’ value.
`UTC_TIMESTAMP()`, `UTC_DATE()`, `UTC_TIME()`	Returns the current Coordinated Universal Time (UTC) date and/or time.

Example

SELECT NOW(), CURDATE(), UTC_TIMESTAMP();

Category 2: Extracting Parts of a Date or Time

These functions allow you to pull specific components from a date/time value, which is useful for grouping and filtering data.

Function	Description
`YEAR(date)`, `MONTH(date)`, `DAY(date)`	Extracts the year, month, or day of the month.
`HOUR(time)`, `MINUTE(time)`, `SECOND(time)`	Extracts the hour, minute, or second.
`DAYNAME(date)`	Returns the full name of the weekday (e.g., ‘Sunday’).
`MONTHNAME(date)`	Returns the full name of the month (e.g., ‘January’).
`QUARTER(date)`	Returns the quarter of the year (1-4).
`WEEKOFYEAR(date)`	Returns the calendar week of the date (1-53).
`EXTRACT(unit FROM date)`	A versatile function to extract a specific unit (e.g., `YEAR`, `MONTH`, `DAY`, `HOUR_MINUTE`).

Example: Analyzing User Signups

Imagine a users table with a created_at timestamp. We can analyze signup trends.

SELECT
    COUNT(id) AS signup_count,
    YEAR(created_at) AS signup_year,
    MONTHNAME(created_at) AS signup_month
FROM users
WHERE YEAR(created_at) = 2023
GROUP BY signup_year, signup_month
ORDER BY MONTH(created_at);

Category 3: Date and Time Arithmetic

Perform calculations like adding or subtracting intervals, or finding the difference between two dates.

Function	Description
`DATE_ADD(date, INTERVAL expr unit)`	Adds a specified time interval to a date. (Synonym: `ADDDATE()`).
`DATE_SUB(date, INTERVAL expr unit)`	Subtracts a specified time interval from a date. (Synonym: `SUBDATE()`).
`DATEDIFF(date1, date2)`	Returns the number of days between two dates.
`TIMESTAMPDIFF(unit, datetime1, datetime2)`	Returns the difference between two datetime expressions in the specified unit (e.g., `MINUTE`, `DAY`, `YEAR`).
`LAST_DAY(date)`	Returns the last day of the month for the given date.

Example: Calculating Subscription Expiry

-- Find all subscriptions that will expire in the next 30 days
SELECT
    user_id,
    start_date,
    DATE_ADD(start_date, INTERVAL 1 YEAR) AS expiry_date
FROM subscriptions
WHERE DATE_ADD(start_date, INTERVAL 1 YEAR) BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 30 DAY);

Category 4: Formatting and Parsing

Convert date/time values into custom string formats, or parse strings into date/time values.

Function	Description
`DATE_FORMAT(date, format)`	Formats a date into a string based on a format specifier string.
`STR_TO_DATE(str, format)`	Parses a string into a date/time value based on a format specifier string.
`FROM_UNIXTIME(unix_timestamp)`	Converts a UNIX timestamp (seconds since ‘1970-01-01 00:00:00’ UTC) to a DATETIME.
`UNIX_TIMESTAMP([date])`	Returns the UNIX timestamp of a given date, or the current time if no date is provided.

Example: Creating a Human-Readable Report Date

The DATE_FORMAT function is incredibly powerful. Common format specifiers include %Y (4-digit year), %m (month, 01-12), %d (day, 01-31), %W (weekday name), and %H:%i:%s (time). Refer to MySQL documentation for the full list.

SELECT
    order_id,
    DATE_FORMAT(order_date, '%W, %M %e, %Y') AS formatted_date
FROM orders
LIMIT 5;

-- Example Output: 'Tuesday, December 5, 2023'

Category 5: Time Zone and Conversion Functions

Essential functions for handling different time zones and converting between units.

Function	Description
`CONVERT_TZ(dt, from_tz, to_tz)`	Converts a datetime value from one time zone to another. Requires time zone tables to be loaded.
`TO_DAYS(date)`	Given a date, returns a day number (the number of days since year 0).
`FROM_DAYS(N)`	Given a day number N, returns a DATE value.
`SEC_TO_TIME(seconds)`	Converts seconds to a ‘HH:MM:SS’ time format.
`TIME_TO_SEC(time)`	Converts a time value into the total number of seconds.

Example: Converting UTC to a Local Time Zone

-- Assuming event_time is stored in UTC
SELECT
    event_name,
    event_time AS time_in_utc,
    CONVERT_TZ(event_time, 'UTC', 'America/New_York') AS time_in_new_york
FROM events
LIMIT 1;

This is a reference to the most commonly used functions. For a complete and detailed list, including less common functions like MAKEDATE(), PERIOD_DIFF(), or TO_SECONDS(), always consult the official MySQL documentation, as it is the most up-to-date source of information.