Select the last event before the specified date.

Question

Suppose I have 2 tables that are described below: EventDatacontains gps events for each device almost every 60 seconds (means a massive amount of data)

CREATE TABLE IF NOT EXISTS `EventData` (
  `accountID` varchar(32) NOT NULL,
  `deviceID` varchar(32) NOT NULL,
  `timestamp` int(10) unsigned NOT NULL,
  `statusCode` int(10) unsigned NOT NULL,
  `latitude` double DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  `gpsAge` int(10) unsigned DEFAULT NULL,
  `speedKPH` double DEFAULT NULL);

Device contains information about devices

CREATE TABLE IF NOT EXISTS `Device` (
  `deviceID` varchar(32) NOT NULL,
  `groupID` varchar(32) DEFAULT NULL,
  `equipmentType` varchar(40) DEFAULT NULL,
  `speedLimitKPH` double DEFAULT NULL,
  `deviceType` varchar(24) DEFAULT NULL,
  `pushpinID` varchar(32) DEFAULT NULL,
  `description` varchar(50) DEFAULT NULL);

I need a query that returns the last event for each device up to a given timestamp ( timestamp<$givenTimestamp), and it should be the only query, otherwise it will cost a lot of time. Is there a way to do this?

---

I tried this:

SELECT
    d.deviceID,
    d.description,
    d.pushpinID,
    e.latitude,
    e.longitude,
    MAX(e.timestamp) as timestamp
FROM
    Device as d
INNER JOIN
    EventData as e
        ON d.deviceID = e.deviceID
WHERE
    timestamp <= $time
GROUP BY
    (e.deviceID);

But he does not return what I want. it returns the last event, not the last event until a given time.

Answer 1

Try this query:

select d.deviceID ,d.description,d.pushpinID,e.latitude,e.longitude, 
       (select max(timestamp) from EventData where deviceID=e.deviceID) as timestamp
FROM Device as d INNER JOIN EventData as e ON d.deviceID=e.deviceID WHERE 
  timestamp < '$time' GROUP BY e.deviceID