Mysql FROM_UNIXTIME() Function Example using Java Servlet

In this section you will learn about how to use mysql from_unixtime() in java servlet.

Mysql FROM_UNIXTIME() Function Example using Java Servlet

In this section you will learn about how to use mysql from_unixtime() in java servlet.

UNIXTIME describes the points into the date/datetime that are defined into the seconds. Unixtime counting was started at the Unix Epoch on 01-01-1970. These unixtime is based on UTC. UTC measure the time into SI seconds.

FROM_UNIXTIME() function of Mysql gives date/datetime by passing a version of unix_timestamp into its parameter. The format of date/datetime as a returned value may be the 'yyyy-mm-dd hh:mm:ss' or yyyymmddhhmmss.uuuuuu depend upon whether context of the function is in String or Numeric.

Syntax :

from_unixtime (unix_timestamp, [format ])

Here unix_timestamp = ((difference between a particular date and the unix epoch)*86400).

and the format = the format of date/datetime into which you want.

Now, since I have to use this function in java servlet program, therefore I have created a java servlet class named MysqlUnixtimeServlet 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 query "SELECT FROM_UNIXTIME(1312848000)" (This query will return the datetime in string format as 9th August 2011 05:30:00 ) and "SELECT FROM_UNIXTIME(1312848000)+0" (This query will return the datetime in numeric format as 20110809053000 ) with two different parameters 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 :

MysqlUnixtimeServlet.java

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Connection;
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 MysqlUnixtimeServlet extends HttpServlet
{
  public void doGet(HttpServletRequest req, HttpServletResponse res)
    throws IOException, ServletException
    {
	  res.setContentType("text/html");
	  PrintWriter pw = res.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 from_unixtime(1312848000,'%D %M %Y %h:%i:%s')";
	  String sql1 = "select from_unixtime(1312848000)+0";
	  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();
					
		pw.println("<br><b>String format from Unix Time</b>");
		pw.println("<table><tr>");
		pw.println("<td><b>from_unixtime(1312848000,'%D %M %Y %h:%i:%s')</b></td>");
		pw.println("</tr>");
		while(rs.next())
		{
			String unixTime = rs.getString(1);
			pw.println("<tr>");
			pw.println("<td>"+unixTime+"</td>");
			pw.println("</tr>");
		}		
		pw.println("</table>");
		
		pw.println("<br><b>Numeric format from Unix Time</b>");
		pw.println("<table><tr>");
		pw.println("<td><b>from_unixtime(1312848000)+0</b></td>");
		pw.println("</tr>");
		while(rs1.next())
		{
			String unixTime1 = rs1.getString(1);
			pw.println("<tr>");
			pw.println("<td>"+unixTime1+"</td>");
			pw.println("</tr>");
		}
		pw.println("</table>");	
	   }
	   catch(SQLException sx)
	   {
		pw.println(sx);
	   }
	   catch(ClassNotFoundException cx)
	   {
		 pw.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>MysqlUnixtimeServlet</servlet-name>

<servlet-class>MysqlUnixtimeServlet</servlet-class>

</servlet>



<servlet-mapping>

<servlet-name>MysqlUnixtimeServlet</servlet-name>

<url-pattern>/MysqlUnixtimeServlet</url-pattern>

</servlet-mapping>



</web-app>

Output :

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

Download Source Code