CODEWITHSUNDEEP
javascriptjspgoogle-visualizationjstl
RK1
RK1

Reputation: 439

How to convert MySQL Timestamp to Javascript date?

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.

Upvotes: 1

Views: 2896

Answers (1)

BalusC
BalusC

Reputation: 1109502

Facts:

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.

Upvotes: 1

Related Questions