Menu
When I execute SQL statement "SELECT datetime('now', 'localtime')" from a stand-alone JDBC program against SQLite JDBC driver, I get correct local timestamp whereas when I execute it through a JSP page, I get GMT/UTC time. Could somebody explain why I am getting UTC time in case of JSP code? I wanted local time as given by the stand-along program. I have made sure that both use the same JDBC driver jar file "sqlite-jdbc-3.7.2.jar".
Here is the stand-alone SQLite JDBC program:
import java.sql.*;
public class GetTimestamp
{
public static void main(String[] args)
{
Connection conn = null;
PreparedStatement p = null;
ResultSet rs = null;
try {
Class.forName("org.sqlite.JDBC");
conn = DriverManager.getConnection("jdbc:sqlite:C:/sqlite/db1", null, null);
p = conn.prepareStatement("SELECT datetime('now', 'localtime')");
rs = p.executeQuery();
System.out.println("Current timestamp = " + rs.getString(1));
conn.close();
} catch (SQLException se) {
System.out.println("Error: " + se.toString());
} catch (ClassNotFoundException cnfe) {
System.out.println("Error: " + cnfe.toString());
}
}
}
Its output:
Here is the equivalent JSP:
<HTML>
<BODY>
<%@page import="java.sql.*"%>
<%
Connection conn = null;
PreparedStatement p = null;
ResultSet rs = null;
try {
Class.forName("org.sqlite.JDBC");
conn = DriverManager.getConnection("jdbc:sqlite:C:/sqlite/db1", null, null);
p = conn.prepareStatement("SELECT datetime('now', 'localtime')");
rs = p.executeQuery();
//System.out.println("Current timestamp = " + rs.getString(1));
out.println("Current timestamp = " + rs.getString(1));
conn.close();
} catch (SQLException se) {
//System.out.println("Error: " + se.toString());
out.println("Error: " + se.toString());
} catch (ClassNotFoundException cnfe) {
//System.out.println("Error: " + cnfe.toString());
out.println("Error: " + cnfe.toString());
}
%>
</BODY>
</HTML
Its output is
800
Well, when we are using the implicit object out in our scriplets, it doesn't generate pure output like our System.out, since it is a type of javax.servlet.jsp.JspWriter, so, it generates HTML and not pure output.
So, with this information, i assume this object is wrapping your information in a way you don't want.
You will probably have to output it in another way, like using tag setTimeZone as Corey said, here is an example:
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt" %>
<html>
<head>
<title>JSTL fmt:setTimeZone Tag</title>
</head>
<body>
<c:set var="now" value="<%=new java.util.Date()%>" />
<p>Date in Current Zone: <fmt:formatDate value="${now}"
type="both" timeStyle="long" dateStyle="long" /></p>
<p>Change Time Zone to GMT-8</p>
<fmt:setTimeZone value="GMT-8" />
<p>Date in Changed Zone: <fmt:formatDate value="${now}"
type="both" timeStyle="long" dateStyle="long" /></p>
</body>
</html>
From what I've read from this thread. SQLLite doesn't have Date type, so you need to get the String of it using rs.getString(1);
Try this.
<%
Connection conn = null;
PreparedStatement p = null;
ResultSet rs = null;
try {
Class.forName("org.sqlite.JDBC");
conn = DriverManager.getConnection("jdbc:sqlite:C:/sqlite/db1", null, null);
p = conn.prepareStatement("SELECT datetime('now', 'localtime')");
rs = p.executeQuery();
//System.out.println("Current timestamp = " + rs.getString(1));
DateFormat converter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date queryDate = converter.parse(rs.getString(1));
out.println("Current timestamp = " + converter.format(queryDate));
conn.close();
} catch (SQLException se) {
//System.out.println("Error: " + se.toString());
out.println("Error: " + se.toString());
} catch (ClassNotFoundException cnfe) {
//System.out.println("Error: " + cnfe.toString());
out.println("Error: " + cnfe.toString());
}catch(ParseException e){
out.println("ParseError: " + e.toString());
}
%>
Not sure if this helps.
39
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
