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

Note: This post is over two years old and so the information contained here might be out of date. If you do spot something please leave a comment and we will endeavour to correct.

20th March 2008 - 3 minutes read time

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.

Value1	Value2	Data	Timestamp
1	1	1	1206057387
1	2	1	1206057387
1	3	1	1206057387
1	1	1	1206059020
1	3	1	1206059020
2	1	1	1206057387
2	2	2	1206057387
2	3	3	1206057387
2	1	1	1206059020

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.

MySQL

Add new comment

Related Content

Solving Query Performance Problems With The MySQL Slow Query Log

21st January 2024

MySQL's slow query log is a key component in your MySQL administration setup. Whilst normal logging can help you in terms of tracking down issues with your database system, the slow query log can help you track down issues in your database setup before they become problematic.

Creating An Authentication System With PHP and MariaDB

15th October 2023

Using frameworks to handle the authentication of your PHP application is perfectly fine to do, and normally encouraged. They abstract away all of the complexity of managing users and sessions that need to work in order to allow your application to function.

Case Insensitive Like Searches In MySQL

2nd July 2011

I needed to create a query that did a case insensitive search using the LIKE command in MySQL and I quickly realised that in order to do this I would need to alter both the parameter and the table data to be the same case. This can be done by using the MySQL UPPER() command on the table data and the strtoupper() PHP function on the input data.

Connecting To A Non Standard MySQL Socket In PHP

11th February 2011

Connecting to a MySQL database in PHP is usually not a difficult thing to do, in fact it is one of the first things that many tutorials will go though. However, there are certain curcumstances that require more information than the standard host, password and username details.

Count Number Of Characters In A String With MySQL

30th September 2010

Today I needed to grab some data from a table where there was more than one occurrence of a string within another string. Basically, I needed to find all URL's from a table that were more than 3 levels deep (i.e. with 3 slashes), but realised there wasn't a function to do this in MySQL.

MySQL Order Table By Character Length

24th September 2009

As part of debugging a bit of code I needed to know the longest possible field lengths that a record contains. You might need to know this if you are performing a database migration. The following query returns a field, along with the length of the string, and orders the results by the number of characters in that string.