MySQL time field converted to millisecond format

The following is a reprint of the MySQL millisecond, microsecond precision time processing two paragraphs, left for themselves and for everyone to reference ~ ~

MySQL gets milliseconds, microseconds, and milliseconds, microseconds processing

MySQL newer version (MySQL 6.0.5), also has not produced a microsecond function, now () can only be accurate to seconds. There is also no date-time type stored in MySQL with milliseconds or microseconds.

But, oddly enough, MySQL already has a function of extracting (extract) microseconds. For example:

Select Microsecond (' 12:00:00.123456 ');                          --123456select microsecond (' 1997-12-31 23:59:59.000010 ');               --10

Select Extract (microsecond from ' 12:00:00.123456 ');             --123456select Extract (microsecond from ' 1997-12-31 23:59:59.000010 ');  --10

Select Date_format (' 1997-12-31 23:59:59.000010 ', '%f ');         --000010

Still, you want to get a millisecond, microsecond, or something in the application-level program for MySQL. If you get time in your application that contains microseconds: 1997-12-31 23:59:59.000010, when MySQL is stored, you can design two fields: C1 datetime, C2 mediumint, respectively, and hold the date and microsecond. Why not use char to store it? The char type requires bytes, and datetime + Mediumint is only 11 (8+3) bytes.

---Transferred from netizens: Mlxia

Address: http://mlxia.iteye.com/blog/278982

Second, MySQL millisecond time comparison in general, MySQL use time comparison is generally converted into a time format to compare, such as: Date_format (' year-month-day time: minutes: Seconds ', '%y-%m-%d%h:%i:%s ') Select Date_format (' 2010-12-01 07:03:16 ', '%y-%m-%d%h:%i:%s ')/* Specific Example */If you want to convert to milliseconds, use Select Date_format (' 2010-12-01 07:03:16.233 ', '%y-%m-% D%t:%f ')--note, T cannot lowercase a query instance: SELECT * from Test where date_format (timess, '%y-%m-%d%t:%f ') between Date_format (' 2010-12-01 06:03:16.233 ', '%y-%m-%d%t:%f ') and Date_format (' 2010-12-01 08:03:16.733 ', '%y-%m-%d%T:%f ') here, The milliseconds before which to convert the string is used with "." Represents a decimal place. In addition, there is a direct comparison of the string: SELECT * from Test where date_format (timess, '%y-%m-%d%t:%f ') between ' 2010-12-01 06:03:16:233 ' and ' 2010-12-01 08:03:16:733 '

This is also possible. But note that the milliseconds before the sign becomes ":", must be this symbol, if you write "." The query result is incorrect.

In fact, these 2 can be written, however, I still recommend the best use of Date_forma () function conversion, because in the use of Navicat Lite tools such as debugging, if the direct comparison of strings, sometimes error. The specific reason is unclear.

Finally, I would like to say to you: in the format of the conversion, or to distinguish between the case, because many people do not pay attention to this, resulting in debugging failure. Just like the example above. You cannot use the lowercase t format or the uppercase T, otherwise the result will be incorrect.

---turn from netizens

Address: http://www.it165.NET/database/html/201110/760.html

Millisecond, microsecond noun explanation:

Milliseconds: Millisecond--1 per thousand sec microseconds: microsecond--one out of 10,000 seconds 1 seconds = 1000 milliseconds; 1 milliseconds = 1000 microseconds

MySQL time field converted to millisecond format