Welcome Guest.

3Answers

Mysql How to convert dates in the database, there are about 3 million rows

Asked by: Lewis 221 views IT September 18, 2018

6/6/2018 23:46

5/20/2018 6:38

8/17/2018 15:54

Addition: Direct conversion to date for query, data statistics

3 Answers

  1. +8Votes  

    1. In the mysql database, “2009-09-15 00:00:00” is converted to a function listed as a long integer:

     

    [java ] view plaincopy

    select unix_timstamp("2009-09-15 00:00:00")*1000,  

     

    here Note that the long integer in the mysql database is less than the number of milliseconds after the second in the long integer in java, so multiply by 1000, which is only a few milliseconds difference

     

    2. In the mysql database, “1252999488000” (long data in java) is converted to date:

     

    [java] view plaincopy

    select from_unixtime(1252999488);

    Patrick Smith- September 18, 2018 |

  2. +5Votes  

    Directly use > < such as select * from table where field > 100 — to supplement your database. The varchar type can also be used. Create table test(col1 varchar(2))declare @i intset @i = 1while @i< 100begininsert into test values(convert(varchar(2),@i))set @i = @i + 1endselect * from test where col1 > 50 –correct select * from test where convert(numeric(10),col1) > 50 –correct select * from test where col1 > ’50’ — error drop table test yourself test if you are not To convert a type to a numeric type, but this is not an index.

    Timothy- September 18, 2018 |

  3. +4Votes  

    You can convert varchar to another type
    such as decimal, float, etc. Example:
    select * from table where convert(float, field)>100

    Karen Mitchell- September 19, 2018 |