MySQL — date and string functions worth keeping nearby

Contents

  1. 1. Dates and time
  2. 2. Strings

Dates and time

  • FROM_UNIXTIME
1
2
Unix timestamp to datetime:
select FROM_UNIXTIME(1156219870);
  • UNIX_TIMESTAMP
1
2
Datetime string to Unix timestamp:
select UNIX_TIMESTAMP('2006-11-04 12:23:00');
  • date_format
1
2
Rows for today:
DATE_FORMAT(FROM_UNIXTIME(ctime),'%Y-%m-%d') = curdate()
  • Records in the last N days
1
2
select * from table_name
where datediff(curdate(), from_unixtime(timestamp_colname,'%Y-%m-%d')) >= N;

Strings

  • IF
1
IF(expr, val1, val2)  -- val1 when expr is true, else val2
  • IFNULL
1
select IFNULL(expr1, expr2)  -- expr2 when expr1 is NULL
  • ISNULL
1
select ISNULL(null)
  • MID
1
2
Substring:
select MID(expr1, start, length) -- start is 1-based in MySQL
  • LOCATE
1
2
Position of a substring:
select LOCATE('ab','efgabc'[, pos]) -- 1-based; 0 if missing; optional start offset pos
  • INSTR
1
2
Like LOCATE but argument order reversed; no pos argument:
select INSTR('efgabc', 'ab')
  • concat & concat_ws
1
2
concat(str1, str2, [str3]...)
concat_ws(separator, str1, str2, [str3]...)
  • group_concat
1
2
3
4
5
6
7
Roll column values into one string (default comma separator):
group_concat(name separator ';') -- custom separator
group_concat(name order by name desc) -- order inside the list

Default max length is often 1024:
show variables like "group_concat_max_len";
set GLOBAL group_concat_max_len=2048;
  • ELT
1
2
3
Pick the Nth string:
ELT(N, str1, str2, str3, ...)
eg: select elt(1,'M','F');
  • FIND_IN_SET
1
2
3
4
5
6
  FIND_IN_SET(str, strlist)

If strlist is a real column, use FIND_IN_SET; if it is a fixed list like ('a','b','c'), IN is fine.

Preserve a custom IN(...) order:
select * from article where id in(1,5,3) order by FIND_IN_SET(id,'1,5,3');
  • QUOTE
1
Escape a string for safe literal use
  • trim
1
2
3
4
5
6
7
8
mysql> SELECT TRIM('  bar   ');
-> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
-> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
-> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
-> 'barx'

More to add later.

Questions? Leave a comment below or email: newbvirgil@gmail.com