Mysql field type conversion and time type Processing

I) converting the field type to the CAST () and CONVERT () functions of MySQL can be used to obtain one type and generate another type. The syntax is as follows: CAST (valueastype); CONVERT (value, type); CAST (xxxAS type), CONVERT (xxx, type ). There are limits on the types that can be converted. This type can be the following

I) converting the field type to the CAST () and CONVERT () functions of MySQL can be used to obtain one type and generate another type. The syntax is as follows: CAST (value AS type); CONVERT (value, type); CAST (xxx as type), CONVERT (xxx, type ). There are limits on the types that can be converted. This type can be the following

I) field type conversion

The CAST () and CONVERT () functions of MySQL can be used to obtain values of one type and generate values of another type. The specific syntax of the two is as follows:

CAST(value as type);CONVERT(value, type);

Is CAST (xxx AS type), CONVERT (xxx, type ).

There are limits on the types that can be converted. This type can be one of the following values:

• Binary, with a BINARY Prefix: binary
• Character type, with parameters: CHAR ()
• DATE: DATE
• TIME: TIME
• DATETIME type
• Floating Point: DECIMAL
  
• Integer: SIGNED
• UNSIGNED integer
  

EEG：mysql> SELECT CAST('3.35' AS signed);+------------------------+| CAST('3.35' AS signed) |+------------------------+|                      3 |+------------------------+1 row in set
mysql> SELECT CONVERT('23.001',SIGNED);+----------------------+| CONVERT('23',SIGNED) |+----------------------+|                   23 |+----------------------+1 row in set

Convert to float:

SELECT 23 + 0.000

Output: 23.000

The following are examples of SQL statements used in the project:

/*SQLServerDBQuery methodconvert to float
Return this. getSession (). createQuery ("FROM" + this. clazz. getName () + "this WHERE this. areaCode IN (: areaCode) and" +
"(Convert (float, this. cpuUsedPer)> '"+ Float. valueOf (cpuValue) + "'or convert (float, this. memoryUsedPer)> '"+ Float. valueOf (memValue) + "'or

Convert (float, this. diskLaveSize) <'"+ Float. valueOf (diskValue) +"' or this. appServerState = 1 or this. dbServerState = 1 or this. mgrServerState = 1 or

Convert (float, DateDiff (mi, this. lastActiveTime, getDate ()> '"+ Float. parseFloat (timeOutValue) + "')"). setParameterList ("areaCode", areaCode ). list ();
*/

// @ Author: chenjun/20140515MysqlDBQuery method with convert DECIMAL
Return this. getSession (). createQuery ("FROM" + this. clazz. getName () + "this WHERE this. areaCode IN (: areaCode) and" +
"(This. cpuUsedPer + 0.00)> '"+ Float. valueOf (cpuValue) + "'or (this. memoryUsedPer + 0.00)> '"+ Float. valueOf (memValue) + "'or (this. diskLaveSize + 0.00) <

'"+ Float. valueOf (diskValue) + "'or this. appServerState = 1 or this. mgrServerState = 1 or convert (TIMESTAMPDIFF (MINUTE, this. lastActiveTime, NOW (), DECIMAL)>

'"+ Float. parseFloat (timeOutValue) +"') "). setParameterList (" areaCode ", areaCode). list ();

II) Processing of time types

SQLServer

DateDiff (mi, this. lastActiveTime, getDate () returns the time difference between lastActiveTime Date and the current system time, in minutes

Mysql

DATEDIFF (expr, expr2) DATEDIFF () returns the number of days between the start time expr and the end time expr2
Expr> expr2, return value> 0; expr = expr2, return value = 0; expr <expr2, return value <0;
TIMEDIFF (expr, expr2) TIMEDIFF () returns the time between the start time expr and the end time expr2
Expr> expr2, return value> 0; expr = expr2, return value = 0; expr <expr2, return value <0;
TIMESTAMPDIFF (MINUTE, expr, expr2) TIMEDIFF () returns the number of minutes between the start time expr and the end time expr2
Expr> expr2, return value <0; expr = expr2, return value = 0; expr> expr2, return value <0; (the parameter is opposite to DATEDIFF and TIMEDIFF)

EGG: TIMESTAMPDIFF (MINUTE, expr, NOW ())

Test Value: expr = '2017-05-15 17:00:10 '; NOW () = '2017-05-15 17:40:10', output: 40
mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');        -> 3mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');        -> -1mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');        -> 128885

MySQL-DATEDIFF (expr1, expr2)
Http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_datediff

MySQL syntax create table, http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#create-table

Show table status, http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#show-table-status

Date and Time type overview document

Http://dev.mysql.com/doc/refman/5.1/zh/column-types.html#date-and-time-type-overview

JOIN syntax document,

Http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#join