MySQL Forums
Forum List  »  Backup

Re: Backup and trimming data idea
Posted by: newbie Shai
Date: June 25, 2012 10:16AM

Dear Rick,
Below are the details you required.

Some of the queries.
Query 1

SELECT DISTINCT tblLocationFrom.geoFenceName As locationFrom, tblLocationTo.geoFenceName As locationTo,
tblLink.linkID,CAST(Date_Add(tblLink.dateTimeStartJourney, Interval '".$gmtValue."' hour_minute) AS CHAR) As dateTimeStartJourney,
CAST(Date_Add(tblLink.dateTimeEndJourney, Interval '".$gmtValue."' hour_minute) AS CHAR) As dateTimeEndJourney1,CAST(Date_Add(CAST(Date_Add(tblLink.dateTimeEnd, Interval '-08:00' hour_minute) AS CHAR) , Interval '".$gmtValue."' hour_minute) AS CHAR) As dateTimeEndJourney2,
FROM tblLink
JOIN tblGeoFence AS tblLocationFrom ON tblLink.locationFromID = tblLocationFrom.geoFenceID
JOIN tblGeoFence AS tblLocationTo ON tblLink.locationToID = tblLocationTo.geoFenceID
WHERE (dateTimeStartJourney between '".$b. "' And '".$e."' And tblLink.compID=$cID

Query 2

SELECT tblGeoFence.geoFenceName, CAST(Date_Add(tblEventAlert.eventAlertDateTime , Interval '".$gmtValue."' hour_minute) AS CHAR) As eventAlertDateTime
FROM tblEventAlert
LEFT JOIN tblGeoFence ON tblEventAlert.geoFenceID=tblGeoFence.geoFenceID
WHERE tblEventAlert.link=".$lID." Order By tblEventAlert.eventAlertDateTime Asc";

Create table statements.

CREATE TABLE IF NOT EXISTS `tblLink` (
`linkID` int(5) NOT NULL,
`compID` int(5) NOT NULL,
`vehicleID` int(5) NOT NULL,
`deviceID` int(5) NOT NULL,
`locationFromID` int(5) NOT NULL,
`locationToID` int(5) NOT NULL,
`employeeIDInsert` int(5) NOT NULL,
`dateTimeInsert` datetime NOT NULL,
`dateTimeStartJourney` datetime NOT NULL,
`dateTimeEnd` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`dateTimeEndJourney` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`linkStatus` enum('a','d','e','m') NOT NULL,
PRIMARY KEY (`linkID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `tblEmailLog` (
`emailLogID` int(11) NOT NULL AUTO_INCREMENT,
`compID` smallint(6) NOT NULL,
`linkID` int(11) NOT NULL DEFAULT '0',
`userID` smallint(6) NOT NULL,
`alertCodeID` tinyint(4) NOT NULL,
`eventAlertID` int(11) NOT NULL,
`userEmail` varchar(100) NOT NULL,
`alertDateTime` datetime NOT NULL,
`alertInsertDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`emailLogID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `tblEventAlert` (
`eventAlertID` int(11) NOT NULL AUTO_INCREMENT,
`compID` int(5) NOT NULL,
`linkID` int(11) NOT NULL DEFAULT '0',
`mainDataID` int(5) NOT NULL,
`vehicleID` int(5) NOT NULL,
`geoFenceID` int(5) NOT NULL,
`eventAlertDateTime` datetime NOT NULL,
`eventAlertSentEmail` varchar(50) DEFAULT NULL,
`eventAlertMessage` varchar(255) NOT NULL,
PRIMARY KEY (`eventAlertID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;



CREATE TABLE IF NOT EXISTS `tblMainData` (
`mainDataID` int(11) NOT NULL AUTO_INCREMENT,
`linkID` int(5) NOT NULL,
`header` varchar(3) NOT NULL,
`deviceSerialNumber` varchar(20) NOT NULL,
`latitude` double NOT NULL,
`longitude` double NOT NULL,
`speed` float NOT NULL,
`course` int(3) NOT NULL,
`dateTimer` datetime NOT NULL,
`gpsDateTime` datetime NOT NULL,
`insertDateTime` datetime NOT NULL,
`odoMeter` float NOT NULL DEFAULT '0',
`driverID` int(5) NOT NULL,
`eventAlertID` int(11) NOT NULL DEFAULT '0',
`mainDataInsertDateTime` datetime NOT NULL,
`gpsString` varchar(450) NOT NULL,
PRIMARY KEY (`mainDataID`),
KEY `dateTime` (`dateTimer`),
KEY `linkID` (`linkID`),
KEY `eventAlertID` (`eventAlertID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `tblSubData` (
`subDataID` int(11) NOT NULL AUTO_INCREMENT,
`mainDataID` int(11) NOT NULL,
`linkID` int(11) NOT NULL,
`eventAlertID` int(11) NOT NULL,
`deviceSerialNumber` varchar(20) NOT NULL,
`subdeviceSerialNumber` varchar(20) NOT NULL,
`dateTimer` datetime NOT NULL,
`eventType` varchar(2) NOT NULL
PRIMARY KEY (`subDataID`),
KEY `mainDataID` (`mainDataID`),
KEY `linkID` (`linkID`),
KEY `eventAlertID` (`eventAlertID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;



Edited 1 time(s). Last edit at 06/26/2012 08:36AM by newbie Shai.

Options: ReplyQuote


Subject
Views
Written By
Posted
3508
June 22, 2012 07:34PM
1687
June 23, 2012 05:23PM
1745
June 23, 2012 05:36PM
1762
June 24, 2012 01:51PM
Re: Backup and trimming data idea
1795
June 25, 2012 10:16AM
1650
June 26, 2012 08:14AM
1743
June 26, 2012 08:46AM
1630
June 27, 2012 08:36AM
1730
June 27, 2012 09:03AM
1592
June 28, 2012 11:19PM
1546
June 29, 2012 07:12AM
1549
June 30, 2012 11:50AM
1625
July 02, 2012 09:47AM
1567
July 03, 2012 01:32PM
1614
July 05, 2012 11:13AM
1783
July 06, 2012 09:57AM
1572
July 07, 2012 10:50AM
1593
July 08, 2012 09:33AM
1584
July 16, 2012 08:08PM
1564
July 21, 2012 10:57PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.