Timestamp comes with .0 at the end

Question

I'm trying to put in some time stamps in a AnnotatedTimeLine (Google Chart), and its req. to be in the Datetime format. When I reformat (to Timestamp format) the string that I receive from the class, it gives me this:

2013-06-28 10:08:35.0

What I want to do is to remove the .0 at the end. How can I do this? The code looks like this:

    public List<Survey> getAllSurvey(List<Status> statusList) {
    String sqlValues = "SELECT * FROM status WHERE idCategory = (SELECT idCategory FROM category WHERE name = '"
            + statusList.get(0).getCategoryName() + "');";
    List<Survey> survies = new ArrayList<Survey>();
    try {
        List<Map<String, Object>> rows = getJdbcTemplate().queryForList(
                sqlValues);
        for (Map row : rows) {
            Survey survey = new Survey();
            survey.setCategoryName(statusList.get(0).getCategoryName());
            survey.setValue((String) (row.get("value")));
            survey.setUnit((String) row.get("unit"));
            survey.setTimestamp(row.get("timeStamp") + "");
            survies.add(survey);
            System.out.println(survey.toString());
        }
    } catch (BadSqlGrammarException e) {
        e.printStackTrace();
    } catch (Exception e) {
        System.out.println(sqlValues);
        e.printStackTrace();
    }
    return survies;
}

Thanks in advance!

Use prepared statements to generate SQL queries, **NOT** string concatenation. Your current code is subject to SQL injection attacks. — fge, Jul 09 '13 at 09:52
That is not a reason! Using prepared statements should be a reflex. — fge, Jul 09 '13 at 09:54
Okay, I will keep that in mind then, I will try to fix it as soon as I can. This is prio though — benskiiii, Jul 09 '13 at 09:56

score 2 · Answer 1 · answered Jul 09 '13 at 09:58

2

You must convert the timestamp to the dateformat you want in your class. The default string representation of Timestamp is including the nanoseconds at the end. If you want to change that you can always do this:

    Timestamp t = Your timestamp;
    SimpleDateFormat df = new SimpleDateFormat("YYYY.MM.dd HH:mm:ss");
    String s = df.format(t);

answered Jul 09 '13 at 09:58

Devolus

21,661
13
66
113

For new readers I recommend you don’t use `SimpleDateFormat` (and also not `Timestamp`). Those classes are poorly designed and long outdated, the former in particular notoriously troublesome. Instead use `LocalDateTime` and `DateTimeFormatter`, both from [java.time, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/). – Ole V.V. Apr 29 '21 at 18:13
Beware of the case of format pattern letters. Please check once more what is the difference between lower case `y` and upper case `Y`. – Ole V.V. Apr 29 '21 at 18:14

score 1 · Answer 2 · answered Jul 09 '13 at 09:59

Your code contains the line row.get("timeStamp") + "", which will effectively call row.get("timeStamp").toString() + "". Assuming the row contains a java.sql.Timestamp object, it will return the timestamp formatted like yyyy-mm-dd hh:mm:ss.fffffffff.

If this is not desired, create a SimpleDateFormat object to represent the desired formatting, and then call SimpleDateFormat#format(row.get("timeStamp")) to have your value formatted properly.

The SimpleDateFormat class describes how to define your date and time patterns. Make sure to create the SimpleDateFormat object only once, since it's relatively expensive to create it. Many people define it as a private static final variable so it can be reused many times.

This one worked as well, but the above answer is faster I believe. If I could take both as answer I would do that :P — benskiiii, Jul 09 '13 at 10:10
For `java.time.format.DateTimeFormatter` (Java 8 and up), I used `yyyy-MM-dd HH:mm:ss.n` as the format string. — Evan Porter, Oct 02 '19 at 15:56

Ole V.V. · Answer 3 · 2021-04-29T18:51:05.987

TL;DR

You seem to be mixing up two things that you should try to distinguish: the value of your timestamp and the format of it.
You should not wish to use the Timestamp class. It is poorly designed and long outdated. Instead use OffsetDateTime, Instant or LocalDatetime from java.time.
Also in your Survey class do not keep the timestamp as a String. Still use one of the java.time classes mentioned. Only when you need to give string output, format into a string in the desired format. For this purpose, depending on what you need the string for you can probably do better than just avoiding the .0 at the end.

Your problem line

The center of your problem is here:

        survey.setTimestamp(row.get("timeStamp") + "");

You are concatenating a Timestamp from the query and a String. In order to do this Java converts the Timestamp to a String too by calling its toString method. Timestamp.toString() returns a string with at least one decimal on the seconds (and more, up to 9, if there are more non-zero decimals).

Solution: java.time

A timestamp means (a representation of) a point in time. The best class to use for a point in time is Instant from java.time. So in your Survey class define timestamp to be an Instant.

For a moment I assume that you are stuck with the List<Map<String,Object>> that Spring queryForList() returns, and that we cannot avoid the timestamp there being a java.sql.Timestamp. If so convert the Timestamp to an Instant as soon as you get hold of it:

        survey.setTimestamp(((Timestamp) row.get("timeStamp")).toInstant());

With a more modern query tool you can get either an OffsetDateTime, an Instant or a LocalDateTIme directly from your database and avoid the Timestamp class completely.

How to format your Instant (or other java.time object) into a string for presentation to the user or for interchange with another system depends so much on the context and requirements. Here’s just one simple example:

    DateTimeFormatter formatter = DateTimeFormatter.ofLocalizedDateTime(FormatStyle.MEDIUM);
    String formattedTimestamp = survey.getTimetamp()
            .atZone(ZoneId.systemDefault())
            .format(formatter);
    System.out.println(formattedTimestamp);

Example output:

Sep 13, 2020 2:26:40 PM

The output from the example code will depend not only on time zone, also on locale.

Links

Oracle tutorial: Date Time explaining how to use java.time.
Questions and answers about using the java.time classes with your SQL database:
- Getting the date from a ResultSet for use with java.time classes
- Best way to Change java.sql.TimeStamp to UTC format in Java
Documentation of the JdbcTemplate.queryForList method that you used

Evgeniy Dorofeev · Accepted Answer · 2013-07-09T10:14:44.787

0

try this one line solution

survey.setTimestamp(row.get("timeStamp").toString().replaceAll("\\.\\d+", ""));

if performance is critical then this solution is better

String ts = row.get("timeStamp").toString();
ts = ts.substring(0, str.indexOf('.'));

edited Jul 09 '13 at 10:14

answered Jul 09 '13 at 10:03

Evgeniy Dorofeev

133,369
30
199
275

Thanks a lot! the beneath answer works as well, but I think this one is faster and better since I dont have to create a new SimpleDateFormat! – benskiiii Jul 09 '13 at 10:10

Timestamp comes with .0 at the end

4 Answers4

TL;DR

Your problem line

Solution: java.time

Links

Linked