MySQL Dates

Date and Time Column Types

MySQL offers several temporal types, each suited to a different level of precision. DATE stores a calendar date only ('YYYY-MM-DD'). DATETIME stores date and time together ('YYYY-MM-DD HH:MM:SS') with no timezone conversion. TIMESTAMP looks similar to DATETIME but is stored internally as UTC and converted to the connection's timezone on read, and it has a smaller valid range. TIME stores a duration or time-of-day, and YEAR stores a four-digit year.

CREATE TABLE events (
  event_id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  event_date DATE NOT NULL,
  starts_at DATETIME NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Getting the Current Date and Time

NOW() returns the current date and time; CURDATE() returns just the date; CURTIME() returns just the time. NOW() is evaluated once per statement, which keeps it consistent even in a multi-row INSERT.

INSERT INTO events (title, event_date, starts_at)
VALUES ('Product Launch', CURDATE(), NOW());

SELECT NOW() AS right_now, CURDATE() AS today, CURTIME() AS current_time_only;

Formatting and Extracting Parts of a Date

DATE_FORMAT() converts a date or datetime into a custom string using format specifiers like %Y (4-digit year), %m (month), %d (day), and %H:%i:%s (time). YEAR(), MONTH(), DAY(), and DAYNAME() extract individual components.

SELECT title,
       DATE_FORMAT(starts_at, '%W, %M %e, %Y at %h:%i %p') AS friendly_date,
       YEAR(starts_at) AS event_year,
       DAYNAME(starts_at) AS weekday
FROM events
WHERE event_date >= CURDATE();

Date Arithmetic

DATE_ADD() and DATE_SUB() shift a date by an interval. DATEDIFF() returns the number of days between two dates. TIMESTAMPDIFF() measures the difference in a chosen unit (day, month, year, hour, and so on).

-- Events happening in the next 7 days
SELECT title, event_date
FROM events
WHERE event_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);

-- Days until each event, and age in years for a birthdate column
SELECT title, DATEDIFF(event_date, CURDATE()) AS days_until_event
FROM events;

SELECT TIMESTAMPDIFF(YEAR, '1990-06-15', CURDATE()) AS age_in_years;