CODEWITHSUNDEEP
phpmysqldate
Edward Hammock
Edward Hammock

Reputation: 57

MySQL Query For Latest Record Before a Date

I have a MySQL database which stores LogID, unix timestamp, temperature, humidity and light level. I am trying to get a query that extracts the last record before a set date & time. My data is as follows:

|69511|2017-04-24 19:53:23|19.8|52.7|1.76
|69512|2017-04-24 20:07:57|20|53.8|1.86
|69513|2017-04-24 20:12:00|20.1|54.9|1.07
|69514|2017-04-24 20:29:58|20.2|53.8|1.95

So if the required date was 2017-04-24 20:10:00 the query would return the record:

|69512|2017-04-24 20:07:57|20|53.8|1.86

as the first record preceding 2017-04-24 20:10:00.

Can anyone help? Thanks.

Upvotes: 1

Views: 1859

Answers (3)

nobody
nobody

Reputation: 11080

Filter the records in where clause using the timestamp. Sort it by descending order and get the top 1 record using limit.

select  * from table_name
where unix_timestamp  < '2017-04-24 20:10:00' 
order by unix_timestamp desc
limit 1

Upvotes: 0

Don&#39;t Panic
Don&#39;t Panic

Reputation: 41810

Use a WHERE clause to limit your search to records before the date in question, then sort in descending order by the timestamp (sorting by ID will probably work as well) and take the first record with LIMIT 1.

SELECT * FROM your_table
WHERE ts_column < '2017-04-24 20:10:00'
ORDER BY ts_column DESC LIMIT 1

I improvised the name of your table & timestamp column, but this should give you the general idea.

Upvotes: 2

DB1500
DB1500

Reputation: 155

Perhaps try: 

 SELECT * FROM {tablename} WHERE {timestamp column} < '2017-04-24 20:10:00' LIMIT 1

EDIT: add 'order by unix_timestamp desc'

Upvotes: 0

Related Questions