0

I am saving current time in my application to a MYSQL database as a timestamp, as a backup I will save the date to a text file. However, the format of the timestames is not the same, I want them to be the same because i need to be abel to compare them.

The database is using datetime type and the the timestamp is formated as "2014-07-23 09:50:14"

In the text file the timestamp is formated as "2014-07-23 09:51:25.877"

I am using a preparedstatement to save to the database.

preparedStatement.setTimestamp(2, new Timestamp(new Date().getTime()));

I am using a BufferedWriter to save to the text file. 

bufferedWriter.write(new Timestamp(new Date().getTime())

How can i get the format to be the same so I can compare the timestamps?

By the way, I do not need millisecond precision, I just want the format to be the same.

Ragnarsson
  • 1,736
  • 2
  • 11
  • 16
  • 1
    when you save the timestamp to database, it saves the timstamp in its TIMESTAMP datatype. The presentation that you see `2014-07-23 09:50:14` is because of the tool that you use for viewing the data. The actual value in the database has millisecond part saved as well. To test this, try to run `SELECT` query and build the Timestamp object in Java. Compare it to the Timestamp object that is built from the text file. If it still doesn't work, then post the full code. – Pat Jul 23 '14 at 08:13
  • @Pat You are somewhat correct. PMA seems to hide the milliseconds part, however, when i use a query to get the timestamp from the database. The miliseconds part is always 0, for example 2014-07-23 10:23:12.0. – Ragnarsson Jul 23 '14 at 08:27
  • you are out of luck here with MySQL. Check this post http://stackoverflow.com/questions/2572209/why-doesnt-mysql-support-millisecond-microsecond-precision . It seems that mySQL doesnt support milliseconds! I didn't know that. hmm, I guess, learned something today - Never use MySQL database :) – Pat Jul 23 '14 at 08:35
  • @Pat I do not need milliseconds precision, I just need the format to be the same in both the database and the text file. – Ragnarsson Jul 23 '14 at 08:37
  • and frustrating part is that this MySQL bug is open since many many years and nobody has bothered to fix it. You may like to explore other options mentioned in that SO post - e.g. PostgreSQL – Pat Jul 23 '14 at 08:38
  • 1
    if you dont need the milliseconds, then truncate the timestamp (using SimpleDateFormat) when you write it to the text file. e.g. `SimpleDateFormat noMilliSecondsFormatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); System.out.println(noMilliSecondsFormatter.format(timestamp));` – Pat Jul 23 '14 at 08:39

2 Answers2

1

It is different because you create two Timestamps a few seconds apart. You should create one timestamp and write to both places.

Untested:

Timestamp stamp = new Timestamp(new Date().getTime());

preparedStatement.setTimestamp(2, stamp);

bufferedWriter.write(stamp)

To store fractional seconds in MySQL see 11.3.7 Fractional Seconds in Time Values:

MySQL 5.7 has fractional seconds support for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision:

To define a column that includes a fractional seconds part, use the syntax type_name(fsp), where type_name is TIME, DATETIME, or TIMESTAMP, and fsp is the fractional seconds precision. For example:

CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)

DavidPostill
  • 7,734
  • 9
  • 41
  • 60
0

You can format the date before inserting it into the database using the following:

SimpleDateFormat dt1 = new SimpleDateFormat("yyyyy-mm-dd hh:mm:ss");
dt1.format(date);

That should fix your problem

mkazma
  • 572
  • 3
  • 11
  • 29