SQL statement does not give local time when executed via jsp
When I execute the SQL statement "SELECT datetime (" now "," localtime ")" from the standalone JDBC program against the SQLite JDBC driver, I get the correct local timestamp, whereas when I execute it through the JSP page, I get GMT / UTC. Can anyone explain why I am getting UTC in case of JSP code? I wanted the local time to be provided by the stand-along program. I have verified that both are using the same JAR driver for the "sqlite-jdbc-3.7.2.jar" JAR file.
Here is a standalone 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 conclusion:
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
His way out
source to share
Well, when we use the implicit object out
in our scripts, it doesn't generate pure output like our System.out, as it's a type javax.servlet.jsp.JspWriter
, so it generates HTML, not pure output.
So with this information, I am assuming that this object wraps your information in a way that you don't want it to.
You may have to display it differently, for example using the setTimeZone tag as Corey said, here's 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>
source to share
From what I read from this thread . SQLLite does not have a Date type , so you need to get a String from it usingrs.getString(1);
Try it.
<%
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.
source to share
Finally, I came up with a workaround based on your input from all of you. Basically, it gets information about the timezone, gets its offset, adds an offset to the timestamp value requested from SQLite. Of course, there is little danger in the workaround where the SQLite JDBC driver starts giving the correct value in the future!
<HTML>
<BODY>
<%@page import="java.sql.*"%>
<%@page import="java.util.*"%>
<%@page import="java.text.*"%>
<%
Connection conn = null;
PreparedStatement p = null;
ResultSet rs = null;
// Get current timezone and find offset from UTC.
TimeZone timeZone = TimeZone.getDefault();
out.println("<BR>Current timezone=" + timeZone);
long offset = timeZone.getOffset(System.currentTimeMillis());
out.println("<BR>Timezone offset=" + offset);
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();
// Convert queried timestamp to Java Date object.
DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
java.util.Date queryDate = sdf.parse(rs.getString(1));
out.println("<BR>timestamp from SQLite = " + sdf.format(queryDate));
// Get localtime by adding time zone offset.
long tm = queryDate.getTime() + offset; // New time with offset.
java.util.Date newDate = new java.util.Date(tm);
out.println("<BR>timestamp with timezone offset= " + sdf.format(newDate));
rs.close();
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>
Here is the o / p:
source to share