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

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 |