Finding The Latest Timestamp Value From A Set Of Values In MySQL

Storing sets of data with a timestamp is common practice, especially if you want to keep a history of the values that have been stored previously. Lets say you wanted to store a piece of information about two variables, each of which can have a history. Here is an example dataset.

Value1Value2DataTimestamp
1111206057387
1211206057387
1311206057387
1111206059020
1311206059020
2111206057387
2221206057387
2331206057387
2111206059020

Value2 has been updated in this table three times. To get the latest data where Value1 is equal to 1 you can use the following query.

SELECT * FROM datatable WHERE datatable.timestamp >= (SELECT MAX(timestamp) AS time FROM datatable WHERE Value1 = 1) ORDER BY Value2 ASC;

This uses a subquery as part of the WHERE clause in order to find the maximum timestamp available for Value2.

If the data items are added over a period of a few seconds you can use the TRUNCATE() function with a negative integer as the second argument to group the timestamp values in a more meaningful way. Using a negative value with TRUNCATE replaces numbers to the right of the number with zeros. Use the following SQL statement to see what the function does with the timestamp.

SELECT UNIX_TIMESTAMP(), TRUNCATE(UNIX_TIMESTAMP(),-8);

This will change the value 1206112846 to 1200000000.

Using the truncate function with the timestamp value in this way can lead to problems if your data is entered every few seconds. If it is the case then you would need to enter values into database using the truncate function so that if two values are entered at more or less the same time the new one replaces the old.

Add new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
14 + 4 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.