Java Timestamp and Mysql Timestamp

Asked

Viewed 1,038 times

3

Problem:

I’m storing a timestamp in MYSQL, but when I reset this timestamp I’m losing the thousandths.

Java timestamp with thousandths. Timestamp do Java com milésimos

MYSQL timestamp without thousandths. Timestamp do MYSQL sem milésimos.

Why does this occur?

How to solve this?

I need a comeback with these thousandths.

edited:

I believe that mysql 5.1.62, do not save the milestones, so part of my solution would be to capture the timestamp and remove the milliseconds, how efficiently to do this?

java mysql timestamp

  • I believe that the Mysql date time does not reach this level of accuracy. I know that in SQL Server there is the type datetime2 with that level of accuracy

  • Out of curiosity: what application are you developing that needs this level of accuracy (milliseconds)?

  • I may run out of a million seconds of timestamp, but as a developer it would be interesting to understand this behavior. Another issue is that in every way I tried to get the timestamp in Java it comes with milliseconds. System.currentTimeMillis(), Calendar.getInstance().getTimeInMillis(), new Date().getTime(); and new Timestamp().getTime(); I didn’t want to have to remove the milliseconds in the box.

  • Mysql version ? Fractional seconds is a new Feature in Mysql: http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-4.html

  • The version of my Mysql is 5.1.62, which is the best way to get a timestamp in Java where I can always reset milesseconds, so part of my problem ends.

2 answers

3


Answering your question:

what is the best way to get a timestamp in Java where you can always reset mileseconds?

For cases:

System.currentTimeMillis(), Calendar.getInstance(). getTimeInMillis(), new Date(). getTime(); and new Timestamp(). getTime();

You say you don’t want to remove in Rra, I’m not sure how you used it, but the simplest way I can imagine to do that is by dividing by 1000 and then multiplying by 1000. So you disregard the last three digits of your date, which correspond to milliseconds. So:

new Timestamp((System.currentTimeMillis()/1000)*1000);

Example:

import java.sql.Timestamp;
import java.util.Calendar;
import java.util.Date;

public class TesteTimestamp {
    public static void main(String[] args) {
        timestampComMili(System.currentTimeMillis());
        timestampSemMili(System.currentTimeMillis());

        timestampComMili(Calendar.getInstance().getTimeInMillis());
        timestampSemMili(Calendar.getInstance().getTimeInMillis());

        timestampComMili(new Date().getTime());
        timestampSemMili(new Date().getTime());

        //timestampComMili(new Timestamp().getTime());
        //timestampSemMili(new Timestamp().getTime());
        /* O código acima não é possível, pois não existe um 
         * construtor padrão a classe Timestamp. Veja em:
         * http://docs.oracle.com/javase/7/docs/api/java/sql/Timestamp.html
         */
    }
    public static void timestampComMili(long l) {
        System.out.println("Com mili: " + new Timestamp(l));
    }
    public static void timestampSemMili(long l) {
        System.out.println("Sem mili: " + new Timestamp((l/1000)*1000));
    }
}

Upshot:

With Mili: 2014-09-16 09:48:41.186
No Mili: 2014-09-16 09:48:41.0
With Mili: 2014-09-16 09:48:41.198
No Mili: 2014-09-16 09:48:41.0
With Mili: 2014-09-16 09:48:41.198
No Mili: 2014-09-16 09:48:41.0

  • It helped a lot, the idea of not having "Mili" is because it always made a difference in comparison of dates because of "Mili", but it’s already solved. Example populated an object, the object’s timestamp had "Mili", passed to the bank and then made a query to see if everything was successfully registered. When comparing the objects with the equals or in any other way, precisely the "Mili" of the first object were different from the return of the bank. So I ended up ignoring the "dates", but with this I can compare the "timestamp". The bank keeps the "Mili". Kind of silly, but it happened. haha

1

In the MySQL 5.6 you’d already get millisecond accuracy.

try

select sysdate(6) will return 2013-04-16 13:47:56.273434

and

select sysdate(3) will return 2013-04-16 13:47:56.273

But in case you don’t want to migrate to the MySQL 5.6, you can use the function DATE_FORMAT().

Follow an example:

DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p') resultado: Aug 19 2014 12:45 PM
DATE_FORMAT(NOW(),'%m-%d-%Y')          resultado: 08-19-2014
DATE_FORMAT(NOW(),'%d %b %y')          resultado: 19 Aug 14
DATE_FORMAT(NOW(),'%d %b %Y %T:%f')    resultado: 19 Aug 2014 12:47:10:214

  • I lose milestones when I register the timestamp, so even if I can use the DATE_FORMAT(create_in,'%d %b %Y %T:%f') wouldn’t work. In that case it might even work because using NOW() is a complete DATE.

Browser other questions tagged java mysql timestamp

You are not signed in. Login or sign up in order to post.