SELECT Wmsstock.sku_id,wmsstock.stock_num,wmsstock.product_date,wmsstock.expire_date,wmsstock.cost_price, Goodssku.sku_name,goodssku.barcode, CONVERT ((Unix_timestamp (wmsstock.expire_date)-unix_timestamp ( wmsstock.product_date)/(60*60*24)), signed) as shelf life, wmsstock.stock_num*wmsstock.cost_price as Total price from Wms_stock as Wmsstock left joins Goods_sku as Goodssku on wmsstock.sku_id=goodssku.sku_id does not recommend operations in SQL statements, the data table is slightly more data, it will be very slow, Affect query speed the cast () and CONVERT () functions of MySQL can be used to get a value of one type and produce a value of another type. The specific syntax is as follows: 1CAST (value as type), 2CONVERT (value, type), or cast (xxx as type), CONVERT (XXX, type). There is a limit to the types that can be converted. This type can be one of the following values: binary, with binary prefix effect: binary character type, parameter: CHAR () Date: Date: Time datetime type: DATETIME floating point number: DECIMAL integer: Signed unsigned integers: UNSIGNED Here are a few examples: example one 1mysql> SELECT CONVERT (' signed '),----------------------+3| CONVERT (' signed ', |4+----------------------+5| |6+----------------------+71 Row in set example two 1mysql> SELECT CAST (' 125e342.83 ' as signed);------------------------- -----+3| CAST (' 125e342.83 ' as sigNed) |4+------------------------------+5| |6+------------------------------+71 Row in set example three 1mysql> SELECT CAST (' 3.35 ' as signed);---------------------- --+3| CAST (' 3.35 ' as signed) |4+------------------------+5| 3 |6+------------------------+71 row in set like the example above, convert varchar to int with cast (a as signed), where a is a varchar-type string. Example 4 in SQL Server, the following code demonstrates the result of a DateTime variable that contains only the pure date and the pure time when the date stores the hexadecimal store representation. 01DECLARE @dt datetime02 03--simple date 04SET @dt = ' 1900-1-2 ' 05SELECT CAST (@dt as binary (8)) 06--Result: 0x000000010000000007 08-- Pure time 09SET @dt = ' 00:00:01 ' 10SELECT CAST (@dt as binary (8)) 11--result: 0x000000000000012cmysql type conversions are like SQL Server. That is, the type parameter is a little different: CAST (xxx as type), CONVERT (XXX, type). II: There are two types of unix_timestamp functions in MySQL for call 1 no parameter call: Unix_timestamp () return value: From ' 1970-01-01 00:00:00 ' to the current time of the second difference example: SELECT unix_ TIMESTAMP () = 13391234152 parameter invocation: Unix_timestamp (date) where date can be a date string, a DateTime string, A timestamp or a local time numeric return value in YYMMDD or YYYMMDD format: From ' 1970-01-01-00:00:00 ' with the specified time of the second difference example: date string format: (date type) SELECT unix_ TIMESTAMP (' 2012-06-08 ') = 1339084800SELECT Unix_timestamp (Current_date ()) =>1339084800 Note: The return value of Current_date () is the same as the result of a DATE string format returned in the following formats: SELECT Unix_timestamp (' 20120608 '); SELECT unix_timestamp (' 2012-6-8 '); Select Unix_timestamp (' 2012-06-08 '); The result is: 1339084800 datetime string format: (Combination of date and time type) SELECT unix_timestamp (' 2012-06-08 10:48:55 ') + 1339123415SELECT Unix_timestamp (now ()) = 1339123415 Note: Now () The return value is a datetime string format
SQL statement Query is a time-related operation
