Java Servlet Mysql UNIX_TIMESTAMP() Function Example

In this tutorial you will learn how to use unix_timestamp() function of mysql in java servlet.

Java Servlet Mysql UNIX_TIMESTAMP() Function Example

In this tutorial you will learn how to use unix_timestamp() function of mysql in java servlet.

UNIX_TIMESTAMP function of mysql calculates on the given date and gives back a value in seconds that is called UNIXTIMESTAMP. These unixtimestamp is calculated from the '1970-01-01 00:00:00' UTC.

This function can be used with/without argument.

When this function is used without any argument it returns an unsigned integer after calculating on the current system datetime since 1970-01-01 00:00:00 UTC this may cause the returned value may varried from system to system.

Syntax :

unix_timestamp();

When the date argument is passed into it's parameter then it do calculation on the given argument since 1970-01-01 UTC and returns an unsigned value in seconds.

Syntax :

unix_timestamp('date');

NOTE : Argument of this function may be DATE, DATETIME, TIEMSTAMP, OR a NUMBER in the format yyyyddmm or yyddmm.

Now, since I have to use this function in java servlet program, therefore I have created a java servlet class named MysqlUnixTimeStampServlet which extends the HttpServlet class. In the body of class I overridden the method doGet() and created an objects of HttpServletRequest and HttpServletResponse into its parameter. Inside the doGet() method first I set the mime type that in which format the browser will show the output. In the next line I used the getWriter() method of ServletResponse interface with the object of HttpServletResponse. HttpServletResponse interface extends this method from the ServletResponse. In continue we have to make a connection between java code and database system. So, since I am using Mysql in the example given below therefore, I am loading the driver of mysql and make a connection by using the getConnection() method of DriverManager class. It returns a Connection. Further I am passing the two queries 1. "SELECT UNIX_TIMESTAMP() AS UNIXTIMESTAMP" (This query will return the output as 1314181036 ) and 2. "SELECT UNIX_TIMESTAMP('2011-08-24') AS UNIXTIMESTAMP" (This query will return the output as 1314124200) into the parameter of prepareStatement() method of Connection interface which gives an object of PreparedStatement interface. In the next line I used the executeQuery() method of PreparedStatement interface which returns a ResultSet object that holds the result of query. At last I have extracted the result from the ResultSet object.

Example :

MysqlUnixTimeStampServlet.java

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class MysqlUnixTimeStampServlet extends HttpServlet
{
 public void doGet(HttpServletRequest request, HttpServletResponse response)
  throws IOException, ServletException
{
 response.setContentType("text/html");
 PrintWriter out = response.getWriter();
 String className = "com.mysql.jdbc.Driver";
 String url = "jdbc:mysql://192.168.10.13/data";
 String user = "root";
 String password = "root";
 String sql = "SELECT UNIX_TIMESTAMP() AS UNIXTIMESTAMP";
 String sql1 = "SELECT UNIX_TIMESTAMP('2011-08-24') AS UNIXTIMESTAMP";
 
 Connection con;
 PreparedStatement ps, ps1;
 ResultSet rs, rs1;
 
 try
 {
  Class.forName(className);
  con = DriverManager.getConnection(url, user, password);
  ps = con.prepareStatement(sql);
  rs = ps.executeQuery();
  ps1 = con.prepareStatement(sql1);
  rs1 = ps1.executeQuery();
  
  out.println("<table>");
  out.println("<tr>");
  out.println("<td><b>UNIXTIMESTAMP</b></td>");
  out.println("</tr>");
  while(rs.next())
  {
   String unixTimeStamp = rs.getString(1);
   out.println("<tr>");
   out.println("<td>"+unixTimeStamp+"</td>");
   out.println("</tr>");
  }
  out.println("<tr>");
  out.println("<td><b>UNIXTIMESTAMP</b></td>");
  out.println("</tr>");
  while(rs1.next())
  {
   String unixTimeStamp1 = rs1.getString(1);
   out.println("<tr>");
   out.println("<td>"+unixTimeStamp1+"</td>");
   out.println("</tr>");
  }
  out.println("</table>");
 }
 catch(SQLException sx)
 {
  out.println(sx);
 }
 catch(ClassNotFoundException cx)
 {
  out.println(cx);
 }
}
}

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
<display-name>mysqlDate</display-name>

<servlet>
<servlet-name>MysqlUnixTimeStampServlet</servlet-name>
<servlet-class>MysqlUnixTimeStampServlet</servlet-class>
</servlet>

<servlet-mapping>
<servlet-name>MysqlUnixTimeStampServlet</servlet-name>
<url-pattern>/MysqlUnixTimeStampServlet</url-pattern>
</servlet-mapping>

</web-app>

Output :

When you will execute the above example you will get the output as :

Download Source Code