How to convert MySQL Timestamp to Javascript date?

Question

I want to display a Google Chart (Line Chart) on .jsp page of my Spring MVC application. The data is retrieved from a MySQL database, so I need to convert the YYYY-MM-DD HH:MM:SS format into Javascript's Date.

The database is created by Hibernate. The Reading entity has a time field of type java.sql.Timestamp, which is stored as DATETIME in the database.

The results is an Iterable<Reading> object passed to the .jsp via controller. It is passed correctly (I am displaying the data as a table, too).

I'm trying to use the solution proposed here, but it does not work.

Here's the code I'm trying to populate the chart with:

<c:forEach items="${results}" var="reading">
    var t = "${reading.time}".split(/[- :]/);
    var d = new Date(t[0], t[1]-1, t[2], t[3], t[4], t[5]);
    data.addRow([d,${reading.temperature}]);
</c:forEach>

The chart is not displaying.

Community · Accepted Answer · 2017-05-23 12:03:49Z

1

Facts:

JDBC's java.sql.Timestamp is a subclass of java.util.Date.
JSTL has a <fmt:formatDate> for converting java.util.Date to String.
JavaScript Date constructor can take a.o. a string in ISO8601 time format.

Put together:

<c:forEach items="${results}" var="reading">
    <fmt:formatDate var="time" value="${reading.time}" pattern="yyyy-MM-dd'T'HH:mm:ss" timeZone="UTC" />
    var d = new Date("${time}");
    // ...
</c:forEach>

Alternatively, just convert results to JSON using a decent JSON formatter in the controller and print it as if it's a JS variable like so var data = ${data};. See also a.o. How to access array of user defined objects within <script> in JSP?

Unrelated to the concrete problem: make sure your model is of java.util.Date type. You shouldn't have java.sql.* typed properties in your model. If you're using plain JDBC, just upcast ResultSet#getTimestamp() to java.util.Date directly. See also a.o. Handling MySQL datetimes and timestamps in Java.

edited May 23, 2017 at 12:03

CommunityBot

11 silver badge

answered Oct 20, 2015 at 17:37

BalusC

1.1m377 gold badges3.7k silver badges3.6k bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

RK1 Over a year ago

Thank you for the hints and the answer. However, the above code does not work. I think I will try the JSON solution.

RK1 Over a year ago

The chart is not displaying. I am using data.addRow([d, ${reading.temperature}]);. How can I debug this? I don't know Javascript at all, unfortunately.

BalusC Over a year ago

Just look in generated HTML output if JSTL has done its job the expected way. If it did, then the problem is beyond JSP/JSTL and you'd have had exactly the same problem when using a hardcoded JS variable.

RK1 Over a year ago

I am displaying the contents of results in a table and just noticed that time time got a .0 at the end. Maybe that's the case, or should I change the pattern in formatDate?

BalusC Over a year ago

Then JSTL has properly done its job as to printing/formatting the JS date variable and your new problem is beyond the scope of the question. Just press [Ask Question] button in right top if you can't figure out it.

|

Collectives™ on Stack Overflow

How to convert MySQL Timestamp to Javascript date?

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related