MySQL time field converted to millisecond format

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.