SELECT * FROM ( select gpstime from xydata a inner join fm4features f on a.ID=f.ID where a.objectId= '18' AND a.clientId = '1' AND a.gpstime >= '2010-05-11 00:00:00' AND a.gpstime <= '2010-05-15 23:59:59' AND f.DataId='1' AND f.value = '1') as a
UNION ALL SELECT * FROM ( select gpstime from xydata where objectId= '18' AND clientId = '1' AND gpstime >= '2010-05-11 00:00:00' AND gpstime <= '2010-05-15 23:59:59' AND speed > '3') as b
output
Row
2010-05-11 12:20:40
2010-05-11 12:21:41
2010-05-11 12:22:41
2010-05-12 09:05:26
2010-05-12 12:00:37
2010-05-12 12:01:37
2010-05-12 12:02:38
2010-05-12 12:12:06
2010-05-13 09:22:57
2010-05-13 11:45:22
2010-05-13 11:46:23
2010-05-13 11:46:34
2010-05-13 11:47:35
2010-05-13 11:48:35
2010-05-13 12:28:46
2010-05-13 12:29:47
2010-05-11 12:57:11
2010-05-11 12:58:11
2010-05-11 12:59:12
2010-05-11 13:02:12
2010-05-11 13:03:12
2010-05-11 13:07:08
2010-05-12 12:01:37
2010-05-12 12:02:38
2010-05-12 12:13:07
2010-05-12 12:14:07
2010-05-12 12:15:07
2010-05-13 11:47:35
2010-05-13 11:48:35
2010-05-13 12:29:47
2010-05-13 12:30:47
2010-05-13 12:31:47
2010-05-13 12:32:47
i need this type of output
ROW
2010-05-11 12:20:40
2010-05-11 12:21:41
2010-05-11 12:22:41
2010-05-12 09:05:26
2010-05-12 12:00:37
2010-05-12 12:01:37
2010-05-12 12:02:38
2010-05-12 12:12:06
2010-05-13 09:22:57
2010-05-13 11:45:22
2010-05-13 11:46:23
2010-05-13 11:46:34
2010-05-13 11:47:35
2010-05-13 11:48:35
2010-05-13 12:28:46
2010-05-13 12:29:47
ROW1
2010-05-11 12:57:11
2010-05-11 12:58:11
2010-05-11 12:59:12
2010-05-11 13:02:12
2010-05-11 13:03:12
2010-05-11 13:07:08
2010-05-12 12:01:37
2010-05-12 12:02:38
2010-05-12 12:13:07
2010-05-12 12:14:07
2010-05-12 12:15:07
2010-05-13 11:47:35
2010-05-13 11:48:35
2010-05-13 12:29:47
2010-05-13 12:30:47
2010-05-13 12:31:47
2010-05-13 12:32:47
what do i changes to get the above output
thanks in advance
You shouldn’t try to include the header in the result of the sql query. Do that in the presentation layer.
please see this java code
package tcplistener;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashSet;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.NamingException;
import java.util.Map;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
public class Speedfinal
{
private static PreparedStatement preparedStatement;
private static ResultSet rs;
public static void main(String[] args) throws NamingException, SQLException
{
int objectId;
int clientId;
DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
// String gpstime = null;
PreparedStatement preparedStatement = null;
Connection conn=null;
long Total=0;
int b;
int b2;
int nano=0;
int nano1=0;
String disHour="";
String disMinu="";
String disSec="";
ArrayList arr = new ArrayList();
ArrayList arr1 = new ArrayList();
try
{
Class.forName("com.mysql.jdbc.Driver");
Connection conn1=DriverManager.getConnection("jdbc:mysql://108.108.8.4:3306/navl?","root", "ajmani@%");
preparedStatement = conn1.prepareStatement("select 1 as rowNumber, gpstime from xydata a join fm4features f on a.ID = f.ID where a.objectId = 18 and a.clientId = 1 and a.gpstime >= '2010-05-11 00:00:00' and a.gpstime <= '2010-05-15 23:59:59' and f.DataId = 1 and f.value = 1 union all select 2, gpstime from xydata where objectId = 18 and clientId = 1 and gpstime >= '2010-05-11 00:00:00' and gpstime <= '2010-05-15 23:59:59' and speed > 3");
rs = preparedStatement.executeQuery();
long totalTimeInMillis=0;
Set<String> dates = new HashSet<String>();
ArrayList<Integer> l=new ArrayList<Integer>();
ArrayList<Integer> m=new ArrayList<Integer>();
ArrayList<Long> n=new ArrayList<Long>();
Map <String, Long> datesAndTotal = new HashMap<String, Long>();
DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
Timestamp t1 = null;
Timestamp t2 = null;
int count = 0;
long sum = 0;
long result=0;
long results=0;
while ( rs.next() )
{
String gpstime = rs.getString("GpsTime");
if (dates.add(gpstime))
{
Date d2 = rs.getDate(1);
t2 = rs.getTimestamp(1);
nano = t2.getSeconds();
System.out.println(t2);
System.out.println(nano);
count++;
if (t1 == null )
{
//nano = t2.getSeconds();
//System.out.println(t2);
//System.out.println(nano);
}
else
{
totalTimeInMillis = (t2.getTime() - t1.getTime()) / 1000;
String dateString = df.format(t2);
System.out.println("GPSTime " +gpstime+ " Time in seconds: " + totalTimeInMillis+ " seconds.");
if (datesAndTotal.containsKey(dateString))
{
m.add((totalTimeInMillis < 80) ? (int) totalTimeInMillis : (int) (totalTimeInMillis % 60));
for (int y=0; y<m.size();)
{
sum+=m.get(y);
y++;
}
sum+= (datesAndTotal.get(dateString))/-1;
System.out.println(sum);
}
datesAndTotal.put(dateString, sum);
}
t1 = t2;
}
}
System.out.println(datesAndTotal);
List mapKeys = new ArrayList(datesAndTotal.keySet());
List mapValues = new ArrayList(datesAndTotal.values());
Collections.sort(mapValues);
Collections.sort(mapKeys);
LinkedHashMap Maps = new LinkedHashMap();
Iterator valueIt = mapValues.iterator();
while (valueIt.hasNext()) {
Object val = valueIt.next();
Iterator keyIt = mapKeys.iterator();
while (keyIt.hasNext()) {
Object key = keyIt.next();
if (datesAndTotal.get(key).toString().equals(val.toString()))
{
datesAndTotal.remove(key);
mapKeys.remove(key);
Maps.put(key, val);
break;
}
}
}
Iterator it1 = Maps.entrySet().iterator();
String prevKey="";
while (it1.hasNext()) {
Map.Entry entry = (Map.Entry) it1.next();
String presentKey=(String)entry.getKey();
if(prevKey.length()==0){
System.out.println(presentKey+"=>"+entry.getValue());
results=(Long) entry.getValue()+60;
System.out.println(results);
arr1.add(presentKey);
int hours = (int) (results / 3600),
remainder = (int) (results % 3600),
minutes = remainder / 60,
seconds = remainder % 60;
disHour = (hours < 10 ? "0" : "") + hours;
disMinu = (minutes < 10 ? "0" : "") + minutes;
disSec = (seconds < 10 ? "0" : "") + seconds;
if (results==0 ||nano<1)
{
//int se = Integer.parseInt(disSec);
//String min = (t2.getSeconds() < 10 ? "0" : "") + t2.getSeconds();
System.out.println("00" + ":" + 00 + ":" + 00 + " hh:mm:ss");
arr.add(00 + ":" + 00 + ":" + 00);
}
System.out.println(disHour +":"+ disMinu+":"+disSec+ " hh:mm:ss");
arr.add(disHour + ":" + disMinu + ":"+ disSec);
}
else{
Long present=(Long)entry.getValue();
Long prev=(Long) Maps.get(prevKey);
result=(Long) (present - prev)+30;
System.out.println(""+presentKey+"=>"+result);
System.out.println(result);
arr1.add(presentKey);
int hours = (int) (result / 3600),
remainder = (int) (result % 3600),
minutes = remainder / 60,
seconds = remainder % 60;
disHour = (hours < 10 ? "0" : "") + hours;
disMinu = (minutes < 10 ? "0" : "") + minutes;
disSec = (seconds < 10 ? "0" : "") + seconds;
if (result==0||nano < 1)
{
int se = Integer.parseInt(disSec);
String min = (t2.getSeconds() < 10 ? "0" : "") + t2.getSeconds();
System.out.println("00" + ":" + min + ":" + se + " hh:mm:ss");
arr.add(00 + ":" + 00 + ":" + 00);
}
System.out.println(disHour +":"+ disMinu+":"+disSec+ " hh:mm:ss");
arr.add(disHour + ":" + disMinu + ":"+ disSec);
}
prevKey=(String)presentKey;
n.add(result);
}
n.add(results);
for (int i=0; i<n.size();)
{
Total+=n.get(i);
i++;
}
System.out.println(Total);
int hours = (int) (Total / 3600),
remainder = (int) (Total % 3600),
minutes = remainder / 60,
seconds = remainder % 60;
disHour = (hours < 10 ? "0" : "") + hours;
disMinu = (minutes < 10 ? "0" : "") + minutes;
disSec = (seconds < 10 ? "0" : "") + seconds;
if (Total==0||count < 2)
{
int se = Integer.parseInt(disSec);
String min = (t2.getSeconds() < 10 ? "0" : "") + t2.getSeconds();
System.out.println("00" + ":" + min + ":" + se + " hh:mm:ss");
arr.add(00 + ":" + 00 + ":" + 00);
}
System.out.println("Total Time=>" +disHour +":"+ disMinu+":"+disSec+ " hh:mm:ss");
}catch(Throwable th)
{
Logger.getLogger(testing1.class.getName()).log(Level.SEVERE, null, th);
}finally
{
if(preparedStatement!=null)
preparedStatement.close();
if(conn!=null)
conn.close();
}
for(int i = 0; i < arr.size(); )
{ arr.get(i);
System.out.println(arr1.get(i));
System.out.println(arr.get(i));
i++;
}
}
}
for the above code i am not able to get output total time=>1(row1) and Total time->2(row2)
how can i get the single query with different total time
i run separe i am getting following output
query 1->select gpstime from xydata a inner join fm4features f on a.ID=f.ID where a.objectId= '18' AND a.clientId = '1' AND a.gpstime >= '2010-06-01 00:00:00' AND a.gpstime <= '2010-06-03 23:59:59' AND f.DataId='1' AND f.value = '1'
query 2->select gpstime from xydata where objectId= '18' AND a.clientId = '1' AND a.gpstime >= '2010-06-01 00:00:00' AND a.gpstime <= '2010-06-03 23:59:59' AND speed > '3'"
i got two output like
query 1 =>09:59:11 hh:mm:ss(row1)
query 2=>05:39:53 hh:mm:ss(row2)
i need this two output in a single query
it will give the following error
SELECT gpstime, * FROM ( SELECT gpstime FROM xydata a INNER JOIN fm4features f ON a.ID=f.ID WHERE a.objectId= '18' AND a.clientId = '1' AND a.gpstime >= '2010-05-11 00:00:00' AND a.gpstime <= '2010-05-15 23:59:59' AND f.DataId='1' AND f.value = '1') AS a
UNION ALL SELECT gpstime, * FROM ( SELECT gpstime FROM xydata
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM ( SELECT gpstime FROM xydata a INNER JOIN fm4features f ON a.ID=f.ID' at line 1
You could include a number which indicates which select the result was from.
SELECT 1, * FROM ( select gpstime from xydata a inner join fm4features f on a.ID=f.ID where a.objectId= '18' AND a.clientId = '1' AND a.gpstime >= '2010-05-11 00:00:00' AND a.gpstime <= '2010-05-15 23:59:59' AND f.DataId='1' AND f.value = '1') as a
UNION ALL SELECT 2, * FROM ( select gpstime from xydata where objectId= '18' AND clientId = '1' AND gpstime >= '2010-05-11 00:00:00' AND gpstime <= '2010-05-15 23:59:59' AND speed > '3') as b
select 1 as rowNumber,
gpstime
from xydata a
join fm4features f
on a.ID = f.ID
where a.objectId = 18
and a.clientId = 1
and a.gpstime >= '2010-05-11 00:00:00'
and a.gpstime <= '2010-05-15 23:59:59'
and f.DataId = 1
and f.value = 1
union all
select 2,
gpstime
from xydata
where objectId = 18
and clientId = 1
and gpstime >= '2010-05-11 00:00:00'
and gpstime <= '2010-05-15 23:59:59'
and speed > 3
keep track of when the rowNumber column changes and write the header.
i need
Column1
2010-05-11 12:20:40
2010-05-11 12:21:41
2010-05-11 12:22:41
2010-05-12 09:05:26
2010-05-12 12:00:37
2010-05-12 12:01:37
2010-05-12 12:02:38
2010-05-12 12:12:06
2010-05-13 09:22:57
2010-05-13 11:45:22
2010-05-13 11:46:23
2010-05-13 11:46:34
2010-05-13 11:47:35
2010-05-13 11:48:35
2010-05-13 12:28:46
2010-05-13 12:29:47
Column2
2010-05-11 12:57:11
2010-05-11 12:58:11
2010-05-11 12:59:12
2010-05-11 13:02:12
2010-05-11 13:03:12
2010-05-11 13:07:08
2010-05-12 12:01:37
2010-05-12 12:02:38
2010-05-12 12:13:07
2010-05-12 12:14:07
2010-05-12 12:15:07
2010-05-13 11:47:35
2010-05-13 11:48:35
2010-05-13 12:29:47
2010-05-13 12:30:47
2010-05-13 12:31:47
2010-05-13 12:32:47
like this