MySQL built-in function table (including string functions, mathematical functions, and datetime functions)

ASCII (str) returns the ASCII value of the first character of the string str (0 if str is an empty string) mysqlgt; selectASCII (

ASCII (str) returns the ASCII value of the first character of the string str (0 if str is an empty string) mysqlgt; select ASCII (

ASCII (str)
Returns the ASCII value of the first character of the string 'str' (0 is returned when 'str' is an empty string)
Mysql> select
ASCII ('2 ');
-> 50
Mysql> select ASCII (2 );
->
50
Mysql> select ASCII ('dete ');
-> 100
ORD (str)
If the string 'str' is at the beginning of a single byte, return the same value as that returned by the ASCII () function.
If it is a multi-byte character
Byte ASCII code) * 256 + (second byte ASCII code) [* 256 + third byte ASCII
Code...]
Mysql> select ORD ('2 ');
->
50

CONV (N, from_base, to_base)
Returns the number N in hexadecimal format and converts it to a string (if any parameter is NULL, NULL is returned. The hexadecimal range is 2-36, when to_base is a negative number, N is used as the signed number. Otherwise, the CONV is used as the unsigned number, and the CONV is used as the 64-point precision)
Mysql>
Select CONV ("a", 16, 2 );
-> '123'
Mysql> select
CONV ("6E", 18, 8 );
-> '123'
Mysql> select
CONV (-17,10,-18 );
-> '-H'
Mysql> select
CONV (10 + "10" + '10' + 0xa, 10, 10 );
-> '40'

BIN (N)
Convert N to a binary value and return it as a string (N is a BIGINT number, equivalent to CONV (N, 10, 2 ))
Mysql> select
BIN (12 );
-> '123'
OCT (N)
Convert N to an octal value and return it as a string (N is a BIGINT number, equivalent to CONV (N, 10, 8 ))
Mysql> select
OCT (12 );
-> '14'

HEX (N)
Convert N to hexadecimal notation and return it as a string (N is a BIGINT number, equivalent to CONV (N, 10, 16 ))
Mysql> select
HEX (255 );
-> 'Ff'

CHAR (N ,...)
Returns a string consisting of Characters in the ASCII code corresponding to the parameter N,... (the parameter is N,... is a numerical sequence, and the NULL value is skipped)
Mysql> select
CHAR (77,121, 83,81, '76 ');
-> 'Mysql'
Mysql> select
CHAR (77, 77.3, '77. 3 ');
-> 'Mmm'

CONCAT (str1, str2 ,...)
Concatenate a parameter into a long string and return it (if any parameter is NULL, return NULL)
Mysql> select CONCAT ('My, s ',
'Ql ');
-> 'Mysql'
Mysql> select CONCAT ('My, NULL,
'Ql ');
-> NULL
Mysql> select CONCAT (14.3 );
-> '14. 3'
LENGTH (str)
OCTET_LENGTH (str)
CHAR_LENGTH (str)
CHARACTER_LENGTH (str)
Returns the length of the str string (for multi-byte characters, CHAR_LENGTH is calculated only once)
Mysql>
Select LENGTH ('text ');
-> 4
Mysql> select
OCTET_LENGTH ('text ');
-> 4
LOCATE (substr, str)
POSITION (substr IN str)
Returns the position where the string substr appears for the first time. If the string does not contain substr, 0 is returned)
Mysql> select
LOCATE ('bar', 'foobar ');
-> 4
Mysql> select LOCATE ('xbar ',
'Foobar ');
-> 0

LOCATE (substr, str, pos)
Returns the position where the string substr first appears at the position pos of the string str. If the string does not contain the substr, 0 is returned)
Mysql>
Select LOCATE ('bar', 'foobarbar ', 5 );
-> 7
INSTR (str, substr)
Returns the position where the string substr appears for the first time. If the string does not contain substr, 0 is returned)
Mysql> select
INSTR ('foobar', 'bar ');
-> 4
Mysql> select INSTR ('xbar ',
'Foobar ');
-> 0
LPAD (str, len, padstr)
Fill the left end of str with the string padstr until the string length is len and return
Mysql> select
LPAD ('hi', 4 ,'?? ');
-> '?? Hi'

RPAD (str, len, padstr)
Fill the right end of str with the string padstr until the string length is len and return
Mysql> select
RPAD ('hi', 5 ,'? ');
-> 'Hi ??? '
LEFT (str, len)
Returns the left len of the str string.
Mysql> select LEFT ('foobarbar ',
5 );
-> 'Fooba'
RIGHT (str, len)
Returns the right len of the str string.
Mysql> select RIGHT ('foobarbar ',
4 );
-> 'Rbar'
SUBSTRING (str, pos, len)
SUBSTRING (str FROM pos FOR len)
MID (str, pos, len)
Returns the string 'str' position. pos starts with len characters (the ugly Syntax of 'from' is ANSI.
SQL92 Standard)
Mysql> select SUBSTRING ('quadratically ', 5, 6 );
->
'Ratica'
SUBSTRING (str, pos)
SUBSTRING (str FROM pos)
Returns a substring starting from the position pos of the str string.
Mysql> select
SUBSTRING ('quadratically ', 5 );
-> 'Ratically'
Mysql> select
SUBSTRING ('foobarbar' FROM 4 );
-> 'Barbar'
SUBSTRING_INDEX (str, delim, count)
Returns the substring after the delimiter delim that appears from the count of the str string (returns the left end when the count is positive, Otherwise returns the right terminal string)
Mysql>
Select SUBSTRING_INDEX ('www .mysql.com ','. ', 2 );
->
'Www. mysql'
Mysql> select SUBSTRING_INDEX ('www .mysql.com ','.',
-2 );
-> 'Mysql. com'
LTRIM (str)
Returns the str string with the left space deleted.
Mysql> select LTRIM ('
Barbarbar ');
-> 'Barbar'
RTRIM (str)
Returns the str string with the right space deleted.
Mysql> select RTRIM ('barbar
');
-> 'Barbar'
TRIM ([[BOTH | LEADING | TRAILING] [remstr] FROM] str)
Returns the str string whose prefix or suffix is deleted by remstr (the default location parameter is BOTH, and the default remstr value is space)
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'
SOUNDEX (str)
Returns a homophone string of str (sounds "roughly the same" string has the same homophone string, non-alphanumeric characters are ignored, and letters outside the A-Z are treated as vowels)
Mysql>
Select SOUNDEX ('hello ');
-> 'H400'
Mysql> select
SOUNDEX ('quadratically ');
-> 'Q36324'

SPACE (N)
Returns a string consisting of N spaces.
Mysql> select SPACE (6 );
->'
'

REPLACE (str, from_str, to_str)
Replace the child string from_str with the to_str string and return
Mysql> select
REPLACE ('www .mysql.com ', 'w', 'ww ');
-> 'Www'
REPEAT (str, count)
Returns a string consisting of str strings (if any parameter is NULL, return NULL, count ).
Select REPEAT ('mysql', 3 );
-> 'Mysqlmysqlmysqlmysql'

REVERSE (str)
Reverse the character order of string 'str' and return
Mysql> select REVERSE ('abc ');
-> 'CBA'
INSERT (str, pos, len, newstr)
Replace the string 'str' with a string 'newstr' and returns
Mysql> select
INSERT ('quadratic ', 3, 4, 'wh ');
-> 'Quwhattic'
ELT (N, str1, str2, str3 ,...)
Returns the nth string (N less than 1 or greater than the number of parameters returns NULL)
Mysql>
Select ELT (1, 'ej', 'heja ', 'hej', 'foo ');
-> 'Ej'
Mysql> select
ELT (4, 'ej', 'heja ', 'hej', 'foo ');
-> 'Foo'
FIELD (str, str1, str2, str3 ,...)
Returns the number of the nth string after which str is equal (if str is not found, 0 is returned)
Mysql> select FIELD ('ej', 'hej ',
'Ej', 'heja ', 'hej', 'foo ');
-> 2
Mysql> select FIELD ('fo', 'hej ',
'Ej', 'heja ', 'hej', 'foo ');
-> 0
FIND_IN_SET (str, strlist)
Returns the serial number of str in the strlist of the string set. (If any parameter is NULL, NULL is returned. If str cannot be found, 0 is returned. If parameter 1 contains ",", a working exception is returned)
Mysql>
SELECT FIND_IN_SET ('B', 'a, B, c, D ');
->
2

MAKE_SET (bits, str1, str2 ,...)
Convert the number of parameter 1 to binary. If the binary bit at a position is equal to 1, the string at the corresponding position is selected into the string set and returned (NULL String is not added to the result)
Mysql>
SELECT MAKE_SET (1, 'A', 'B', 'C ');
-> 'A'
Mysql> SELECT MAKE_SET (1 |
4, 'Hello', 'Nice ', 'World ');
-> 'Hello, world'
Mysql> SELECT
MAKE_SET (0, 'A', 'B', 'C ');
->''
EXPORT_SET (bits, on, off, [separator, [number_of_bits])
Sort string sets by bits. Only when the bit is equal to 1 will the string on be inserted. Otherwise, the off will be inserted (the default value of separator is ",", number_of_bits is used, and the length is not enough to add 0 and the length is too long)
Mysql> select EXPORT_SET (5, 'y', 'n', ',', 4)
-> Y, N, Y, N
LCASE (str)
LOWER (str)
Returns the str string in lower case.
Mysql> select
LCASE ('quadratically ');
-> 'Quadratically'

UCASE (str)
UPPER (str)
Returns an uppercase str string.
Mysql> select
UCASE ('quadratically ');
-> 'Quadratically'
LOAD_FILE (file_name)
Read the file and return the file content as a string (the file cannot be found, the path is incomplete, there is no permission, length greater than max_allowed_packet will return NULL)
Mysql>
UPDATE table_name SET blob_column = LOAD_FILE ("/tmp/picture") WHERE id = 1;

Mathematical functions
ABS (N)
Returns the absolute value of N.
Mysql> select ABS (2 );
-> 2
Mysql>
Select ABS (-32 );
-> 32

SIGN (N)
Symbol of the returned parameter (-1, 0, or 1)
Mysql> select SIGN (-32 );
->-1
Mysql> select SIGN (0 );
-> 0
Mysql>
Select SIGN (1, 234 );
-> 1
MOD (N, M)
Modulo operation returns the remainder of N divided by M (same as the % operator)
Mysql> select MOD (234, 10 );
-> 4
Mysql> select 234% 10;
-> 4
Mysql>
Select MOD (29,9 );
-> 2
FLOOR (N)
Returns the maximum integer value not greater than N.
Mysql & gt; select FLOOR (1.23 );
-> 1
Mysql> select FLOOR (-1.23 );
->-2
CEILING (N)
Returns the smallest integer value not less than N.
Mysql> select CEILING (1.23 );
->
2
Mysql> select CEILING (-1.23 );
->-1
ROUND (N, D)
Returns the rounding value of N and retains the D decimal places (the default value of D is 0)
Mysql> select ROUND (-1.23 );
->-1
Mysql> select ROUND (-1.58 );
->-2
Mysql> select ROUND (1.58 );
-> 2
Mysql> select
ROUND (1.298, 1 );
-> 1.3
Mysql> select ROUND (1.298, 0 );
-> 1
EXP (N)
Returns the nth power of e (the base of the natural logarithm)
Mysql> select EXP (2 );
->
7.389056
Mysql> select EXP (-2 );
-> 0.135335
LOG (N)
Returns the natural logarithm of N.
Mysql> select LOG (2 );
-> 0.693147
Mysql> select LOG (-2 );
-> NULL
LOG10 (N)
Returns the base-10 logarithm of N.
Mysql> select LOG10 (2 );
-> 0.301030
Mysql> select LOG10 (100 );
-> 2.000000
Mysql> select
LOG10 (-100 );
-> NULL
POW (X, Y)
POWER (X, Y)
Returns the Y Power of X.
Mysql> select POW (2, 2 );
-> 4.000000
Mysql> select POW (2,-2 );
-> 0.250000
SQRT (N)
Returns the square root of non-negative N.
Mysql> select SQRT (4 );
-> 2.000000
Mysql> select SQRT (20 );
-> 4.472136
PI ()
Returns the circumference rate.
Mysql> select PI ();
-> 3.141593
COS (N)
Returns the cosine of N.
Mysql> select COS (PI ());
->-1.000000
SIN (N)
Returns the sine of N.
Mysql> select SIN (PI ());
-> 0.000000
TAN (N)
Returns the tangent of N.
Mysql> select TAN (PI () + 1 );
-> 1.557408
ACOS (N)
Returns the inverse cosine of N (N is the cosine value in the range of-1 to 1; otherwise, NULL is returned)
Mysql> select ACOS (1 );
-> 0.000000
Mysql> select ACOS (1.0001 );
-> NULL
Mysql> select ACOS (0 );
-> 1.570796
ASIN (N)
Returns the inverse sine of N.
Mysql> select ASIN (0.2 );
-> 0.201358
Mysql> select ASIN ('foo ');
-> 0.000000
ATAN (N)
Returns the arc tangent of N.
Mysql> select ATAN (2 );
-> 1.107149
Mysql> select ATAN (-2 );
->-1.107149
ATAN2 (X, Y)
Returns the arc tangent of two variables X and Y (similar to the arc tangent of Y/X, the symbol determines the quadrant)
Mysql> select ATAN (-2, 2 );
->-0.785398
Mysql> select ATAN (PI (), 0 );
-> 1.570796
COT (N)
Returns the tangent of X.
Mysql> select COT (12 );
->-1.57267341
Mysql> select COT (0 );
-> NULL
RAND ()
RAND (N)
Returns a random floating point value ranging from 0 to 1.0 (the number N can be used as the initial value)
Mysql> select
RAND ();
-> 0.5925
Mysql> select RAND (20 );
-> 0.1811
Mysql> select RAND (20 );
-> 0.1811
Mysql> select
RAND ();
-> 0.2079
Mysql> select RAND ();
-> 0.7888
DEGREES (N)
Converts N from radians to degrees and returns
Mysql> select DEGREES (PI ());
->
180.000000
RADIANS (N)
Converts N from angle to radian and returns
Mysql> select RADIANS (90 );
->
1.570796
TRUNCATE (N, D)
Retain the D decimal places of the number N and return
Mysql> select TRUNCATE (1.223, 1 );
-> 1.2
Mysql> select TRUNCATE (1.999, 1 );
-> 1.9
Mysql> select TRUNCATE (1.999, 0 );
-> 1
LEAST (X, Y ,...)
Returns the minimum value (if the return value is used in the context of an integer (real number or size-sensitive string) or if all parameters are integers (real number or size-sensitive string), they are used as integers (real number or size-sensitive string) otherwise, strings with case-insensitive characters are compared)
Mysql>
Select LEAST (2, 0 );
-> 0
Mysql> select
LEAST (34.0, 3.0, 5.0, 767.0 );
-> 3.0
Mysql> select
LEAST ("B", "A", "C ");
-> ""
GREATEST (X, Y ,...)
Returns the maximum value (other values are the same as LEAST ())
Mysql> select GREATEST (2, 0 );
-> 2
Mysql> select GREATEST (34.0, 3.0, 5.0, 767.0 );
->
767.0
Mysql> select GREATEST ("B", "A", "C ");
-> "C"

Period time functions
DAYOFWEEK (date)
The return date is the day of the week (1 = Sunday, 2 = Monday ,...... 7 = Saturday, ODBC Standard)
Mysql>
Select DAYOFWEEK ('2017-02-03 ');
-> 3
WEEKDAY (date)
Returns the date of a week (0 = Monday, 1 = Tuesday ,...... 6 = Sunday ).
Mysql> select
WEEKDAY ('2017-10-04 22:23:00 ');
-> 5
Mysql> select
WEEKDAY ('2017-11-05 ');
-> 2
DAYOFMONTH (date)
Returns the day (within the range of 1 to 31) from January 1, January)
Mysql> select
DAYOFMONTH ('2017-02-03 ');
-> 3
DAYOFYEAR (date)
Returns the day (within the range of 1 to 366) of a year)
Mysql> select
DAYOFYEAR ('2017-02-03 ');
-> 34
MONTH (date)
Returns the month value in date.
Mysql> select MONTH ('2014-02-03 ');
-> 2
DAYNAME (date)
Returns the day of the week by the English name)
Mysql> select
DAYNAME ("1998-02-05 ");
-> 'Thursday'
MONTHNAME (date)
Returns the month of the date value (returned by English name)
Mysql> select
MONTHNAME ("1998-02-05 ");
-> 'February'
QUARTER (date)
Returns the quarter of the year for date.
Mysql> select
QUARTER ('98-04-01 ');
-> 2
WEEK (date, first)
Returns the week number Of The Year for date. (the default value of first is 0. The value of first is 1, indicating that Monday is the start of the week. 0 starts from Sunday)
Mysql> select
WEEK ('2017-02-20 ');
-> 7
Mysql> select WEEK ('2017-02-20 ', 0 );
-> 7
Mysql> select WEEK ('2017-02-20 ', 1 );
-> 8
YEAR (date)
Returns the year of date (range: 1000 to 9999)
Mysql> select
YEAR ('98-02-03 ');
-> 1998
HOUR (time)
Returns the hour of time (ranging from 0 to 23)
Mysql> select HOUR ('10: 05: 03 ');
-> 10
MINUTE (time)
Returns the number of minutes of time (ranging from 0 to 59)
Mysql> select MINUTE ('98-02-03
10:05:03 ');
-> 5
SECOND (time)
Returns the number of seconds (ranging from 0 to 59) of time)
Mysql> select
SECOND ('10: 05: 03 ');
-> 3
PERIOD_ADD (P, N)
Add N months to period P and return (P format: YYMM or YYYYMM)
Mysql> select
PERIOD_ADD (9801,2 );
-> 199803
PERIOD_DIFF (P1, P2)
Returns the number of months between period P1 and P2 (in the format of YYMM or YYYYMM for P1 and P2)
Mysql>
Select PERIOD_DIFF (9802,199703 );
-> 11
DATE_ADD (date, INTERVAL expr type)
DATE_SUB (date, INTERVAL expr type)
ADDDATE (date, INTERVAL expr type)
SUBDATE (date, INTERVAL expr
Type)
Addition and subtraction of date and time
(ADDDATE () and SUBDATE () are synonyms of DATE_ADD () and DATE_SUB (). You can also use operators + and-instead of functions.
Date is a DATETIME or DATE value. An expression string type used for the addition and subtraction of date by expr indicates how the expression expr should be interpreted.
[Type value
Meaning expected expr format]:
SECOND SECONDS
MINUTE MINUTES
HOUR time HOURS
DAY DAYS
MONTH-MONTH MONTHS
YEAR YEARS
MINUTE_SECOND minutes and seconds
"MINUTES: SECONDS"
HOUR_MINUTE hour and minute "HOURS: MINUTES"
DAY_HOUR and hour
"Days hours"
YEAR_MONTH and month "YEARS-MONTHS"
HOUR_SECOND hour, minute ,,
"HOURS: MINUTES: SECONDS"
DAY_MINUTE day, hour, minute "days hours: MINUTES"
DAY_SECOND day, hour, minute, second "DAYS
HOURS: MINUTES: SECONDS"
Expr allows any punctuation to be used as a separator. If all values are DATE values, the result is a DATE value; otherwise, the result is a DATETIME value)
If the type keyword is incomplete, MySQL will take the value from the right end. DAY_SECOND is equal to MINUTE_SECOND because the hour is missing)
If you increase MONTH, YEAR_MONTH, or YEAR, the number of days is greater than the maximum number of days in the result MONTH)
Mysql> SELECT "23:59:59" + INTERVAL 1 SECOND;
->
00:00:00
Mysql> select interval 1 DAY + "1997-12-31 ";
-> 1998-01-01
Mysql> SELECT "1998-01-01"-INTERVAL 1 SECOND;
-> 1997-12-31 23:59:59
Mysql> SELECT DATE_ADD ("1997-12-31
23:59:59 ", INTERVAL 1 SECOND );
-> 00:00:00
Mysql>
SELECT DATE_ADD ("23:59:59", INTERVAL 1 DAY );
-> 1998-01-01
23:59:59
Mysql> SELECT DATE_ADD ("23:59:59", INTERVAL"
MINUTE_SECOND );
-> 00:01:00
Mysql> SELECT
DATE_SUB ("00:00:00", INTERVAL "1" DAY_SECOND );
->
1997-12-30 22:58:59
Mysql> SELECT DATE_ADD ("00:00:00 ",
INTERVAL "-1 10" DAY_HOUR );
-> 1997-12-30 14:00:00
Mysql> SELECT
DATE_SUB ("1998-01-02", INTERVAL 31 DAY );
-> 1997-12-02
Mysql>
Select extract (year from "maid ");
-> 1999
Mysql> SELECT
EXTRACT (YEAR_MONTH FROM "01:02:03 ");
-> 199907
Mysql> select extract (DAY_MINUTE FROM "01:02:03 ");
-> 20102
TO_DAYS (date)
Returns the number of days since the date of the date (not calculated before January 1, 1582)
Mysql> select
TO_DAYS (950501 );
-> 728779
Mysql> select
TO_DAYS ('2017-10-07 ');
-> 729669
FROM_DAYS (N)
Returns the DATE value for the day from the DATE of the West dollar (not counted before January 1, 1582)
Mysql> select
FROM_DAYS (729669 );
-> '2017-10-07'
DATE_FORMAT (date, format)
Format the date value based on the format string
(The flag is available in the format string:
% M month name (January ...... December)
% W name of the Week (Sunday ...... Saturday)
% D indicates the date of the month with an English prefix (1st, 2nd, 3rd, and so on .)
% Y
Year, number, 4 digits
% Y year, number, 2 digits
% A abbreviated name of the Week (Sun ...... Sat)
% D number of days in the month,
Number (00 ...... 31)
% E number of days in the month (0 ...... 31)
% M month, number (01 ...... 12)
% C month,
Number (1 ...... 12)
% B abbreviated month name (Jan ...... Dec)
% J days in a year (001 ...... 366)
% H
Hour (00 ...... 23)
% K hour (0 ...... 23)
% H hour (01 ...... 12)
% I hour (01 ...... 12)
% L
Hour (1 ...... 12)
% I minute, number (00 ...... 59)
% R time, 12 hours (hh: mm: ss [AP] M)
% T
Time, 24 hours (hh: mm: ss)
% S seconds (00 ...... 59)
% S seconds (00 ...... 59)
% P AM or PM
% W
Days in a week (0 = Sunday ...... 6 = Saturday)
% U Week (0 ...... 52). Sunday is the first day of the week.
% U
Week (0 ...... 52) Monday is the first day of the week.
% Characters %)
Mysql> select
DATE_FORMAT ('2017-10-04 22:23:00 ',' % W % M % y ');
-> 'Saturday October
1997'
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % H: % I: % s ');
-> '22: 23: 00'
Mysql> select DATE_FORMAT ('2017-10-04
At 22:23:00 ',' % D % y % a % d % m % B % J ');
-> '4th 97 Sat 04 10 Oct 123'
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % H % k % I % r % T % S % W ');
-> '22 22 10 10:23:00 PM 22:23:00 6'
TIME_FORMAT (time, format)
Similar to DATE_FORMAT (), but TIME_FORMAT only processes hours, minutes, And seconds (other symbols generate a NULL value or 0)
CURDATE ()
CURRENT_DATE ()
Returns the current date value in 'yyyy-MM-DD 'or YYYYMMDD format (depending on the context in which the returned value is a string or number)
Mysql> select CURDATE ();
-> '2017-12-15'
Mysql> select
CURDATE () + 0;
-> 19971215
CURTIME ()
CURRENT_TIME ()
Returns the current time value in 'hh: MM: ss' or HHMMSS format (based on the context in which the returned value is a string or number)
Mysql> select CURTIME ();
-> '23: 50: 26'
Mysql> select
CURTIME () + 0;
-> 235026
NOW ()
SYSDATE ()
CURRENT_TIMESTAMP ()
Take 'yyyy-MM-DD
HH: MM: SS 'or YYYYMMDDHHMMSS format returns the current date and time (according to the context of the returned value, it is a string or number)
Mysql> select
NOW ();
-> '2017-12-15 23:50:26'
Mysql> select NOW () + 0;
-> 19971215235026
UNIX_TIMESTAMP ()
UNIX_TIMESTAMP (date)
Returns a Unix timestamp (from '2017-01-01
00:00:00 'gmt start seconds, date default value is the current time)
Mysql> select UNIX_TIMESTAMP ();
-> 882226357
Mysql> select UNIX_TIMESTAMP ('2017-10-04
22:23:00 ');
-> 875996580
FROM_UNIXTIME (unix_timestamp)
Take 'yyyy-MM-DD
HH: MM: SS 'or YYYYMMDDHHMMSS format returns the timestamp value (based on the context of the returned value is a string or number)
Mysql> select
FROM_UNIXTIME (875996580 );
-> '2017-10-04 22:23:00'
Mysql>
Select FROM_UNIXTIME (875996580) + 0;
-> 19971004222300
FROM_UNIXTIME (unix_timestamp, format)
Returns the timestamp value in format.
Mysql>
Select FROM_UNIXTIME (UNIX_TIMESTAMP (), '% Y % D % M % h: % I: % s % x ');
->
'2014 23rd December 03:43:30 x'
SEC_TO_TIME (seconds)
Returns the TIME value in the format of 'hh: MM: ss' or HHMMSS in seconds (depending on the context of the returned value, it is a string or number)
Mysql> select
SEC_TO_TIME (2378 );
-> '00: 39: 38'
Mysql> select
SEC_TO_TIME (2378) + 0;
-> 3938
TIME_TO_SEC (time)
How many seconds does the returned time Value have?
Mysql> select
TIME_TO_SEC ('22: 23: 00 ');
-> 80580
Mysql> select
TIME_TO_SEC ('00: 39: 38 ');
-> 2378