Useful MYSQL functions

#sql #mysql

Date & Time Functions

Function	Use Case
`NOW()`	Current date & time (timestamp).
`CURDATE()`	Current date only (YYYY-MM-DD).
`CURTIME()`	Current time only (HH:MM:SS).
`DATE(datetime)`	Extracts the date portion only.
`TIME(datetime)`	Extracts the time portion only.
`CAST(expr AS DATE)`	Converts datetime to date.
`YEAR(date)`	Extracts year (e.g., `2005`).
`MONTH(date)`	Extracts month number (1–12).
`DAY(date)` / `DAYOFMONTH(date)`	Extracts day of the month.
`DAYNAME(date)`	Returns name of weekday (e.g., 'Monday').
`HOUR(time)` / `MINUTE(time)` / `SECOND(time)`	Extracts parts of time.
`WEEK(date)`	Week number of the year.
`DAYOFWEEK(date)`	1=Sunday, 7=Saturday.
`DAYOFYEAR(date)`	Day number in the year (1–366).
`QUARTER(date)`	Quarter of the year (1–4).
`LAST_DAY(date)`	Last day of the month.
`EXTRACT(unit FROM date)`	Extracts a specific part (e.g., YEAR, MONTH, DAY).
`DATE_ADD(date, INTERVAL n unit)`	Add time (e.g., `+7 DAYS`).
`DATE_SUB(date, INTERVAL n unit)`	Subtract time.
`DATEDIFF(d1, d2)`	Days between two dates. (`d1 - d2`).
`TIMESTAMPDIFF(unit, d1, d2)`	Difference in years, months, hours, etc.
`STR_TO_DATE(str, format)`	Parse a string into a date.
`DATE_FORMAT(date, format)`	Format a date to string.
`TO_DAYS(date)`	Convert date to integer (days since year 0).
`FROM_DAYS(n)`	Convert integer back to date.
`UNIX_TIMESTAMP(date)`	Convert to Unix epoch (seconds).
`FROM_UNIXTIME(epoch)`	Convert epoch back to datetime.

Function	Use Case
`CONCAT(s1, s2, ...)`	Join strings.
`CONCAT_WS(sep, s1, s2, ...)`	Join strings with separator.
`SUBSTRING(str, pos, len)`	Extract substring.
`LEFT(str, len)` / `RIGHT(str, len)`	Extract from left/right.
`UPPER(str)` / `LOWER(str)`	Case conversion.
`TRIM(str)`	Remove leading/trailing spaces.
`LTRIM(str)` / `RTRIM(str)`	Remove spaces from left/right.
`REPLACE(str, from, to)`	Replace substring.
`INSTR(str, substr)`	Position of substring (1-based).
`LOCATE(substr, str)`	Like `INSTR` but with argument order swapped.
`REVERSE(str)`	Reverse a string.
`LPAD(str, len, pad)`	Left pad with chars.
`RPAD(str, len, pad)`	Right pad with chars.
`FORMAT(number, decimals)`	Format number with commas.

Function	Use Case
`ABS(x)`	Absolute value.
`CEIL(x)` / `CEILING(x)`	Round up.
`FLOOR(x)`	Round down.
`ROUND(x, d)`	Round to `d` decimal places.
`TRUNCATE(x, d)`	Cut off decimals (no rounding).
`MOD(x, y)`	Modulus (remainder).
`POWER(x, y)` / `POW(x, y)`	Exponentiation.
`SQRT(x)`	Square root.
`RAND()`	Random float between 0 and 1.
`SIGN(x)`	-1 if negative, 0 if zero, 1 if positive.
`GREATEST(x, y, …)`	Largest value.
`LEAST(x, y, …)`	Smallest value.

Function	Use Case
`COUNT(*)`	Count rows.
`SUM(col)`	Sum values.
`AVG(col)`	Average.
`MIN(col)`	Minimum.
`MAX(col)`	Maximum.
`GROUP_CONCAT(col)`	Concatenate values within a group.

Function	Use Case
`IF(expr, true_val, false_val)`	If-else logic.
`IFNULL(expr, alt)`	Replace `NULL` with default.
`NULLIF(expr1, expr2)`	Return NULL if values are equal.
`CASE WHEN ... THEN ... ELSE ... END`	Multi-branch conditional.
`COALESCE(v1, v2, …)`	First non-null value.

Function	Use Case
`TO_DAYS(date)`	Convert date to integer days (used in partitioning).
`YEAR(date)` / `MONTH(date)`	Extract year/month (can be used in partitioning).
`DAY(date)`	Extract day of month.
`UNIX_TIMESTAMP()`	Useful for sharding/partitioning on epoch time.