How to use timestamp precision in mysql
This article will introduce to you the usage of 1901 timestamp precision in mysql. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to everyone.
Why does the DATETIME precision in mysql only support seconds?
Is the DATETIME type in mysql related to time zone?
When designing a table in mysql, how to choose the field that represents time?
Case Analysis DATETIME Accuracy Issue
Some time ago, the version of mysql-connector-java for the responsible application was upgraded from 5.1.16 to 5.1.30, and I discovered it when doing functional regression. , the runtime data of use cases similar to the above SQL will be missing, resulting in functional problems.
Considering that in the application I am responsible for, there is a function that requires the use of SQL similar to the following, that is, using the timestamp as the query condition to query all data after a certain timestamp.
After investigation, it was found that mysql-connector-java would discard the precision after seconds before 5.1.23 and then pass it to the MySQL server, which happened to be in the mysql version we used. The precision of DATETIME is seconds; after I upgraded mysql-connector-java to 5.1.30, when the timestamp is transmitted from the java application to the MySQL server through mysql-connector-java, the milliseconds will not be discarded. From the perspective of mysql-connector-java, it fixes a BUG, but for my application, it triggers a BUG.
If you face this problem, how will you fix it?
We thought of three options at the time:
Change the type of timestamp parameter in the Mapper interface of mybatis from java.util.Date to java.sql.Date;
Before passing it to the Mapper interface, round up the incoming timestamp by seconds. The code is as follows
Before querying, decrement the incoming timestamp by 1 Seconds;
It has been verified that in Solution 1, the java.sql.Date object transferred from java.util.Date will lose all the precision after the date, resulting in more unnecessary data being queried; Solution 3 is possible, but one or two more data may be found; Option 2 is also possible, which is equivalent to compensating for the characteristics of mysql-connector-java in terms of code. In the end I chose option 2.
Case Reproduction
Use homebrew to install MySQL. The version is 8.0.15. After installation, create a table to store user information. The SQL is as follows:
Use spirngboot mybatis as the development framework to define a user entity. The code is as follows:
Define the Mapper corresponding to the entity. The code is as follows:
Set the configuration related to connecting to mysql, the code is as follows:
Write the test code, first insert a piece of data, and then use The timestamp is used as a query condition. The code is as follows:
When running a single test, as we imagined, no data was queried. The results are as follows:
Then modify the code and use the above code to correct the query timestamp by seconds. The code is as follows:
Run the single again Test, as we imagined, the data can be queried this time.
However, here is a small episode. When I first designed the table, the SQL statement I used was as follows,
Smart as you are You must have discovered that the datetime here already supports smaller time precision after the decimal point. It supports up to 6 digits, that is, it can support up to the microscopic level. When was this feature introduced? I checked [MySQL's official documentation][9] and found that this feature was supported after mysql 5.6.4.
Summary of knowledge points
After the previous actual case analysis and case recurrence, readers must have a certain understanding of the DATETIME type in mysql. Next, let’s take a look at what experiences we can summarize from this case.
The version of mysql-connector-java and the version of mysql need to be used together. For example, for versions before 5.6.4, it is best not to use versions before 5.1.23 of mysql-connector-java, otherwise it may Will encounter the problems we encountered this time.
The field types used to represent time in MySQL are: DATE, DATETIME, and TIMESTAMP. They have similarities and each has its own characteristics. I have summarized a table as follows:
The DATETIME type is stored as an integer in the format of "YYYYMMDDHHMMSS" in MySQL. It has nothing to do with the time zone and uses 8 bytes of space;
TIMESTAMP type can be saved The time range is much smaller, and the displayed value depends on the time zone. The MySQL server, operating system, and client connection all have time zone settings.
Generally, it is recommended to use DATETIME as the timestamp field, and it is not recommended to use the bigint type to store time.
During development, you should try to avoid using timestamps as query conditions. If you must use them, you need to fully consider the accuracy of MySQL and the accuracy of query parameters.
Recommended learning: php video tutorial
The above is the detailed content of How to use timestamp precision in mysql. For more information, please follow other related articles on the PHP Chinese website!

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

Big data structure processing skills: Chunking: Break down the data set and process it in chunks to reduce memory consumption. Generator: Generate data items one by one without loading the entire data set, suitable for unlimited data sets. Streaming: Read files or query results line by line, suitable for large files or remote data. External storage: For very large data sets, store the data in a database or NoSQL.

MySQL query performance can be optimized by building indexes that reduce lookup time from linear complexity to logarithmic complexity. Use PreparedStatements to prevent SQL injection and improve query performance. Limit query results and reduce the amount of data processed by the server. Optimize join queries, including using appropriate join types, creating indexes, and considering using subqueries. Analyze queries to identify bottlenecks; use caching to reduce database load; optimize PHP code to minimize overhead.

Backing up and restoring a MySQL database in PHP can be achieved by following these steps: Back up the database: Use the mysqldump command to dump the database into a SQL file. Restore database: Use the mysql command to restore the database from SQL files.

How to insert data into MySQL table? Connect to the database: Use mysqli to establish a connection to the database. Prepare the SQL query: Write an INSERT statement to specify the columns and values to be inserted. Execute query: Use the query() method to execute the insertion query. If successful, a confirmation message will be output.

One of the major changes introduced in MySQL 8.4 (the latest LTS release as of 2024) is that the "MySQL Native Password" plugin is no longer enabled by default. Further, MySQL 9.0 removes this plugin completely. This change affects PHP and other app

To use MySQL stored procedures in PHP: Use PDO or the MySQLi extension to connect to a MySQL database. Prepare the statement to call the stored procedure. Execute the stored procedure. Process the result set (if the stored procedure returns results). Close the database connection.

Creating a MySQL table using PHP requires the following steps: Connect to the database. Create the database if it does not exist. Select a database. Create table. Execute the query. Close the connection.

In Go, you can use regular expressions to match timestamps: compile a regular expression string, such as the one used to match ISO8601 timestamps: ^\d{4}-\d{2}-\d{2}T \d{2}:\d{2}:\d{2}(\.\d+)?(Z|[+-][0-9]{2}:[0-9]{2})$ . Use the regexp.MatchString function to check if a string matches a regular expression.
