Common MariaDB MySQL Functions
Common MariaDB MySQL Functions used in the WPDB Power Tool as well as ones used in general with applications that have a MariaDB/MySQL database backend.
CONCAT: Combines text in a string or in other words adds strings together.
Usage: SELECT CONCAT(‘The time is ‘, NOW());
DATE_FORMAT: Formats date and time in anyway you can specify. There are 36 date time options at present and a current list is fully displayed at mariadb.com here. Commonly used options:
| %a Day as: Sun Mon Tues | %b Month as: Oct Nov Dec | %c Month as: 9 10 11 | %D Day as : 1st 2nd 3rd 4th |
| %d Day as: 08 09 10 | %e Day as: 8 9 10 | %S or %s seconds as: 00-59 | %H Hour as: 00–23 |
| %h or %I Hour as: 01–12 %p for AM or PM | %i Minute as: 00–59 | %k Hour as: 0–23 | %l Hour as: 1–12 |
| %M Month as: January | %m Month as: 01–12 | %W Weekday as: Monday | %U Week # as: 00–53 |
| %w day of week as: 0–6 | %Y Year as: 2021 | %y Year as: 21 | %V Week # as: 01–53 |
Usage Examples: SELECT DATE_FORMAT(NOW(), '%W %M %Y'); # Output like: Wednesday March 2021
SELECT DATE_FORMAT(NOW(), '%m/%d/%Y'); # Output like: 03/25/2021
SELECT DATE_FORMAT(NOW(), '%a %M %D, %Y'); # Output like: Thu March 25th, 2021
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); # Output like: 2021-03-25 15:53:14
DATE_FORMAT(NOW(), '%W %b %D, %Y at %l:%i:%s %p'); #Output like:Wednesday Mar 31st, 2021 at 4:29:20 PM
You can replace NOW() with a DATETIME field for the same format results.
FROM_UNIXTIME: Displays UNIX/LINUX Epoch timestamp as readable format. Usage:
Usage: SELECT CONCAT('EPOCH TIME 2147483647 ', FROM_UNIXTIME(2147483647), ' High as MariaDB goes not one second more! ');
LOWER() or LCASE(): Converts all alpha charcters to lower case.
Usage: SELECT LOWER('NOT UPER CASE ANYMORE!');
NOW(): Gets the time to the second of NOW.
Usage: SELECT NOW();
UPPER() or UCASE(): Converts all lower case characters to upper case.
Usage: SELECT UPPER('no longer lower case!');
Complete list can be obtained at MariaDB.