MariaDB Date & Time Functions

NOW() returns the same timestamp for every row in a single statement, ensuring consistency across an entire INSERT or UPDATE batch. This makes it safe for replication and auditing. For long-running procedures where you need wall-clock drift, use SYSDATE() instead.

INSERT INTO audit_log (action, ts) SELECT action, NOW() FROM events;

SYSDATE() re-evaluates on every call, so SLEEP(1) between two SYSDATE() calls returns different values. Use it only when you intentionally need to capture elapsed time within a procedure. Avoid it in statement-based replication setups.

SELECT NOW(), SLEEP(2), SYSDATE();

CURDATE() is the cheapest current-date function because it returns a DATE with no time component. For DATETIME columns, wrap the column in DATE() or use a range: WHERE ts >= CURDATE() AND ts < CURDATE() + INTERVAL 1 DAY — the range form allows index use.

SELECT * FROM orders WHERE order_date = CURDATE();

Prefer CURRENT_DATE (no parentheses) in SQL scripts that may be run on other databases such as PostgreSQL. It is also accepted with parentheses in MariaDB for MySQL compatibility.

SELECT * FROM subscriptions WHERE expires_on < CURRENT_DATE;

CURTIME() returns the current time as TIME. With the optional fsp argument (0-6), it includes fractional seconds. Useful for logging query durations when stored in TIME columns rather than full DATETIME.

SELECT CURTIME(6) AS start_time;

CURRENT_TIME without parentheses is the SQL-standard synonym. Use it in portable SQL that must run on PostgreSQL or other ANSI-compliant databases.

SELECT CURRENT_TIME;

CURRENT_TIMESTAMP (or CURRENT_TIMESTAMP()) is the SQL-standard name and is also accepted in PostgreSQL. Use it in DDL DEFAULT clauses and portable application SQL to avoid vendor lock-in.

CREATE TABLE events (created_at DATETIME DEFAULT CURRENT_TIMESTAMP);

UTC_DATE() returns the UTC date regardless of the server timezone. For globally-distributed applications, always store dates as UTC and convert to local time at the application layer to avoid DST gaps and overlaps.

INSERT INTO events (event_date) VALUES (UTC_DATE());

UTC_TIME() returns the current UTC time as a TIME value. Use it for time-only logging where timezone drift would cause incorrect chronological ordering in application logs.

SELECT UTC_TIME(), CURTIME();

UTC_TIMESTAMP() is the UTC equivalent of NOW(). Store all application timestamps in UTC using this function to prevent data inconsistencies when the server timezone changes or when data is replicated across regions.

UPDATE sessions SET last_active = UTC_TIMESTAMP() WHERE id = ?;

DATE(datetime_col) extracts just the date part, enabling GROUP BY date without time interference. However, this prevents index use on the column — for range filtering, use a >= / < range instead.

SELECT DATE(created_at) AS day, COUNT(*) FROM orders GROUP BY DATE(created_at);

TIME(datetime_col) extracts the time portion as a TIME value. Combine with HOUR() to bucket events by time of day, or use in a WHERE clause to find events within a specific time window regardless of date.

SELECT TIME(created_at) AS event_time FROM logs WHERE TIME(created_at) BETWEEN '08:00:00' AND '17:00:00';

YEAR() extracts the four-digit year from a date expression. It's the most common date-part function in reporting queries. Note that using YEAR(col) in a WHERE clause prevents index use; use a range condition for filtered queries.

SELECT YEAR(order_date) AS yr, SUM(total) FROM orders GROUP BY YEAR(order_date);

MONTH() returns 1-12. Always pair it with YEAR() in GROUP BY clauses to avoid collapsing January of different years into the same bucket. For display, use MONTHNAME() or DATE_FORMAT with %M.

SELECT YEAR(order_date), MONTH(order_date), SUM(total) FROM orders GROUP BY YEAR(order_date), MONTH(order_date);

DAY() returns the day of the month (1-31). Use it to identify billing dates, find orders on a specific day of the month, or build recurring-schedule logic without date arithmetic.

SELECT * FROM subscriptions WHERE DAY(billing_date) = DAY(CURDATE());

HOUR() returns 0-23. Grouping by HOUR(created_at) gives you an intraday distribution of events — essential for capacity planning, identifying peak usage windows, and scheduling maintenance.

SELECT HOUR(created_at) AS hr, COUNT(*) FROM requests GROUP BY HOUR(created_at) ORDER BY hr;

MINUTE() returns 0-59. For 15-minute bucketing combine with integer division: FLOOR(MINUTE(ts)/15)*15. This is useful for time-series charts and rate-limiting windows.

SELECT HOUR(ts), FLOOR(MINUTE(ts)/15)*15 AS bucket, COUNT(*) FROM events GROUP BY 1,2;

SECOND() extracts the seconds component (0-59). Use it to detect timestamps that have been truncated to whole minutes during data ingestion — a data quality check for time-series pipelines.

SELECT COUNT(*) FROM sensor_readings WHERE SECOND(recorded_at) != 0;

MICROSECOND() extracts the fractional seconds as microseconds (0-999999). Requires the column or expression to be DATETIME(6) or TIME(6). Used for benchmarking stored procedure durations and high-frequency event deduplication.

SELECT MICROSECOND(NOW(6)) AS us;

DAYOFWEEK returns 1 (Sunday) through 7 (Saturday) following the ODBC standard. To find weekdays only, use DAYOFWEEK(col) BETWEEN 2 AND 6. Remember that 1=Sunday, not Monday, which differs from ISO week conventions.

SELECT * FROM deliveries WHERE DAYOFWEEK(delivery_date) NOT IN (1, 7);

DAYOFYEAR returns 1-366 and is useful for finding 'same day last year' comparisons or building day-of-year heat maps. Combine with YEAR() to create a composite seasonal index key.

SELECT * FROM sales WHERE DAYOFYEAR(sale_date) = DAYOFYEAR(CURDATE()) AND YEAR(sale_date) = YEAR(CURDATE()) - 1;

WEEKDAY returns 0 (Monday) through 6 (Sunday), following ISO convention. This is the opposite mapping from DAYOFWEEK. Use WEEKDAY when your business logic treats Monday as the start of the week.

SELECT * FROM schedules WHERE WEEKDAY(work_date) < 5;

WEEK(date, mode) gives ISO or US-style week numbers depending on mode (0-7). Mode 3 gives ISO 8601 week numbers (Monday start, first week with Thursday). Using the wrong mode causes off-by-one errors in year-boundary weeks.

SELECT WEEK(order_date, 3) AS iso_week FROM orders WHERE YEAR(order_date) = 2024;

WEEKOFYEAR always uses ISO 8601 (mode 3): Monday start, first week contains January 4th. Use it for weekly KPI reporting to get consistent week boundaries across year-end transitions.

SELECT WEEKOFYEAR(order_date) AS wk, SUM(total) FROM orders GROUP BY WEEKOFYEAR(order_date);

QUARTER returns 1-4. Always include YEAR() in GROUP BY alongside QUARTER() to avoid merging Q1 of different years. For fiscal years that don't start in January, you'll need custom date arithmetic.

SELECT YEAR(sale_date), QUARTER(sale_date), SUM(amount) FROM sales GROUP BY 1, 2;

DATE_FORMAT saves a round-trip to the application by returning locale-appropriate date strings directly. Use %W %D %M %Y for long-form dates, or %d/%m/%Y for European format. Note that results are strings — do not use DATE_FORMAT output in further date arithmetic.

SELECT DATE_FORMAT(order_date, '%d %b %Y') AS display_date FROM orders;

TIME_FORMAT works like DATE_FORMAT but for TIME values. Use %h:%i %p for 12-hour clock with AM/PM. Useful for rendering schedule times in web UI templates without application-layer formatting.

SELECT TIME_FORMAT(appointment_time, '%h:%i %p') FROM schedule;

STR_TO_DATE converts a formatted string to a DATE/DATETIME using the same format specifiers as DATE_FORMAT — the two functions are inverse operations. Essential for importing dates from legacy systems with non-ISO formats like DD/MM/YYYY.

INSERT INTO events (event_date) VALUES (STR_TO_DATE('15/03/2024', '%d/%m/%Y'));

DATE_ADD(date, INTERVAL n unit) is the cleanest way to compute future deadlines. INTERVAL units include SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR. For subtracting, use INTERVAL -n or DATE_SUB.

SELECT DATE_ADD(signup_date, INTERVAL 30 DAY) AS trial_end FROM users;

DATE_SUB is the explicit subtraction partner to DATE_ADD. Use it for 'show me the last N days/months' queries. For index-friendly range queries, compute the boundary once outside the query and use a >= comparison.

SELECT * FROM logs WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);

ADDDATE(date, INTERVAL n unit) is an alias for DATE_ADD. But ADDDATE(date, n) where n is an integer adds n days — a useful shorthand for simple day-offset calculations. ADDDATE with an integer is slightly more concise than DATE_ADD for day-only math.

SELECT ADDDATE(order_date, 14) AS estimated_delivery FROM orders;

Like ADDDATE, SUBDATE(date, n) with an integer subtracts n days — no INTERVAL keyword needed. Use this for 'N days ago' lookback boundaries in dashboard queries.

SELECT * FROM activity WHERE action_date >= SUBDATE(CURDATE(), 30);

ADDTIME(datetime, time) adds a time expression to a DATETIME or TIME value. Use it to add buffer periods: ADDTIME(appointment_start, '00:30:00') gives the end time of a 30-minute meeting.

SELECT ADDTIME(appointment_start, '00:45:00') AS end_time FROM appointments;

SUBTIME(datetime, time) subtracts a time interval from a DATETIME or TIME. Useful when you know an end time and duration but need the start time — e.g., for backwards-computing job start from completion time.

SELECT SUBTIME(completed_at, duration) AS started_at FROM jobs;

DATEDIFF(end, start) returns a signed integer number of days. It ignores the time component, so DATEDIFF('2024-03-15 23:59', '2024-03-14 00:01') = 1. Perfect for computing account age, days overdue, or membership duration.

SELECT DATEDIFF(CURDATE(), birth_date) / 365.25 AS age_years FROM users;

TIMEDIFF(end, start) returns the difference as a TIME value. Since TIME is limited to 838 hours, use TIMESTAMPDIFF(SECOND, ...) for differences larger than about 35 days. TIMEDIFF is best for intra-day duration measurements.

SELECT TIMEDIFF(end_time, start_time) AS duration FROM sessions;

TIMESTAMPDIFF(unit, start, end) returns an integer difference in the specified unit (SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR). Unlike DATEDIFF/TIMEDIFF, it handles month and year boundaries correctly. Use it for precise age calculations.

SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age FROM customers;

TIMESTAMPADD(unit, n, datetime) adds n units to a DATETIME. Unlike DATE_ADD, the unit is the first argument which makes it easier to use in dynamic SQL where the unit comes from a variable. Both produce identical results for standard units.

SELECT TIMESTAMPADD(MONTH, 6, subscription_start) AS renewal_date FROM subs;

FROM_UNIXTIME converts seconds since 1970-01-01 00:00:00 UTC to a DATETIME in the server's timezone. Pass an optional format string (same as DATE_FORMAT) to get a formatted string in a single call. Essential for processing API timestamps stored as integers.

SELECT FROM_UNIXTIME(created_at, '%Y-%m-%d %H:%i') FROM events;

UNIX_TIMESTAMP() or UNIX_TIMESTAMP(datetime) converts to seconds since epoch, stripping timezone ambiguity. INT UNSIGNED columns storing epoch times are 4 bytes vs 8 bytes for DATETIME(6) and are trivially comparable in any language.

SELECT UNIX_TIMESTAMP(created_at) AS epoch FROM events;

FROM_DAYS(N) converts a daynumber (where day 1 = year 0001-01-01) back to a DATE. It is primarily used in conjunction with TO_DAYS() for period arithmetic or when working with legacy systems that stored dates as day counts.

SELECT FROM_DAYS(TO_DAYS(CURDATE()) - 30) AS thirty_days_ago;

TO_DAYS(date) returns an absolute day count, allowing simple subtraction to compute day differences: TO_DAYS(end) - TO_DAYS(start). It's equivalent to DATEDIFF but explicitly returns the count from year 0. Avoid for dates before year 1582 (Gregorian cutover).

SELECT TO_DAYS(expiry_date) - TO_DAYS(CURDATE()) AS days_remaining FROM licenses;

TO_SECONDS(datetime) returns the number of seconds since year 0, useful for computing precise absolute time differences on DATETIME values including the time component, unlike TO_DAYS which ignores time.

SELECT (TO_SECONDS(end_dt) - TO_SECONDS(start_dt)) / 3600 AS hours FROM sessions;

MAKEDATE(year, dayofyear) constructs a DATE from a year and a 1-based day-of-year number. Useful when raw data provides year + DOY instead of full dates, which is common in scientific and satellite data formats.

SELECT MAKEDATE(2024, 75) AS target_date;