MySQL functions (MySQL functions) _ MySQL

MySQL function description (MySQL function Daquan) bitsCN.com for string position operations, the first position is marked as 1.
ASCII (str)
Returns the ASCII code value of the leftmost character of the str string. If str is a null string, 0 is returned. If 'str' is NULL, return NULL.
Mysql> select ASCII ('2 ');
-> 50
Mysql> select ASCII (2 );
-> 50
Mysql> select ASCII ('dx ');
-> 100
For more information, see the ORD () function.
ORD (str)
If the leftmost character of a string 'str' is a multi-byte character, use the format (first byte ASCII code) * 256 + (second byte ASCII code )) [* 256 + third byte ASCII code...] returns the ASCII code value of a character to return the multi-byte code. If the leftmost character is not a multi-byte character. Returns the same value as that returned by the ASCII () function.
Mysql> select ORD ('2 ');
-> 50
CONV (N, from_base, to_base)
Convert numbers between different digit bases. Returns the string number of number N, which is converted from from_base base to to_base base. if any parameter is NULL, NULL is returned. Parameter N is interpreted as an integer, but can be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If to_base is a negative number, N is considered as a signed number. otherwise, N is considered as an unsigned number. CONV works with 64-bit 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)
Returns a string of the binary value N. Here N is a long integer (BIGINT) number, which is equivalent to CONV (N, 10, 2 ). If N is NULL, return NULL.
Mysql> select BIN (12 );
-> '123'
OCT (N)
Returns the representation of a string with an octal value N. Here N is a long integer, which is equivalent to CONV (N, 10, 8 ). If N is NULL, return NULL.
Mysql> select OCT (12 );
-> '14'
HEX (N)
Returns the representation of a hexadecimal value N string. Here N is a long integer (BIGINT) number, which is equivalent to CONV (N, 10, 16 ). If N is NULL, return NULL.
Mysql & gt; select HEX (255 );
-> 'Ff'
CHAR (N ,...)
CHAR () interprets the parameter as an integer and returns a string consisting of ASCII code characters of these integers. The NULL value is skipped.
Mysql> select CHAR (77,121, 81, '76 ');
-> 'Mysql'
Mysql> select CHAR (77, 77.3, '77. 3 ');
-> 'Mmm'
CONCAT (str1, str2 ,...)
Returns a string from the parameter link. If any parameter is NULL, return NULL. There can be more than two parameters. A numeric parameter is converted to an equivalent string.
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.
Mysql> select LENGTH ('text ');
-> 4
Mysql> select OCTET_LENGTH ('text ');
-> 4
Note that for multi-byte characters, its CHAR_LENGTH () is calculated only once.
LOCATE (substr, str)
　
POSITION (substr IN str)
Returns the position of the substring substr in the first occurrence of the str. if the substring is not in the str, the return value is 0.
Mysql> select LOCATE ('bar', 'foobarbar ');
-> 4
Mysql> select LOCATE ('xbar', 'foobar ');
-> 0
This function is multi-byte reliable.
LOCATE (substr, str, pos)
Returns the position of the substring substr at the first occurrence of the substring, starting from the position pos. If substr is not in str, 0 is returned.
Mysql> select LOCATE ('bar', 'foobarbarbar ', 5 );
-> 7
This function is multi-byte reliable.
INSTR (str, substr)
Returns the position where the substring substr first appears in the str string. This is the same as LOCATE () in the form of two parameters, except that the parameters are reversed.
Mysql> select INSTR ('foobar', 'bar ');
-> 4
Mysql> select INSTR ('xbar', 'foobar ');
-> 0
This function is multi-byte reliable.
LPAD (str, len, padstr)
Returns the str string. the left side is filled with the string padstr until str is a string of len characters.
Mysql> select LPAD ('hi', 4 ,'?? ');
-> '?? Hi'
RPAD (str, len, padstr)
Returns the str string. fill it with the string padstr on the right until it is a string of len characters.
Mysql> select RPAD ('hi', 5 ,'? ');
-> 'Hi ??? '
LEFT (str, len)
Returns the leftmost len character of the str string.
Mysql> select LEFT ('foobarbar', 5 );
-> 'Fooba'
This function is multi-byte reliable.
RIGHT (str, len)
Returns the rightmost len character of the str string.
Mysql> select RIGHT ('foobarbar', 4 );
-> 'Rbar'
This function is multi-byte reliable.
SUBSTRING (str, pos, len)
　
SUBSTRING (str FROM pos FOR len)
　
MID (str, pos, len)
Returns a substring of len characters from the str string, starting from the position pos. The variant form of FROM is ANSI SQL92 syntax.
Mysql> select SUBSTRING ('quadratically ', 5, 6 );
-> 'Ratica'
This function is multi-byte reliable.
SUBSTRING (str, pos)
　
SUBSTRING (str FROM pos)
Returns a substring from the start position of the str string pos.
Mysql> select SUBSTRING ('quadratically ', 5 );
-> 'Ratically'
Mysql> select SUBSTRING ('foobarbar' FROM 4 );
-> 'Barbar'
This function is multi-byte reliable.
SUBSTRING_INDEX (str, delim, count)
Returns the substring after the delimiter delim that appears from the count of the str string. If count is a positive number, all characters from the last separator to the left (from the left) are returned. If count is a negative number, return all characters (from the right) from the last separator to the right ).
Mysql> select SUBSTRING_INDEX ('www .mysql.com ','. ', 2 );
-> 'Www. mysql'
Mysql> select SUBSTRING_INDEX ('www .mysql.com ','. ',-2 );
-> 'MySQL. com'
This function is reliable for multiple bytes.
LTRIM (str)
Returns the str string with leading space characters deleted.
Mysql> select LTRIM ('barbar ');
-> 'Barbar'
RTRIM (str)
Returns the str string that deletes the trailing space characters.
Mysql> select RTRIM ('barbar ');
-> 'Barbar'
This function is reliable for multiple bytes.
TRIM ([[BOTH | LEA
DING | TRAILING] [remstr] FROM] str)
Returns the str string. all the remstr prefixes or suffixes are deleted. If no modifier BOTH, LEADING, or TRAILING is given, BOTH is assumed. If remstr is not specified, spaces are deleted.
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'
This function is reliable for multiple bytes.
SOUNDEX (str)
Returns a homophone string of str. The two strings that sound "roughly the same" should have the same homophone string. The length of a "standard" homophone string is 4 characters, but the SOUNDEX () function returns a string of any length. You can use SUBSTRING () in the result to obtain a "standard" homophone string. All non-alphanumeric characters are ignored in a given string. All international letters except 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)
Returns the str string, which is replaced by the to_str character string.
Mysql> select REPLACE ('www .mysql.com ', 'W', 'WW ');
-> 'Wwwwww .mysql.com'
This function is reliable for multiple bytes.
REPEAT (str, count)
Returns a string consisting of str strings that repeat countTimes. If count <= 0, an empty string is returned. If 'str' or 'count' is NULL, return NULL.
Mysql> select REPEAT ('mysql', 3 );
-> 'Mysqlmysqlmysqlmysql'
REVERSE (str)
Returns the str string in the reversed character order.
Mysql> select REVERSE ('ABC ');
-> 'CBA'
This function is reliable for multiple bytes.
INSERT (str, pos, len, newstr)
Returns the str string, which is the substring starting from the position pos and is replaced by the newstr string with the len character length.
Mysql> select INSERT ('quadratic ', 3, 4, 'wh ');
-> 'Quwhattic'
This function is reliable for multiple bytes.
ELT (N, str1, str2, str3 ,...)
If N = 1, return str1. if N = 2, return str2, and so on. If N is less than 1 or greater than the number of parameters, NULL is returned. ELT () is a FIELD () inverse operation.
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 index of str in the str1, str2, str3,... list. If str is not found, 0 is returned. FIELD () is an inverse operation of ELT.
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)
If the string 'str' is in the strlist consisting of N substrings, a value ranging from 1 to N is returned. A string table is a string consisting of substrings separated by commas. If the first parameter is a constant string and the second parameter is a SET column, the FIND_IN_SET () function is optimized and bit operations are used! If str is not in strlist or if strlist is a null string, 0 is returned. If any parameter is NULL, NULL is returned. If the first parameter contains a ",", the function will not work properly.
Mysql> SELECT FIND_IN_SET ('B', 'a, B, c, D ');
-> 2
MAKE_SET (bits, str1, str2 ,...)
Returns a collection (a string consisting of substrings separated by ","), which is composed of the strings of the corresponding bits. Str1 corresponds to bit 0, str2 corresponds to bit 1, and so on. The NULL string in str1, str2,... 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])
Returns a string. here, for each bit set in "bits", you get a "on" string, and for each reset (reset) bit, you get a "off" string. Each string is separated by "separator" (default ","), and only the "number_of_bits" (default 64) bits are used.
Mysql> select EXPORT_SET (5, 'y', 'n', ',', 4)
-> Y, N, Y, N
LCASE (str)
　
LOWER (str)
Returns str, which converts all characters to lowercase based on the current character set ing (ISO-8859-1 Latin1 by default. This function is reliable for multiple bytes.
Mysql> select LCASE ('quadratically ');
-> 'Quadratically'
UCASE (str)
　
UPPER (str)
Returns str, which converts all characters to uppercase based on the current character set ing (ISO-8859-1 Latin1 by default. This function is reliable for multiple bytes.
Mysql> select UCASE ('hej ');
-> 'Hej'
This function is reliable for multiple bytes.
LOAD_FILE (file_name)
Read the file and return the file content as a string. The file must be on the server, you must specify the full path name of the file, and you must have the file permission. All content of the file must be readable and smaller than max_allowed_packet. If the file does not exist or cannot be read due to one of the above reasons, the function returns NULL.
Mysql> UPDATE table_name
SET blob_column = LOAD_FILE ("/tmp/picture ")
WHERE id = 1;
MySQL automatically converts numbers to strings when necessary, and the reverse is also true:
Mysql> SELECT 1 + "1 ";
-> 2
Mysql> select concat (2, 'test ');
-> '2 test'
If you want to explicitly convert a number to a string, pass it as a parameter to CONCAT ().
If a string function provides a binary string as a parameter, the result string is also a binary string. The number converted to a string is treated as a binary string. This only affects comparison

Mysql time function usage set

Here is an example of using the date function. The following query selects all records. The value of date_col is within the last 30 days:
Mysql> SELECT something FROM table
WHERE TO_DAYS (NOW ()-TO_DAYS (date_col) <= 30;
DAYOFWEEK (date)
Returns the index of the week of the date (1 = Sunday, 2 = Monday ,...... 7 = Saturday ). These index values correspond to the ODBC standard.
Mysql> select DAYOFWEEK ('2017-02-03 ');
-> 3
WEEKDAY (date)
Returns the week index of date (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 date of a month in the range of 1 to 31.
Mysql> select DAYOFMONTH ('2017-02-03 ');
-> 3
DAYOFYEAR (date)
Returns the number of days in a year from 1 to 366.
Mysql> select DAYOFYEAR ('2017-02-03 ');
-> 34
MONTH (date)
Returns the month of date, ranging from 1 to 12.
Mysql> select MONTH ('2014-02-03 ');
-> 2
DAYNAME (date)
Returns the week name of date.
Mysql> select DAYNAME ("1998-02-05 ");
-> 'Thursday'
MONTHNAME (date)
Returns the month name of date.
Mysql> select MONTHNAME ("1998-02-05 ");
-> 'February'
QUARTER (date)
Returns the quarter of a year from date, ranging from 1 to 4.
Mysql> select QUARTER ('98-04-01 ');
-> 2
WEEK (date)
　　　
WEEK (date, first)
If Sunday is the first day of a week, there is a single parameter that returns the number of weeks of the date, ranging from 0 to 52. Two parameter formats: WEEK () allows you to specify whether the WEEK starts on Sunday or Monday. If the second parameter is 0, the week starts from Sunday, and if the second parameter is 1, it starts from Monday.
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, ranging from 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 minute of time, ranging from 0 to 59.
Mysql> select MINUTE ('98-02-03 10:05:03 ');
-> 5
SECOND (time)
The number of seconds for the return time, ranging from 0 to 59.
Mysql> select SECOND ('10: 05: 03 ');
-> 3
PERIOD_ADD (P, N)
Add N months to phase P (in the format of YYMM or YYYYMM ). Return value in the format of YYYYMM. Note that the phase parameter P is not a date value.
Mysql> select PERIOD_ADD (9801,2 );
-> 199803
PERIOD_DIFF (P1, P2)
Returns the number of months between period P1 and P2. P1 and P2 should be in the format of YYMM or YYYYMM. Note that the period parameters P1 and P2 are not date values.
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)
These functions perform date operations. For MySQL 3.22, they are new. ADDDATE () and SUBDATE () are synonyms of DATE_ADD () and DATE_SUB.
In MySQL 3.23, you can use + and-instead of DATE_ADD () and DATE_SUB (). (See the example) date is a specified start date.
DATETIME or DATE value. expr is an expression that specifies the value to be added to the start DATE or the value to be subtracted from the start DATE. expr is a string.
A "-" indicates the negative interval. Type is a keyword that specifies how the expression should be interpreted. EXTRACT (type FROM date) function FROM date
Returns the "type" interval. The following table shows how the type and expr parameters are associated: the type value indicates the 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"
MySQL allows any punctuation separator in the expr format. It indicates that the recommended delimiter is displayed. If the date parameter is a DATE value and your calculation only contains the YEAR, MONTH, and DAY sections (that is, there is no time section), the result is a DATE value. Otherwise, the result is a DATETIME Value.
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 ("23:59:59 ",
INTERVAL 1 SECOND );
-> 00:00:00
Mysql> SELECT DATE_ADD ("23:59:59 ",
INTERVAL 1 DAY );
-> 23:59:59
Mysql> SELECT DATE_ADD ("23:59:59 ",
INTERVAL "1:1" 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
If you specify an interval value that is too short (excluding the expected interval of the type keyword), MySQL assumes that you have saved the leftmost portion of the interval value. For example, if you specify a type of DAY_SECOND, the value of expr is expected to be one day, hour, minute, and second. If you specify a value like "", MySQL assumes that the days and hours are lost and the value represents minutes and seconds. In other words, "" DAY_SECOND is interpreted as it is equivalent to "" MINUTE_SECOND, this interpretation of MySQL's TIME value indicates that the elapsed TIME is not as a one-day TIME. If you use an incorrect date, the result is NULL. If you increase MONTH, YEAR_MONTH, or YEAR and the result date is greater than the maximum number of days of the new MONTH, the day is adjusted by the maximum number of days of the new moon.
Mysql> select DATE_ADD ('2017-01-30 ', Interval 1 month );
-> 1998-02-28
Note: In the previous example, the INTERVAL and type keywords are not case sensitive.
　　
TO_DAYS (date)
Returns the number of days (from 0 years ).
Mysql> select TO_DAYS (950501 );
-> 728779
Mysql> select TO_DAYS ('2017-10-07 ');
-> 729669
TO_DAYS ()
It is not intended to be used to use the value before the occurrence of the glipro calendars (1582.
FROM_DAYS (N)
Returns a DATE value for the number of days N.
Mysql> select FROM_DAYS (729669 );
-> '2017-10-07'
DATE_FORMAT (date, format)
Format the date value based on the format string. The following modifier can be used 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 (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.
% A text "% ".
All other characters are not interpreted and copied to the result.
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 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'
In MySQL3.23, % is required before the format modifier. In earlier MySQL versions, % is optional.
TIME_FORMAT (time, format)
This is used like the preceding DATE_FORMAT () function, but the format string can only contain format modifiers for processing hours, minutes, and seconds. Other modifiers generate a NULL value or 0.
　　
CURDATE ()
　　　
CURRENT_DATE
Returns today's date value in 'yyyy-MM-DD 'or YYYYMMDD format, depending on whether the function is used in a string or numeric context.
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, depending on whether the function is used in a string or in the context of a number.
Mysql> select CURTIME ();
-> '23: 50: 26'
Mysql> select CURTIME () + 0;
-> 235026
NOW ()
　　　
SYSDATE ()
　　　
CURRENT_TIMESTAMP
Returns the current date and time in 'yyyy-MM-DD HH: MM: SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or in a numeric context.
Mysql> select NOW ();
-> '2017-12-15 23:50:26'
Mysql> select NOW () + 0;
-> 19971215235026
UNIX_TIMESTAMP ()
　　　
UNIX_TIMESTAMP (date)
If no parameter is called, a Unix timestamp (in seconds starting from '2017-01-01 00:00:00 'GMT) is returned ). If UNIX_TIMESTAMP () is called with a date parameter, it returns the second value starting from '2017-01-01 00:00:00 'GMT. Date can be a number of a DATE string, a DATETIME string, a TIMESTAMP, or a local time in YYMMDD or YYYYMMDD format.
Mysql> select UNIX_TIMESTAMP ();
-> 882226357
Mysql> select UNIX_TIMESTAMP ('2017-10-04 22:23:00 ');
-> 875996580
When UNIX_TIMESTAMP is used in a TIMESTAMP column, the function accepts the value directly without the implicit "string-to-unix-timestamp" transformation.
FROM_UNIXTIME (unix_timestamp)
Returns the value represented by the unix_timestamp parameter in 'yyyy-MM-DD HH: MM: SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or number context.
Mysql> select FROM_UNIXTIME (875996580 );
-> '2017-10-04 22:23:00'
Mysql> select FROM_UNIXTIME (875996580) + 0;
-> 19971004222300
FROM_UNIXTIME (unix_timestamp, format)
Returns a string representing the Unix time Mark, formatted according to the format string. Format can contain the same modifier as the entries listed by the DATE_FORMAT () function.
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 seconds parameter, which is converted to hour, minute, and second. The value is formatted in 'hh: MM: SS' or HHMMSS, depending on whether the function is used in a string or a number context.
Mysql> select SEC_TO_TIME (2378 );
-> '00: 39: 38'
Mysql> select SEC_TO_TIME (2378) + 0;
-> 3938
TIME_TO_SEC (time)
Returns the time parameter, which is converted to seconds.
Mysql> select TIME_TO_SEC ('22: 23: 00 ');
-> 80580
Mysql> select TIME_TO_SEC ('00: 39: 38 ');
-> 2378
Mysql system functions:
Select curtime ();
Select curdate ():
Select sysdate ():
Select now (); bitsCN.com