Prevent rounding of timestamp in mysql
I have a MySQL table with one field of type TIMESTAMP. When I insert a timestamp into the table, it gets rounded.
For example - if I do
insert into tablename (time) values ("2014-08-25 23:59:59.587");
and then i do
select * from tablename;
I get
2014-08-26 00:00:00
This is causing an error in my code where I am using the timestamp of the last entry to determine where the new day started.
How to ensure that the fractional part is stored as is? (or if it's stored correctly, then how to get it - (using Java))
I am using MySQL 5.6.16 and Java 8 (although the problem does not require java and can be checked from the command line)
When using Java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
public class DBTest {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection dbConn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/demod?useUnicode=true&characterEncoding=utf8", "dawon","1111");
Statement stmt = dbConn.createStatement();
String sql = "SELECT TEST FROM TIME;";
ResultSet rs = stmt.executeQuery(sql);
rs.next();
Timestamp ts = rs.getTimestamp("TEST");
System.out.println("time" + ts.toString() + " " + rs.getTimestamp("TEST"));
//both print out the rounded off value
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}
source to share
As of MySQL 5.6.4, support for fractional seconds is included in the data types Time
, DateTime
and TimeStamp
.
Your version should support it, you just need to detail your column like this:
To define a column containing fractional seconds fraction, use syntax type_name (fsp), where type_name is TIME, DATETIME, or TIMESTAMP and fsp is fractional seconds precision. For example:
CREATE TABLE t1 (t TIME (3), dt DATETIME (6)); The fsp value, if specified, must be in the range 0 to 6. A value of 0 means no fractional part. If omitted, the default precision is 0. (This differs from standard SQL by default 6, for compatibility with previous MySQL.)
You should read the full article as it has more information on this.
Update
It worked for me here using the following command:
/* The fsp value, if given, must be in the range 0 to 6 */
CREATE TABLE tablename (columnname TIMESTAMP(3));
No matter, the current version of the Workbench doesn't seem to support this feature in the "create table" interface. I needed to use a direct command to create a table.
source to share
[Post to save time for others]
I faced similar issue with mysql / aurora.
I tried to use the new SimpleDateFormat ("yyyy-MM-dd HH: mm: ss") in my queries. (createdOn), but found out that SimpleDateFormat just adds additional fields.
The default mapping of default fields for date fields to MySQL DateTime columns (using @Temporal (TemporalType.TIMESTAMP) annotation) rounded milliseconds.
To generate consistent queries, I had to implement rounding myself when using SimpleDateFormat.
Rounding code example:
Calendar cal = Calendar.getInstance();
cal.setTime(this.getCreatedOn());
if (cal.get(Calendar.MILLISECOND) >= 500 ) {
System.out.println("Round off milliseconds to seconds");
cal.set(Calendar.SECOND, cal.get(Calendar.SECOND) + 1);
}
Usage in queries:
createdOn = "'" + new SimpleDateFormat ("yyyy-MM-dd HH: mm: ss"). format (roundedCreatedOn) + "'"
source to share