📅

MariaDB Date & Time Functions

MariaDB

Complete reference for MariaDB date and time functions covering NOW, DATE_FORMAT, DATEDIFF, TIMESTAMPDIFF, STR_TO_DATE, FROM_UNIXTIME, CONVERT_TZ, and 57+ more. Every function includes syntax, format specifiers, MySQL compatibility notes, and timezone examples. Updated for MariaDB 11.x.

50 functions

What are MariaDB Date & Time Functions?

MariaDB date and time functions provide comprehensive tools for date arithmetic, formatting, and timezone conversion. DATE_FORMAT() formats a datetime value using a format string with % specifiers (similar to C strftime), DATEDIFF() returns the number of days between two dates, and TIMESTAMPDIFF() computes the difference in any specified unit (SECOND, MINUTE, HOUR, DAY, MONTH, YEAR). MariaDB date functions are broadly compatible with MySQL but differ significantly from PostgreSQL date functions in syntax.

NOW

→ DATETIME

Returns the current date and time as a DATETIME value.

Signature

NOW([fsp])

Parameters

Parameter	Type	Description
fsp	INT	Fractional seconds precision 0-6 (optional)

Examples

Current datetime

sql

SELECT NOW();

→-- 2024-03-15 10:30:00

With milliseconds

sql

SELECT NOW(3);

→-- 2024-03-15 10:30:00.123

Extract date part

sql

SELECT DATE(NOW());

→-- 2024-03-15

Comparison

sql

SELECT NOW() > '2024-01-01';

→-- 1

Audit column

sql

INSERT INTO logs (created_at) VALUES (NOW());

→-- inserted

⚠Anti-Pattern— Using SYSDATE() in replication

SYSDATE() is non-deterministic and causes unsafe replication in statement-based mode.

✓ Instead: Use NOW() for replication-safe queries.

⇄ vs PostgreSQL: PostgreSQL uses NOW() identically; also CURRENT_TIMESTAMP.

SYSDATE

→ DATETIME

Returns the actual current date and time at the moment of execution, not statement start.

Signature

SYSDATE([fsp])

Parameters

Parameter	Type	Description
fsp	INT	Fractional seconds precision 0-6 (optional)

Examples

Wall-clock time

sql

SELECT SYSDATE();

→-- 2024-03-15 10:30:00

With microseconds

sql

SELECT SYSDATE(3);

→-- 2024-03-15 10:30:00.456

Diverges from NOW()

sql

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

→-- NOW and SYSDATE differ by 1s

Arithmetic

sql

SELECT SYSDATE() - INTERVAL 1 DAY;

→-- 2024-03-14 10:30:00

Extract date

sql

SELECT DATE(SYSDATE());

→-- 2024-03-15

⚠Anti-Pattern— SYSDATE() in statement-based replication

SYSDATE() produces different values on replica, causing data drift in statement-based replication.

✓ Instead: Prefer NOW() unless you specifically need intra-statement time differences.

⇄ vs PostgreSQL: PostgreSQL has clock_timestamp() for the equivalent live wall-clock behavior.

CURDATE

→ DATE

Returns the current date as a DATE value in 'YYYY-MM-DD' format.

Signature

CURDATE()

Examples

Today's date

sql

SELECT CURDATE();

→-- 2024-03-15

Date arithmetic

sql

SELECT CURDATE() + INTERVAL 7 DAY;

→-- 2024-03-22

Days since event

sql

SELECT DATEDIFF(CURDATE(), hire_date) FROM employees;

→-- days since hired

Filter today

sql

SELECT * FROM orders WHERE order_date = CURDATE();

→-- today's orders

Extract year

sql

SELECT YEAR(CURDATE());

→-- 2024

⚠Anti-Pattern— Comparing CURDATE() to a DATETIME column without cast

CURDATE() = datetime_col misses rows after midnight due to implicit type conversion.

✓ Instead: Use DATE(datetime_col) = CURDATE() or a range condition.

⇄ vs PostgreSQL: PostgreSQL uses CURRENT_DATE.

CURRENT_DATE

→ DATE

SQL-standard synonym for CURDATE(). Returns the current date as 'YYYY-MM-DD'.

Signatures

CURRENT_DATE

CURRENT_DATE()

Examples

No parentheses needed

sql

SELECT CURRENT_DATE;

→-- 2024-03-15

Parentheses also valid

sql

SELECT CURRENT_DATE();

→-- 2024-03-15

Add a month

sql

SELECT CURRENT_DATE + INTERVAL 1 MONTH;

→-- 2024-04-15

Upcoming rows

sql

SELECT * FROM events WHERE event_date >= CURRENT_DATE;

→-- future events

Extract month

sql

SELECT MONTH(CURRENT_DATE);

→-- 3

⚠Anti-Pattern— Mixing CURRENT_DATE with DATETIME comparisons

CURRENT_DATE has no time part; direct equality with DATETIME columns can miss records.

✓ Instead: Wrap the DATETIME column with DATE() before comparing.

⇄ vs PostgreSQL: PostgreSQL supports CURRENT_DATE identically.

CURTIME

→ TIME

Returns the current time as a TIME value in 'HH:MM:SS' format.

Signature

CURTIME([fsp])

Parameters

Parameter	Type	Description
fsp	INT	Fractional seconds precision 0-6 (optional)

Examples

Current time

sql

SELECT CURTIME();

→-- 10:30:00

With milliseconds

sql

SELECT CURTIME(3);

→-- 10:30:00.123

Extract hour

sql

SELECT HOUR(CURTIME());

→-- 10

Current shift

sql

SELECT * FROM shifts WHERE start_time <= CURTIME() AND end_time >= CURTIME();

→-- active shifts

Time since 8am

sql

SELECT TIMEDIFF(CURTIME(), '08:00:00');

→-- 02:30:00

⚠Anti-Pattern— Using CURTIME() for duration math across midnight

CURTIME() wraps at midnight; subtracting times can yield negative results unexpectedly.

✓ Instead: Use TIMEDIFF() or full DATETIME arithmetic with NOW().

⇄ vs PostgreSQL: PostgreSQL uses CURRENT_TIME.

CURRENT_TIME

→ TIME

SQL-standard synonym for CURTIME(). Returns current time as 'HH:MM:SS'.

Signatures

CURRENT_TIME

CURRENT_TIME([fsp])

Parameters

Parameter	Type	Description
fsp	INT	Fractional seconds precision 0-6 (optional)

Examples

No parentheses

sql

SELECT CURRENT_TIME;

→-- 10:30:00

With precision

sql

SELECT CURRENT_TIME(2);

→-- 10:30:00.12

Extract hour

sql

SELECT HOUR(CURRENT_TIME);

→-- 10

Business hours check

sql

SELECT CURRENT_TIME BETWEEN '09:00:00' AND '17:00:00';

→-- 1

Elapsed since midnight

sql

SELECT TIMEDIFF(CURRENT_TIME, '00:00:00');

→-- 10:30:00

⚠Anti-Pattern— Storing CURRENT_TIME in a DATETIME column

Assigning a TIME value to a DATETIME column pads the date as '0000-00-00', causing invalid data.

✓ Instead: Use NOW() or CURRENT_TIMESTAMP when a full DATETIME is required.

⇄ vs PostgreSQL: PostgreSQL supports CURRENT_TIME identically.

CURRENT_TIMESTAMP

→ DATETIME

SQL-standard synonym for NOW(). Returns current date and time as DATETIME.

Signatures

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP([fsp])

Parameters

Parameter	Type	Description
fsp	INT	Fractional seconds precision 0-6 (optional)

Examples

Current datetime

sql

SELECT CURRENT_TIMESTAMP;

→-- 2024-03-15 10:30:00

Microsecond precision

sql

SELECT CURRENT_TIMESTAMP(6);

→-- 2024-03-15 10:30:00.123456

Default value

sql

CREATE TABLE t (created_at DATETIME DEFAULT CURRENT_TIMESTAMP);

→-- table created

Recent rows

sql

SELECT * FROM logs WHERE ts >= CURRENT_TIMESTAMP - INTERVAL 1 HOUR;

→-- last hour

Audit update

sql

UPDATE t SET updated_at = CURRENT_TIMESTAMP WHERE id = 1;

→-- updated

⚠Anti-Pattern— Assuming CURRENT_TIMESTAMP changes mid-statement

CURRENT_TIMESTAMP is fixed at statement start; it does not advance during a long transaction.

✓ Instead: Use SYSDATE() if you need live wall-clock time within a stored procedure loop.

⇄ vs PostgreSQL: PostgreSQL supports CURRENT_TIMESTAMP identically.

UTC_DATE

→ DATE

Returns the current UTC date as a DATE value in 'YYYY-MM-DD' format.

Signature

UTC_DATE()

Examples

Current UTC date

sql

SELECT UTC_DATE();

→-- 2024-03-15

Compare local vs UTC

sql

SELECT CURDATE(), UTC_DATE();

→-- may differ near midnight

UTC date arithmetic

sql

SELECT UTC_DATE() + INTERVAL 7 DAY;

→-- 2024-03-22

Days elapsed

sql

SELECT DATEDIFF(UTC_DATE(), start_date) FROM projects;

→-- UTC-based diff

Extract parts

sql

SELECT YEAR(UTC_DATE()), MONTH(UTC_DATE());

→-- 2024, 3

⚠Anti-Pattern— Mixing UTC_DATE() and CURDATE() in the same query

Combining UTC and local date functions in one query creates subtle timezone-boundary bugs.

✓ Instead: Pick one timezone strategy (UTC throughout) and apply conversions consistently.

⇄ vs PostgreSQL: PostgreSQL uses CURRENT_DATE AT TIME ZONE 'UTC' or NOW() AT TIME ZONE 'UTC'.

UTC_TIME

→ TIME

Returns the current UTC time as a TIME value in 'HH:MM:SS' format.

Signature

UTC_TIME([fsp])

Parameters

Parameter	Type	Description
fsp	INT	Fractional seconds precision 0-6 (optional)

Examples

Current UTC time

sql

SELECT UTC_TIME();

→-- 14:30:00

With milliseconds

sql

SELECT UTC_TIME(3);

→-- 14:30:00.123

Local vs UTC

sql

SELECT CURTIME(), UTC_TIME();

→-- differ by timezone offset

UTC hour

sql

SELECT HOUR(UTC_TIME());

→-- 14

Offset detection

sql

SELECT TIMEDIFF(UTC_TIME(), CURTIME());

→-- timezone offset

⚠Anti-Pattern— Storing UTC_TIME() without a date

Storing only a UTC time without date loses the date context needed for correct timezone conversion.

✓ Instead: Store full UTC_TIMESTAMP() values and convert to local time at the application layer.

⇄ vs PostgreSQL: PostgreSQL uses CURRENT_TIME AT TIME ZONE 'UTC'.

UTC_TIMESTAMP

→ DATETIME

Returns the current UTC date and time as a DATETIME value.

Signature

UTC_TIMESTAMP([fsp])

Parameters

Parameter	Type	Description
fsp	INT	Fractional seconds precision 0-6 (optional)

Examples

Current UTC datetime

sql

SELECT UTC_TIMESTAMP();

→-- 2024-03-15 14:30:00

Microsecond precision

sql

SELECT UTC_TIMESTAMP(6);

→-- 2024-03-15 14:30:00.123456

Local vs UTC

sql

SELECT NOW(), UTC_TIMESTAMP();

→-- differ by timezone offset

UTC audit column

sql

INSERT INTO events (created_utc) VALUES (UTC_TIMESTAMP());

→-- inserted

Offset

sql

SELECT TIMESTAMPDIFF(HOUR, UTC_TIMESTAMP(), NOW());

→-- timezone offset in hours

⚠Anti-Pattern— Using UTC_TIMESTAMP() in a local-timezone app without conversion

Displaying raw UTC_TIMESTAMP() values to users without local conversion causes incorrect times.

✓ Instead: Convert UTC to local time using CONVERT_TZ() before presenting to end users.

⇄ vs PostgreSQL: PostgreSQL uses NOW() AT TIME ZONE 'UTC' or CURRENT_TIMESTAMP AT TIME ZONE 'UTC'.

DATE

→ DATE

Extracts the date part from a DATETIME or TIMESTAMP expression.

Signature

DATE(expr)

Parameters

Parameter	Type	Description
expr	DATETIME	A date or datetime expression

Examples

Strip time

sql

SELECT DATE('2024-03-15 10:30:00');

→-- 2024-03-15

Today's date from NOW()

sql

SELECT DATE(NOW());

→-- 2024-03-15

Filter by date

sql

SELECT * FROM orders WHERE DATE(created_at) = CURDATE();

→-- today's orders

Date string passthrough

sql

SELECT DATE('2024-03-15');

→-- 2024-03-15

Duration

sql

SELECT DATEDIFF(DATE(completed_at), DATE(created_at)) FROM tasks;

→-- days to complete

⚠Anti-Pattern— WHERE DATE(indexed_col) = '2024-03-15'

Wrapping an indexed column in DATE() causes a full table scan, ignoring the index.

✓ Instead: Use WHERE indexed_col >= '2024-03-15' AND indexed_col < '2024-03-16'.

⇄ vs PostgreSQL: PostgreSQL uses expr::date or DATE_TRUNC('day', expr).

TIME

→ TIME

Extracts the time part from a DATETIME or TIMESTAMP expression as 'HH:MM:SS'.

Signature

TIME(expr)

Parameters

Parameter	Type	Description
expr	DATETIME	A datetime or time expression

Examples

Extract time portion

sql

SELECT TIME('2024-03-15 10:30:45');

→-- 10:30:45

Current time via NOW()

sql

SELECT TIME(NOW());

→-- 10:30:45

Nested extraction

sql

SELECT HOUR(TIME(created_at)) FROM logs;

→-- hour of creation

Filter by time

sql

SELECT * FROM events WHERE TIME(starts_at) BETWEEN '09:00:00' AND '17:00:00';

→-- business hours

TIME supports >24h values

sql

SELECT TIME('300:10:05');

→-- 300:10:05

⚠Anti-Pattern— Using TIME() on an indexed DATETIME for filtering

TIME(indexed_datetime_col) = '10:00:00' cannot use the index and scans the full table.

✓ Instead: Filter on the full DATETIME column with a range or store time separately.

⇄ vs PostgreSQL: PostgreSQL uses expr::time or EXTRACT(hour/minute/second FROM expr).

YEAR

→ INT

Returns the year from a date expression as an integer (1000–9999).

Signature

YEAR(date)

Parameters

Parameter	Type	Description
date	DATE	A date, datetime, or date string

Examples

Year from string

sql

SELECT YEAR('2024-03-15');

→-- 2024

Current year

sql

SELECT YEAR(NOW());

→-- 2024

Column extraction

sql

SELECT YEAR(birth_date) FROM employees;

→-- birth year

Filter by year

sql

SELECT * FROM orders WHERE YEAR(order_date) = 2024;

→-- 2024 orders

Annual aggregation

sql

SELECT COUNT(*) FROM sales GROUP BY YEAR(sale_date);

→-- annual totals

⚠Anti-Pattern— WHERE YEAR(date_col) = 2024 on large tables

YEAR() wrapping an indexed column forces a full scan, making year-range queries slow.

✓ Instead: Use WHERE date_col >= '2024-01-01' AND date_col < '2025-01-01'.

⇄ vs PostgreSQL: PostgreSQL uses EXTRACT(YEAR FROM col) or DATE_PART('year', col).

MONTH

→ INT

Returns the month from a date expression as an integer (1–12).

Signature

MONTH(date)

Parameters

Parameter	Type	Description
date	DATE	A date, datetime, or date string

Examples

Month from string

sql

SELECT MONTH('2024-03-15');

→-- 3

Current month

sql

SELECT MONTH(NOW());

→-- 3

Named month

sql

SELECT MONTHNAME(NOW());

→-- March

Filter by month

sql

SELECT * FROM sales WHERE MONTH(sale_date) = 12;

→-- December sales

Monthly aggregation

sql

SELECT COUNT(*) FROM orders GROUP BY MONTH(order_date);

→-- monthly totals

⚠Anti-Pattern— Filtering a single month without specifying the year

WHERE MONTH(col) = 3 matches March of every year, not just the current year.

✓ Instead: Always pair MONTH() with YEAR() or use a date range condition.

⇄ vs PostgreSQL: PostgreSQL uses EXTRACT(MONTH FROM col).

DAY

→ INT

Returns the day of the month from a date expression (1–31). Alias for DAYOFMONTH().

Signature

DAY(date)

Parameters

Parameter	Type	Description
date	DATE	A date, datetime, or date string

Examples

Day from string

sql

SELECT DAY('2024-03-15');

→-- 15

Current day

sql

SELECT DAY(NOW());

→-- 15

Last day of month

sql

SELECT DAY(LAST_DAY(NOW()));

→-- 31

Filter by day

sql

SELECT * FROM events WHERE DAY(event_date) = 1;

→-- first of month events

Manual format

sql

SELECT CONCAT(YEAR(d),'-',LPAD(MONTH(d),2,'0'),'-',LPAD(DAY(d),2,'0')) FROM t;

→-- manual date string

⚠Anti-Pattern— Scheduling on DAY() = 31

DAY() = 31 skips months with fewer than 31 days, breaking monthly schedules.

✓ Instead: Use LAST_DAY() to find the actual last day of each month.

⇄ vs PostgreSQL: PostgreSQL uses EXTRACT(DAY FROM col).

HOUR

→ INT

Returns the hour from a time or datetime expression (0–23 for DATETIME; can exceed 23 for TIME).

Signature

HOUR(time)

Parameters

Parameter	Type	Description
time	TIME	A time, datetime, or time string

Examples

Hour from time string

sql

SELECT HOUR('10:30:45');

→-- 10

Current hour

sql

SELECT HOUR(NOW());

→-- 10

Elapsed time > 24h

sql

SELECT HOUR('300:10:05');

→-- 300

Office hours filter

sql

SELECT * FROM logs WHERE HOUR(created_at) BETWEEN 9 AND 17;

→-- business hours logs

Hourly aggregation

sql

SELECT COUNT(*) FROM events GROUP BY HOUR(starts_at);

→-- hourly distribution

⚠Anti-Pattern— Assuming HOUR() always returns 0-23

When applied to a TIME column storing elapsed durations, HOUR() can exceed 23.

✓ Instead: Use HOUR() on DATETIME for clock hours; validate range when applied to elapsed TIME.

⇄ vs PostgreSQL: PostgreSQL uses EXTRACT(HOUR FROM col).

MINUTE

→ INT

Returns the minute component from a time or datetime expression (0–59).

Signature

MINUTE(time)

Parameters

Parameter	Type	Description
time	TIME	A time, datetime, or time string

Examples

Minute from string

sql

SELECT MINUTE('10:30:45');

→-- 30

Current minute

sql

SELECT MINUTE(NOW());

→-- 30

Top-of-hour filter

sql

SELECT * FROM tasks WHERE MINUTE(created_at) = 0;

→-- on-the-hour tasks

Convert to minutes

sql

SELECT HOUR(t)*60 + MINUTE(t) AS total_minutes FROM durations;

→-- total minutes

Minute aggregation

sql

SELECT COUNT(*) FROM events GROUP BY HOUR(dt), MINUTE(dt);

→-- per-minute histogram

⚠Anti-Pattern— Rounding timestamps using MINUTE() arithmetic

Manual MINUTE() rounding is error-prone near hour boundaries.

✓ Instead: Use FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(dt)/60)*60) for clean minute truncation.

⇄ vs PostgreSQL: PostgreSQL uses EXTRACT(MINUTE FROM col).

SECOND

→ INT

Returns the seconds component from a time or datetime expression (0–59).

Signature

SECOND(time)

Parameters

Parameter	Type	Description
time	TIME	A time, datetime, or time string

Examples

Seconds from string

sql

SELECT SECOND('10:30:45');

→-- 45

Current second

sql

SELECT SECOND(NOW());

→-- 45

Fractional seconds truncated

sql

SELECT SECOND('10:30:45.789');

→-- 45

To seconds

sql

SELECT HOUR(t)*3600 + MINUTE(t)*60 + SECOND(t) FROM durations;

→-- total seconds

On the minute

sql

SELECT * FROM heartbeats WHERE SECOND(recorded_at) = 0;

→-- minute-mark records

⚠Anti-Pattern— Using SECOND() to get sub-second precision

SECOND('10:30:45.789') returns 45, discarding the .789 fractional part.

✓ Instead: Use MICROSECOND() alongside SECOND() when sub-second values matter.

⇄ vs PostgreSQL: PostgreSQL uses EXTRACT(SECOND FROM col) which includes fractional part.

MICROSECOND

→ INT

Returns the microseconds component from a time or datetime expression (0–999999).

Signature

MICROSECOND(expr)

Parameters

Parameter	Type	Description
expr	TIME	A time or datetime expression with fractional seconds

Examples

Microseconds from string

sql

SELECT MICROSECOND('10:30:45.123456');

→-- 123456

Current microsecond

sql

SELECT MICROSECOND(NOW(6));

→-- e.g. 789012

Half-second

sql

SELECT MICROSECOND('2024-03-15 10:30:45.5');

→-- 500000

Full precision

sql

SELECT SECOND(t), MICROSECOND(t) FROM precision_events;

→-- full sub-second

Sub-second filter

sql

SELECT * FROM traces WHERE MICROSECOND(ts) < 500000;

→-- first half-second

⚠Anti-Pattern— Expecting non-zero MICROSECOND from a DATETIME(0) column

DATETIME without fractional precision stores no sub-second data; MICROSECOND() always returns 0.

✓ Instead: Declare the column as DATETIME(6) to store and retrieve microseconds.

⇄ vs PostgreSQL: PostgreSQL uses EXTRACT(MICROSECONDS FROM col) which includes seconds * 1e6.

DAYOFWEEK

→ INT

Returns the weekday index for a date (1 = Sunday, 2 = Monday, …, 7 = Saturday).

Signature

DAYOFWEEK(date)

Parameters

Parameter	Type	Description
date	DATE	A date or datetime expression

Examples

Friday = 6

sql

SELECT DAYOFWEEK('2024-03-15');

→-- 6

Current day of week

sql

SELECT DAYOFWEEK(NOW());

→-- 1-7

Weekend filter

sql

SELECT * FROM schedules WHERE DAYOFWEEK(shift_date) IN (1, 7);

→-- weekend rows

Human-readable name

sql

SELECT DAYNAME('2024-03-15');

→-- Friday

Weekday distribution

sql

SELECT COUNT(*) FROM orders GROUP BY DAYOFWEEK(order_date);

→-- orders per weekday

⚠Anti-Pattern— Assuming DAYOFWEEK() starts on Monday

DAYOFWEEK() starts on Sunday (1). Using it as Monday=1 causes off-by-one errors in week logic.

✓ Instead: Use WEEKDAY() where Monday=0, or subtract 1 and apply MOD for custom week starts.

⇄ vs PostgreSQL: PostgreSQL uses EXTRACT(DOW FROM col) where 0=Sunday, or ISODOW where 1=Monday.

DAYOFYEAR

→ INT

Returns the day of the year for a date as an integer (1–366).

Signature

DAYOFYEAR(date)

Parameters

Parameter	Type	Description
date	DATE	A date or datetime expression

Examples

75th day of 2024

sql

SELECT DAYOFYEAR('2024-03-15');

→-- 75

Today's ordinal

sql

SELECT DAYOFYEAR(NOW());

→-- current day number

Leap year last day

sql

SELECT DAYOFYEAR('2024-12-31');

→-- 366

Days until year end

sql

SELECT 366 - DAYOFYEAR(NOW()) AS days_remaining FROM DUAL;

→-- days left in year

Day-of-year range

sql

SELECT * FROM events WHERE DAYOFYEAR(event_date) BETWEEN 60 AND 90;

→-- events in ~March

⚠Anti-Pattern— Hard-coding 365 as max DAYOFYEAR

Leap years have 366 days; using 365 as a fixed maximum breaks end-of-year calculations.

✓ Instead: Use DAYOFYEAR(LAST_DAY(CONCAT(YEAR(d), '-12-01'))) to get the year's actual last day number.

⇄ vs PostgreSQL: PostgreSQL uses EXTRACT(DOY FROM col).

WEEKDAY

→ INT

Returns the weekday index for a date (0 = Monday, 1 = Tuesday, …, 6 = Sunday).

Signature

WEEKDAY(date)

Parameters

Parameter	Type	Description
date	DATE	A date or datetime expression

Examples

Friday = 4

sql

SELECT WEEKDAY('2024-03-15');

→-- 4

Current weekday

sql

SELECT WEEKDAY(NOW());

→-- 0-6

Mon-Fri filter

sql

SELECT * FROM shifts WHERE WEEKDAY(shift_date) < 5;

→-- weekday shifts only

Weekend filter

sql

SELECT * FROM events WHERE WEEKDAY(event_date) IN (5, 6);

→-- weekend events

Day-of-week breakdown

sql

SELECT COUNT(*) FROM sales GROUP BY WEEKDAY(sale_date);

→-- sales per weekday

⚠Anti-Pattern— Confusing WEEKDAY() and DAYOFWEEK() numbering

WEEKDAY() Monday=0 and DAYOFWEEK() Sunday=1 are easy to mix up, causing wrong day filters.

✓ Instead: Add a comment or use DAYNAME() for readability when day numbering matters.

⇄ vs PostgreSQL: PostgreSQL uses EXTRACT(ISODOW FROM col) where 1=Monday, 7=Sunday.

WEEK

→ INT

Returns the week number for a date (0–53). The mode parameter controls week numbering behavior.

Signature

WEEK(date[, mode])

Parameters

Parameter	Type	Description
date	DATE	A date or datetime expression
mode	INT	Week numbering mode 0-7 (optional, default from @@default_week_format)

Examples

Default mode week number

sql

SELECT WEEK('2024-03-15');

→-- 10

ISO week (Monday start, min 4 days)

sql

SELECT WEEK('2024-03-15', 1);

→-- 11

Mode 0: first partial week = 0

sql

SELECT WEEK('2024-01-01', 0);

→-- 0

Weekly aggregation

sql

SELECT COUNT(*) FROM orders GROUP BY YEAR(order_date), WEEK(order_date, 1);

→-- weekly totals

Current week filter

sql

SELECT * FROM events WHERE WEEK(event_date, 1) = WEEK(NOW(), 1) AND YEAR(event_date) = YEAR(NOW());

→-- this week's events

⚠Anti-Pattern— Grouping by WEEK() without YEAR()

WEEK(col) = 10 matches week 10 of every year. Always pair with YEAR() for correct weekly grouping.

✓ Instead: GROUP BY YEAR(d), WEEK(d, 1) or use YEARWEEK(d, 1) which combines both.

⇄ vs PostgreSQL: PostgreSQL uses EXTRACT(WEEK FROM col) for ISO week numbers.

WEEKOFYEAR

→ INT

Returns the ISO 8601 week number of the year (1–53). Equivalent to WEEK(date, 3).

Signature

WEEKOFYEAR(date)

Parameters

Parameter	Type	Description
date	DATE	A date or datetime expression

Examples

ISO week 11

sql

SELECT WEEKOFYEAR('2024-03-15');

→-- 11

Current ISO week

sql

SELECT WEEKOFYEAR(NOW());

→-- current ISO week

First ISO week

sql

SELECT WEEKOFYEAR('2024-01-01');

→-- 1

Last week of 2023

sql

SELECT WEEKOFYEAR('2023-12-31');

→-- 52

ISO weekly aggregation

sql

SELECT COUNT(*) FROM orders GROUP BY YEAR(order_date), WEEKOFYEAR(order_date);

→-- weekly totals

⚠Anti-Pattern— Grouping by WEEKOFYEAR() without YEAR()

WEEKOFYEAR() = 1 matches week 1 of every year. Always include YEAR() to avoid cross-year merging.

✓ Instead: Use YEARWEEK(d, 3) which returns a single YYYYWW integer combining year and ISO week.

⇄ vs PostgreSQL: PostgreSQL uses EXTRACT(WEEK FROM col) for ISO 8601 week numbers.

QUARTER

→ INT

Returns the quarter of the year for a date as an integer (1–4).

Signature

QUARTER(date)

Parameters

Parameter	Type	Description
date	DATE	A date or datetime expression

Examples

Q1 (Jan-Mar)

sql

SELECT QUARTER('2024-03-15');

→-- 1

Q3 (Jul-Sep)

sql

SELECT QUARTER('2024-07-01');

→-- 3

Current quarter

sql

SELECT QUARTER(NOW());

→-- current quarter

Quarterly aggregation

sql

SELECT SUM(revenue) FROM sales GROUP BY YEAR(sale_date), QUARTER(sale_date);

→-- quarterly revenue

Quarter filter

sql

SELECT * FROM reports WHERE YEAR(report_date) = 2024 AND QUARTER(report_date) = 4;

→-- Q4 2024 reports

⚠Anti-Pattern— Grouping by QUARTER() without YEAR()

QUARTER(col) = 1 matches Q1 of all years, silently merging multiple years of data.

✓ Instead: Always use GROUP BY YEAR(d), QUARTER(d) for correct quarterly breakdowns.

⇄ vs PostgreSQL: PostgreSQL uses EXTRACT(QUARTER FROM col).

DATE_FORMAT

→ VARCHAR

Formats a date value using a format string with % specifiers.

Signature

DATE_FORMAT(date, format)

Parameters

Parameter	Type	Description
date	DATE/DATETIME	The date to format
format	VARCHAR	Format string with % specifiers like %Y, %m, %d

Examples

ISO format

sql

SELECT DATE_FORMAT('2024-03-15', '%Y-%m-%d');

→-- 2024-03-15

European format

sql

SELECT DATE_FORMAT(NOW(), '%d/%m/%Y');

→-- 15/03/2024

Long form

sql

SELECT DATE_FORMAT(NOW(), '%W, %M %e %Y');

→-- Friday, March 15 2024

Group by month

sql

SELECT DATE_FORMAT(created_at, '%Y-%m') FROM orders GROUP BY 1;

→-- monthly grouping

NULL returns NULL

sql

SELECT DATE_FORMAT(NULL, '%Y');

→-- NULL

⚠Anti-Pattern— Using DATE_FORMAT for sorting

Sorting on a DATE_FORMAT result sorts lexicographically, not chronologically unless format is ISO 8601.

✓ Instead: Sort on the raw date column, use DATE_FORMAT only for display.

⇄ vs PostgreSQL: PostgreSQL uses TO_CHAR(date, 'YYYY-MM-DD') with different format codes.

TIME_FORMAT

→ VARCHAR

Formats a time value using a format string with % specifiers.

Signature

TIME_FORMAT(time, format)

Parameters

Parameter	Type	Description
time	TIME	The time value to format
format	VARCHAR	Format string; only time specifiers (%H, %i, %s, %p) are valid

Examples

24-hour short

sql

SELECT TIME_FORMAT('14:30:00', '%H:%i');

→-- 14:30

12-hour with AM/PM

sql

SELECT TIME_FORMAT('14:30:00', '%h:%i %p');

→-- 02:30 PM

%T is shorthand for %H:%i:%s

sql

SELECT TIME_FORMAT(CURTIME(), '%T');

→-- 14:30:00

TIME can exceed 24h

sql

SELECT TIME_FORMAT('838:59:59', '%H:%i:%s');

→-- 838:59:59

NULL input returns NULL

sql

SELECT TIME_FORMAT(NULL, '%H');

→-- NULL

⚠Anti-Pattern— Using date specifiers in TIME_FORMAT

Specifiers like %Y or %d are not valid in TIME_FORMAT and return empty strings.

✓ Instead: Use DATE_FORMAT for date parts or combined datetime formatting.

⇄ vs PostgreSQL: PostgreSQL uses TO_CHAR(time_val, 'HH24:MI:SS').

STR_TO_DATE

→ DATE/DATETIME/TIME

Parses a string into a DATE, DATETIME, or TIME using a format string.

Signature

STR_TO_DATE(str, format)

Parameters

Parameter	Type	Description
str	VARCHAR	The string to parse
format	VARCHAR	Format string matching the input string's layout

Examples

European date string

sql

SELECT STR_TO_DATE('15-03-2024', '%d-%m-%Y');

→-- 2024-03-15

Long month name

sql

SELECT STR_TO_DATE('March 15, 2024', '%M %d, %Y');

→-- 2024-03-15

Full datetime

sql

SELECT STR_TO_DATE('2024-03-15 14:30:00', '%Y-%m-%d %H:%i:%s');

→-- 2024-03-15 14:30:00

Returns NULL on failure

sql

SELECT STR_TO_DATE('invalid', '%Y-%m-%d');

→-- NULL

Parsing on insert

sql

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

→-- inserts 2024-03-15

⚠Anti-Pattern— Relying on implicit string conversion

Inserting date strings without STR_TO_DATE risks silent conversion errors for non-ISO formats.

✓ Instead: Always use STR_TO_DATE when input is not in YYYY-MM-DD format.

⇄ vs PostgreSQL: PostgreSQL uses TO_DATE(str, 'DD-MM-YYYY') with different format codes.

DATE_ADD

→ DATE/DATETIME

Adds a time interval to a date or datetime value.

Signature

DATE_ADD(date, INTERVAL expr unit)

Parameters

Parameter	Type	Description
date	DATE/DATETIME	The starting date or datetime
expr	INT/VARCHAR	The interval amount to add
unit	keyword	Interval unit: DAY, MONTH, YEAR, HOUR, MINUTE, SECOND, etc.

Examples

Add 7 days

sql

SELECT DATE_ADD('2024-03-15', INTERVAL 7 DAY);

→-- 2024-03-22

Add one month

sql

SELECT DATE_ADD('2024-03-15', INTERVAL 1 MONTH);

→-- 2024-04-15

Add hours to current time

sql

SELECT DATE_ADD(NOW(), INTERVAL 2 HOUR);

→-- 2 hours from now

Month-end clamping

sql

SELECT DATE_ADD('2024-01-31', INTERVAL 1 MONTH);

→-- 2024-02-29

Negative interval subtracts

sql

SELECT DATE_ADD('2024-03-15', INTERVAL -3 DAY);

→-- 2024-03-12

⚠Anti-Pattern— Adding months to month-end dates

DATE_ADD('2024-01-31', INTERVAL 1 MONTH) clamps to the last day of February, which may surprise you.

✓ Instead: Account for month-end edge cases in application logic when needed.

⇄ vs PostgreSQL: PostgreSQL uses date + INTERVAL '7 days' operator syntax.

DATE_SUB

→ DATE/DATETIME

Subtracts a time interval from a date or datetime value.

Signature

DATE_SUB(date, INTERVAL expr unit)

Parameters

Parameter	Type	Description
date	DATE/DATETIME	The starting date or datetime
expr	INT/VARCHAR	The interval amount to subtract
unit	keyword	Interval unit: DAY, MONTH, YEAR, HOUR, MINUTE, SECOND, etc.

Examples

Subtract 7 days

sql

SELECT DATE_SUB('2024-03-15', INTERVAL 7 DAY);

→-- 2024-03-08

Subtract one month

sql

SELECT DATE_SUB('2024-03-15', INTERVAL 1 MONTH);

→-- 2024-02-15

Rolling 30-day window

sql

SELECT DATE_SUB(NOW(), INTERVAL 30 DAY);

→-- 30 days ago

Filter recent rows

sql

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

→-- last 7 days

Cross-day boundary

sql

SELECT DATE_SUB('2024-03-01', INTERVAL 1 SECOND);

→-- 2024-02-29 23:59:59

⚠Anti-Pattern— Using NOW() - INTERVAL in WHERE without index

Wrapping an indexed column in a function prevents index usage.

✓ Instead: Write WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) so the column stays unwrapped.

⇄ vs PostgreSQL: PostgreSQL uses date - INTERVAL '7 days'.

ADDDATE

→ DATE/DATETIME

Adds days or an interval to a date. Alias of DATE_ADD when used with INTERVAL.

Signatures

ADDDATE(date, INTERVAL expr unit)

ADDDATE(date, days)

Parameters

Parameter	Type	Description
date	DATE/DATETIME	The starting date
days / expr	INT / INTERVAL	Number of days to add, or an interval expression

Examples

Add 10 days (integer form)

sql

SELECT ADDDATE('2024-03-15', 10);

→-- 2024-03-25

INTERVAL form identical to DATE_ADD

sql

SELECT ADDDATE('2024-03-15', INTERVAL 1 MONTH);

→-- 2024-04-15

Add zero days

sql

SELECT ADDDATE(CURDATE(), 0);

→-- today's date

Negative days subtract

sql

SELECT ADDDATE('2024-03-15', -5);

→-- 2024-03-10

NULL propagates

sql

SELECT ADDDATE(NULL, 5);

→-- NULL

⚠Anti-Pattern— Confusing ADDDATE with ADDTIME

ADDDATE adds days or date intervals; ADDTIME adds a time expression to a datetime.

✓ Instead: Use ADDTIME to add hours/minutes/seconds to a DATETIME value.

⇄ vs PostgreSQL: PostgreSQL uses date + N or date + INTERVAL '...'.

SUBDATE

→ DATE/DATETIME

Subtracts days or an interval from a date. Alias of DATE_SUB when used with INTERVAL.

Signatures

SUBDATE(date, INTERVAL expr unit)

SUBDATE(date, days)

Parameters

Parameter	Type	Description
date	DATE/DATETIME	The starting date
days / expr	INT / INTERVAL	Number of days to subtract, or an interval expression

Examples

Subtract 5 days

sql

SELECT SUBDATE('2024-03-15', 5);

→-- 2024-03-10

INTERVAL form identical to DATE_SUB

sql

SELECT SUBDATE('2024-03-15', INTERVAL 1 MONTH);

→-- 2024-02-15

Rolling window

sql

SELECT SUBDATE(CURDATE(), 30);

→-- 30 days ago

Year boundary crossing

sql

SELECT SUBDATE('2024-01-01', 1);

→-- 2023-12-31

NULL propagates

sql

SELECT SUBDATE(NULL, 3);

→-- NULL

⚠Anti-Pattern— Using SUBDATE where DATEDIFF is appropriate

SUBDATE returns a new date; use DATEDIFF when you need the numeric difference between two dates.

✓ Instead: Use DATEDIFF(date1, date2) to get the integer day difference.

⇄ vs PostgreSQL: PostgreSQL uses date - N or date - INTERVAL '...'.

ADDTIME

→ DATETIME/TIME

Adds a time expression to a datetime or time value.

Signature

ADDTIME(expr1, expr2)

Parameters

Parameter	Type	Description
expr1	DATETIME/TIME	The base datetime or time value
expr2	TIME/VARCHAR	The time amount to add, e.g. '01:30:00'

Examples

Add 1.5 hours

sql

SELECT ADDTIME('2024-03-15 10:00:00', '01:30:00');

→-- 2024-03-15 11:30:00

Add 30 minutes to TIME

sql

SELECT ADDTIME('10:00:00', '00:30:00');

→-- 10:30:00

Add to current datetime

sql

SELECT ADDTIME(NOW(), '00:05:00');

→-- 5 minutes from now

TIME can exceed 24 hours

sql

SELECT ADDTIME('23:30:00', '01:00:00');

→-- 24:30:00

Day rollover

sql

SELECT ADDTIME('2024-03-15 23:00:00', '02:00:00');

→-- 2024-03-16 01:00:00

⚠Anti-Pattern— Confusing ADDTIME and DATE_ADD for hour addition

ADDTIME requires a string like '01:00:00'; DATE_ADD(dt, INTERVAL 1 HOUR) is less error-prone.

✓ Instead: Prefer DATE_ADD(dt, INTERVAL N HOUR) for adding whole hours.

⇄ vs PostgreSQL: PostgreSQL uses datetime + INTERVAL '1 hour 30 minutes'.

SUBTIME

→ DATETIME/TIME

Subtracts a time expression from a datetime or time value.

Signature

SUBTIME(expr1, expr2)

Parameters

Parameter	Type	Description
expr1	DATETIME/TIME	The base datetime or time value
expr2	TIME/VARCHAR	The time amount to subtract, e.g. '00:30:00'

Examples

Subtract 1.5 hours

sql

SELECT SUBTIME('2024-03-15 10:00:00', '01:30:00');

→-- 2024-03-15 08:30:00

Subtract from TIME

sql

SELECT SUBTIME('10:00:00', '00:30:00');

→-- 09:30:00

Recent time

sql

SELECT SUBTIME(NOW(), '00:05:00');

→-- 5 minutes ago

Day boundary crossing

sql

SELECT SUBTIME('2024-03-15 01:00:00', '02:00:00');

→-- 2024-03-14 23:00:00

NULL propagates

sql

SELECT SUBTIME(NULL, '01:00:00');

→-- NULL

⚠Anti-Pattern— Using SUBTIME where TIMEDIFF is needed

SUBTIME modifies a datetime by subtracting time; TIMEDIFF returns the difference between two times.

✓ Instead: Use TIMEDIFF(t1, t2) to get the TIME difference between two values.

⇄ vs PostgreSQL: PostgreSQL uses datetime - INTERVAL '30 minutes'.

DATEDIFF

→ INT

Returns the number of days between two date values (expr1 - expr2).

Signature

DATEDIFF(expr1, expr2)

Parameters

Parameter	Type	Description
expr1	DATE/DATETIME	The later (minuend) date
expr2	DATE/DATETIME	The earlier (subtrahend) date

Examples

7-day difference

sql

SELECT DATEDIFF('2024-03-22', '2024-03-15');

→-- 7

Negative when expr1 < expr2

sql

SELECT DATEDIFF('2024-01-01', '2024-12-31');

→-- -365

Age in days

sql

SELECT DATEDIFF(NOW(), created_at) FROM orders;

→-- days since order

Time part ignored

sql

SELECT DATEDIFF('2024-03-15 23:59:59', '2024-03-14 00:00:01');

→-- 1

NULL returns NULL

sql

SELECT DATEDIFF(NULL, CURDATE());

→-- NULL

⚠Anti-Pattern— Using DATEDIFF for sub-day precision

DATEDIFF('2024-03-15 23:00', '2024-03-15 01:00') returns 0 because only dates are compared.

✓ Instead: Use TIMESTAMPDIFF(HOUR, dt1, dt2) for hour-level differences.

⇄ vs PostgreSQL: PostgreSQL uses date1 - date2 operator returning integer days.

TIMEDIFF

→ TIME

Returns the difference between two time or datetime values as a TIME value.

Signature

TIMEDIFF(expr1, expr2)

Parameters

Parameter	Type	Description
expr1	TIME/DATETIME	The first (later) time or datetime
expr2	TIME/DATETIME	The second (earlier) time or datetime

Examples

Difference between two times

sql

SELECT TIMEDIFF('10:30:00', '08:00:00');

→-- 02:30:00

Datetime difference

sql

SELECT TIMEDIFF('2024-03-15 12:00:00', '2024-03-15 08:00:00');

→-- 04:00:00

Negative when expr1 < expr2

sql

SELECT TIMEDIFF('08:00:00', '10:30:00');

→-- -02:30:00

Extract hours from difference

sql

SELECT HOUR(TIMEDIFF(end_at, start_at)) FROM shifts;

→-- hours worked

NULL propagates

sql

SELECT TIMEDIFF(NULL, '08:00:00');

→-- NULL

⚠Anti-Pattern— Using TIMEDIFF for multi-day spans

TIMEDIFF clips at ±838:59:59. Differences larger than ~35 days will be capped.

✓ Instead: Use TIMESTAMPDIFF(HOUR, dt1, dt2) for large datetime spans.

⇄ vs PostgreSQL: PostgreSQL uses t1 - t2 returning an INTERVAL.

TIMESTAMPDIFF

→ INT

Returns the integer difference between two datetimes in a specified unit.

Signature

TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)

Parameters

Parameter	Type	Description
unit	keyword	Unit: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR
datetime_expr1	DATETIME	The earlier datetime (subtracted from expr2)
datetime_expr2	DATETIME	The later datetime

Examples

Age in years

sql

SELECT TIMESTAMPDIFF(YEAR, '1990-06-15', '2024-03-15');

→-- 33

Day difference

sql

SELECT TIMESTAMPDIFF(DAY, '2024-03-01', '2024-03-15');

→-- 14

Truncated hours

sql

SELECT TIMESTAMPDIFF(HOUR, '2024-03-15 08:00', '2024-03-15 14:30');

→-- 6

Subscription tenure

sql

SELECT TIMESTAMPDIFF(MONTH, created_at, NOW()) FROM subscriptions;

→-- subscription age months

Event duration

sql

SELECT TIMESTAMPDIFF(SECOND, start_time, end_time) FROM events;

→-- duration in seconds

⚠Anti-Pattern— Expecting TIMESTAMPDIFF(YEAR) to round

TIMESTAMPDIFF(YEAR, dob, CURDATE()) returns 0 if the birthday hasn't occurred yet this year.

✓ Instead: Use FLOOR(DATEDIFF(CURDATE(), dob) / 365.25) for a simpler age approximation if rounding matters.

⇄ vs PostgreSQL: PostgreSQL uses DATE_PART('year', age(d2, d1)) or EXTRACT(EPOCH FROM d2-d1).

TIMESTAMPADD

→ DATETIME

Adds an integer interval in a specified unit to a datetime expression.

Signature

TIMESTAMPADD(unit, interval, datetime_expr)

Parameters

Parameter	Type	Description
unit	keyword	Unit: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR
interval	INT	Number of units to add (negative to subtract)
datetime_expr	DATETIME/DATE	The base datetime value

Examples

Add 7 days

sql

SELECT TIMESTAMPADD(DAY, 7, '2024-03-15');

→-- 2024-03-22

Month-end clamping

sql

SELECT TIMESTAMPADD(MONTH, 3, '2024-01-31');

→-- 2024-04-30

Negative subtracts

sql

SELECT TIMESTAMPADD(HOUR, -2, NOW());

→-- 2 hours ago

Anniversary

sql

SELECT TIMESTAMPADD(YEAR, 1, CURDATE());

→-- same day next year

Add 90 minutes

sql

SELECT TIMESTAMPADD(MINUTE, 90, '2024-03-15 09:00:00');

→-- 2024-03-15 10:30:00

⚠Anti-Pattern— Using TIMESTAMPADD when DATE_ADD is clearer

DATE_ADD(dt, INTERVAL 7 DAY) is more readable for most use cases.

✓ Instead: Reserve TIMESTAMPADD for dynamic unit scenarios where the unit is a variable.

⇄ vs PostgreSQL: PostgreSQL uses dt + (N || ' days')::INTERVAL for dynamic unit intervals.

FROM_UNIXTIME

→ DATETIME/VARCHAR

Converts a Unix timestamp (seconds since epoch) to a DATETIME or formatted string.

Signatures

FROM_UNIXTIME(unix_timestamp)

FROM_UNIXTIME(unix_timestamp, format)

Parameters

Parameter	Type	Description
unix_timestamp	INT/DECIMAL	Unix timestamp in seconds (supports sub-second decimals)
format	VARCHAR	Optional DATE_FORMAT-style format string

Examples

Epoch origin (UTC)

sql

SELECT FROM_UNIXTIME(0);

→-- 1970-01-01 00:00:00

Sample Unix timestamp

sql

SELECT FROM_UNIXTIME(1710504000);

→-- 2024-03-15 12:00:00

With format string

sql

SELECT FROM_UNIXTIME(1710504000, '%Y-%m-%d');

→-- 2024-03-15

Round-trip with UNIX_TIMESTAMP

sql

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());

→-- current datetime

Sub-second support

sql

SELECT FROM_UNIXTIME(1710504000.5);

→-- 2024-03-15 12:00:00.500000

⚠Anti-Pattern— Assuming UTC output from FROM_UNIXTIME

FROM_UNIXTIME converts to the session time zone, not necessarily UTC.

✓ Instead: Set @@time_zone = '+00:00' or use CONVERT_TZ for explicit UTC output.

⇄ vs PostgreSQL: PostgreSQL uses to_timestamp(unix_ts) AT TIME ZONE 'UTC'.

UNIX_TIMESTAMP

→ INT/DECIMAL

Returns the current time or a given datetime as a Unix timestamp (seconds since 1970-01-01 UTC).

Signatures

UNIX_TIMESTAMP()

UNIX_TIMESTAMP(date)

Parameters

Parameter	Type	Description
date	DATE/DATETIME/VARCHAR	Optional datetime to convert; defaults to NOW()

Examples

Current time as Unix timestamp

sql

SELECT UNIX_TIMESTAMP();

→-- 1710504000

Specific datetime

sql

SELECT UNIX_TIMESTAMP('2024-03-15 12:00:00');

→-- 1710504000

Date only (midnight)

sql

SELECT UNIX_TIMESTAMP('2024-03-15');

→-- 1710460800

Round-trip

sql

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2024-03-15 12:00:00'));

→-- 2024-03-15 12:00:00

NULL input returns NULL

sql

SELECT UNIX_TIMESTAMP(NULL);

→-- NULL

⚠Anti-Pattern— Storing Unix timestamps in INT columns for all use cases

Unix timestamps are harder to query readably and lose time zone context.

✓ Instead: Use DATETIME or TIMESTAMP columns for most storage; convert to Unix only for interop.

⇄ vs PostgreSQL: PostgreSQL uses EXTRACT(EPOCH FROM datetime).

FROM_DAYS

→ DATE

Converts a day number (days since year 0) to a DATE value.

Signature

FROM_DAYS(N)

Parameters

Parameter	Type	Description
N	INT	Day number since year 0 (day 1 = 0001-01-01 in proleptic Gregorian)

Examples

Day number to date

sql

SELECT FROM_DAYS(738960);

→-- 2024-03-15

Round-trip with TO_DAYS

sql

SELECT FROM_DAYS(TO_DAYS('2024-03-15'));

→-- 2024-03-15

Add days via day number

sql

SELECT FROM_DAYS(TO_DAYS(CURDATE()) + 30);

→-- 30 days from today

Low day numbers return zero date

sql

SELECT FROM_DAYS(1);

→-- 0000-00-00

First valid date

sql

SELECT FROM_DAYS(366);

→-- 0001-01-01

⚠Anti-Pattern— Using FROM_DAYS for general date arithmetic

Day-number arithmetic with FROM_DAYS/TO_DAYS is less readable than DATE_ADD/DATEDIFF.

✓ Instead: Use DATE_ADD(date, INTERVAL N DAY) and DATEDIFF for clarity.

⇄ vs PostgreSQL: PostgreSQL uses DATE '0001-01-01' + N * INTERVAL '1 day'.

TO_DAYS

→ INT

Returns the number of days since year 0 for a given date.

Signature

TO_DAYS(date)

Parameters

Parameter	Type	Description
date	DATE/DATETIME/VARCHAR	The date to convert to a day number

Examples

Day number for 2024-03-15

sql

SELECT TO_DAYS('2024-03-15');

→-- 738960

Day difference

sql

SELECT TO_DAYS('2024-03-15') - TO_DAYS('2024-03-01');

→-- 14

Round-trip with FROM_DAYS

sql

SELECT FROM_DAYS(TO_DAYS(CURDATE()));

→-- today

NULL returns NULL

sql

SELECT TO_DAYS(NULL);

→-- NULL

Zero date

sql

SELECT TO_DAYS('0000-00-00');

→-- 0

⚠Anti-Pattern— Using TO_DAYS subtraction instead of DATEDIFF

TO_DAYS(d1) - TO_DAYS(d2) works but DATEDIFF(d1, d2) is more expressive and readable.

✓ Instead: Use DATEDIFF for day-count differences.

⇄ vs PostgreSQL: PostgreSQL uses date - DATE '0001-01-01' + 366 for a day number.

TO_SECONDS

→ INT

Returns the number of seconds since year 0 for a given date or datetime.

Signature

TO_SECONDS(expr)

Parameters

Parameter	Type	Description
expr	DATE/DATETIME/VARCHAR	The date or datetime to convert

Examples

Seconds for a date

sql

SELECT TO_SECONDS('2024-03-15');

→-- 63883641600

With time component

sql

SELECT TO_SECONDS('2024-03-15 12:00:00');

→-- 63883684800

Elapsed seconds

sql

SELECT TO_SECONDS(NOW()) - TO_SECONDS(created_at) FROM events;

→-- age in seconds

One day in seconds

sql

SELECT TO_SECONDS('2024-03-15') - TO_SECONDS('2024-03-14');

→-- 86400

NULL returns NULL

sql

SELECT TO_SECONDS(NULL);

→-- NULL

⚠Anti-Pattern— Using TO_SECONDS instead of UNIX_TIMESTAMP for epoch arithmetic

TO_SECONDS counts from year 0, not 1970. Its large values are harder to reason about.

✓ Instead: Use UNIX_TIMESTAMP for epoch-based arithmetic and interop with application code.

◆MariaDB Note

Available since MariaDB 5.5.

⇄ vs PostgreSQL: PostgreSQL uses EXTRACT(EPOCH FROM datetime) for Unix-epoch seconds.

MAKEDATE

→ DATE

Returns a DATE for a given year and day-of-year number.

Signature

MAKEDATE(year, dayofyear)

Parameters

Parameter	Type	Description
year	INT	The four-digit year
dayofyear	INT	Day of year (1–366); values > year length overflow to next year

Examples

75th day of 2024

sql

SELECT MAKEDATE(2024, 75);

→-- 2024-03-15

First day of year

sql

SELECT MAKEDATE(2024, 1);

→-- 2024-01-01

Last day of leap year

sql

SELECT MAKEDATE(2024, 366);

→-- 2024-12-31

Overflow to next year

sql

SELECT MAKEDATE(2024, 400);

→-- 2025-02-04

Day 0 returns NULL

sql

SELECT MAKEDATE(2024, 0);

→-- NULL

⚠Anti-Pattern— Using MAKEDATE with zero or negative dayofyear

MAKEDATE returns NULL for dayofyear <= 0.

✓ Instead: Validate that dayofyear >= 1 before calling MAKEDATE.

⇄ vs PostgreSQL: PostgreSQL: use (year::text||'-01-01')::date + (dayofyear-1).

MAKETIME

→ TIME

Constructs a TIME value from integer hour, minute, and second components.

Signature

MAKETIME(hour, minute, second)

Parameters

Parameter	Type	Description
hour	INT	Hour component (can exceed 23 for large TIME values)
minute	INT	Minute component (0–59)
second	DECIMAL	Second component (0–59.999999), supports microseconds

Examples

Build a time value

sql

SELECT MAKETIME(14, 30, 0);

→-- 14:30:00

Maximum TIME value

sql

SELECT MAKETIME(838, 59, 59);

→-- 838:59:59

Midnight

sql

SELECT MAKETIME(0, 0, 0);

→-- 00:00:00

With microseconds

sql

SELECT MAKETIME(10, 30, 45.5);

→-- 10:30:45.500000

NULL in any arg returns NULL

sql

SELECT MAKETIME(NULL, 30, 0);

→-- NULL

⚠Anti-Pattern— Using MAKETIME to build a DATETIME

MAKETIME returns a TIME only. To build a DATETIME, combine with MAKEDATE using TIMESTAMP().

✓ Instead: Use TIMESTAMP(MAKEDATE(year, doy), MAKETIME(h, m, s)) for a full DATETIME.

⇄ vs PostgreSQL: PostgreSQL uses MAKE_TIME(hour, min, sec).

PERIOD_ADD

→ INT

Adds a number of months to a period in YYMM or YYYYMM format and returns YYYYMM.

Signature

PERIOD_ADD(period, months)

Parameters

Parameter	Type	Description
period	INT	Period in YYMM or YYYYMM format (not a date)
months	INT	Number of months to add (negative to subtract)

Examples

Add 3 months

sql

SELECT PERIOD_ADD(202403, 3);

→-- 202406

Year rollover

sql

SELECT PERIOD_ADD(202412, 2);

→-- 202502

Subtract months

sql

SELECT PERIOD_ADD(202403, -2);

→-- 202401

YYMM format expands to YYYYMM

sql

SELECT PERIOD_ADD(9903, 1);

→-- 199904

Zero months unchanged

sql

SELECT PERIOD_ADD(202403, 0);

→-- 202403

⚠Anti-Pattern— Treating PERIOD_ADD result as a date

PERIOD_ADD returns an integer like 202406, not a DATE or DATETIME.

✓ Instead: Convert back to a date with STR_TO_DATE(PERIOD_ADD(p, n), '%Y%m') if needed.

⇄ vs PostgreSQL: PostgreSQL uses date + INTERVAL 'N months' directly on DATE types.

PERIOD_DIFF

→ INT

Returns the number of months between two periods in YYMM or YYYYMM format.

Signature

PERIOD_DIFF(period1, period2)

Parameters

Parameter	Type	Description
period1	INT	First period in YYMM or YYYYMM format
period2	INT	Second period in YYMM or YYYYMM format

Examples

3 months apart

sql

SELECT PERIOD_DIFF(202406, 202403);

→-- 3

Negative when period1 < period2

sql

SELECT PERIOD_DIFF(202403, 202406);

→-- -3

Cross-year difference

sql

SELECT PERIOD_DIFF(202501, 202312);

→-- 13

YYMM format

sql

SELECT PERIOD_DIFF(9903, 9901);

→-- 2

Same period

sql

SELECT PERIOD_DIFF(202403, 202403);

→-- 0

⚠Anti-Pattern— Using PERIOD_DIFF on actual DATE columns

PERIOD_DIFF expects YYMM/YYYYMM integers, not DATE values.

✓ Instead: Use TIMESTAMPDIFF(MONTH, date1, date2) for month differences between real dates.

⇄ vs PostgreSQL: PostgreSQL uses DATE_PART('month', age(d1, d2)) for month-level date differences.

CONVERT_TZ

→ DATETIME

Converts a DATETIME from one time zone to another.

Signature

CONVERT_TZ(dt, from_tz, to_tz)

Parameters

Parameter	Type	Description
dt	DATETIME	The datetime value to convert
from_tz	VARCHAR	Source time zone (e.g. 'UTC', '+00:00', 'US/Eastern')
to_tz	VARCHAR	Target time zone (same format as from_tz)

Examples

UTC to Eastern

sql

SELECT CONVERT_TZ('2024-03-15 12:00:00', 'UTC', 'US/Eastern');

→-- 2024-03-15 08:00:00

Offset notation

sql

SELECT CONVERT_TZ('2024-03-15 12:00:00', '+00:00', '+05:30');

→-- 2024-03-15 17:30:00

UTC to session TZ

sql

SELECT CONVERT_TZ(UTC_TIMESTAMP(), 'UTC', @@time_zone);

→-- current local time

DST gap may return NULL

sql

SELECT CONVERT_TZ('2024-03-10 02:30:00', 'US/Eastern', 'UTC');

→-- NULL or adjusted

NULL propagates

sql

SELECT CONVERT_TZ(NULL, 'UTC', 'UTC');

→-- NULL

⚠Anti-Pattern— Using named zones without loading TZ tables

CONVERT_TZ with named zones returns NULL if the time zone tables are not loaded.

✓ Instead: Use offset notation ('+05:30') for portability, or populate TZ tables via mysql_tzinfo_to_sql.

⇄ vs PostgreSQL: PostgreSQL uses AT TIME ZONE operator: dt AT TIME ZONE 'UTC'.

LAST_DAY

→ DATE

Returns the last day of the month for a given date or datetime.

Signature

LAST_DAY(date)

Parameters

Parameter	Type	Description
date	DATE/DATETIME/VARCHAR	Any date within the target month

Examples

Last day of March

sql

SELECT LAST_DAY('2024-03-15');

→-- 2024-03-31

Leap year February

sql

SELECT LAST_DAY('2024-02-01');

→-- 2024-02-29

Non-leap February

sql

SELECT LAST_DAY('2023-02-01');

→-- 2023-02-28

Days in month

sql

SELECT DAY(LAST_DAY('2024-03-15'));

→-- 31

NULL returns NULL

sql

SELECT LAST_DAY(NULL);

→-- NULL

⚠Anti-Pattern— Hardcoding month-end dates

Hardcoding 28, 30, or 31 for month-end logic breaks for leap years and different months.

✓ Instead: Always use LAST_DAY(date) for dynamic month-end dates.

⇄ vs PostgreSQL: PostgreSQL uses DATE_TRUNC('month', date) + INTERVAL '1 month - 1 day'.

EXTRACT

→ INT

Extracts a specified date or time part from a datetime expression.

Signature

EXTRACT(unit FROM date)

Parameters

Parameter	Type	Description
unit	keyword	Part to extract: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, WEEK, QUARTER, etc.
date	DATE/DATETIME/TIME	The datetime value to extract from

Examples

Extract year

sql

SELECT EXTRACT(YEAR FROM '2024-03-15');

→-- 2024

Extract month

sql

SELECT EXTRACT(MONTH FROM '2024-03-15');

→-- 3

Extract day

sql

SELECT EXTRACT(DAY FROM '2024-03-15');

→-- 15

Extract hour

sql

SELECT EXTRACT(HOUR FROM '2024-03-15 14:30:00');

→-- 14

Compound YEAR_MONTH unit

sql

SELECT EXTRACT(YEAR_MONTH FROM '2024-03-15');

→-- 202403

⚠Anti-Pattern— Using string functions to extract date parts

Using SUBSTRING or LEFT on a date string to get the year is fragile.

✓ Instead: Always use EXTRACT(YEAR FROM date) or YEAR(date) for reliable date part extraction.

⇄ vs PostgreSQL: PostgreSQL uses EXTRACT(YEAR FROM date) with identical SQL-standard syntax.

Related MariaDB Categories

String Functions →Aggregate Functions →Numeric Functions →

MariaDB Date & Time Functions

Jump to function

What are MariaDB Date & Time Functions?

NOW

Signature

Parameters

Examples

SYSDATE

Signature

Parameters

Examples

CURDATE

Signature

Examples

CURRENT_DATE

Signatures

Examples

CURTIME

Signature

Parameters

Examples

CURRENT_TIME

Signatures

Parameters

Examples

CURRENT_TIMESTAMP

Signatures

Parameters

Examples

UTC_DATE

Signature

Examples

UTC_TIME

Signature

Parameters

Examples

UTC_TIMESTAMP

Signature

Parameters

Examples

DATE

Signature

Parameters

Examples

TIME

Signature

Parameters

Examples

YEAR

Signature

Parameters

Examples

MONTH

Signature

Parameters

Examples

DAY

Signature

Parameters

Examples

HOUR

Signature

Parameters

Examples

MINUTE

Signature

Parameters

Examples

SECOND

Signature

Parameters

Examples

MICROSECOND

Signature

Parameters

Examples

DAYOFWEEK

Signature

Parameters

Examples