I have an IoT device that logs data every 3 minutes and uploads that data to Azure Table Storage every 10 minutes. This means the table entities have the built in Timestamp property and a date property. Sadly, date is stored as a string and not as an actual datetime. Timestamp is automatically generated as the time Azure got the data, but date is the timestamp for when the device recorded the data. This means that several entities can have the same timestamp, but different dates (actual data):
Timestamp date tagName tagValue
2025-04-03T18:20:19.2764867Z 2025-04-03T18:19:55Z LEVEL 52.2
2025-04-03T18:20:19.2754824Z 2025-04-03T18:11:14Z LEVEL 52.2
2025-04-03T18:20:19.2754824Z 2025-04-03T18:15:35Z LEVEL 52.2
2025-04-03T18:10:20.8985209Z 2025-04-03T18:02:54Z LEVEL 52.2
2025-04-03T18:00:19.3550008Z 2025-04-03T17:58:33Z LEVEL 52.2
2025-04-03T18:00:19.3550008Z 2025-04-03T17:54:13Z LEVEL 52.2
2025-04-03T17:50:20.126223Z 2025-04-03T17:45:52Z LEVEL 52.2
2025-04-03T17:50:20.126223Z 2025-04-03T17:49:53Z LEVEL 52.2
My goal is to query the table for the most recent level reading using the REST API. My plan to accomplish this is to get all entities uploaded in the last hour then sort through them to find the most recent date property. I've tried two approaches:
This URL returns data 90% of the time:
https://mystorage.table.core.windows.net/HistoryValues?sv=2019-02-02&st=2025-04-02T15%3A33%3A02Z&se=2030-04-03T15%3A33%3A00Z&sp=r&sig=<mysig>&tn=HistoryValues&$filter=Timestamp%20ge%20datetime%272025-04-02T20:33:02Z%27%20and%20tagName%20eq%20%27LEVEL%27&$select=tagValue,date
This one never returns data:
https://mystorage.table.core.windows.net/HistoryValues?sv=2019-02-02&st=2025-04-02T15%3A33%3A02Z&se=2030-04-03T15%3A33%3A00Z&sp=r&sig=<mysig>&tn=HistoryValues&$filter=date%20ge%20%272025-04-02T20:33:02Z%27%20and%20tagName%20eq%20%27TOWER_LEVEL%27&$select=tagValue,date
Both return HTTP status 200, but only the first query returns entities. The second does not return any entities. If put the filter date ge '2025-04-02T20:33:02Z' and tagName eq 'TOWER_LEVEL' in Azure Storage Explorer, it currently returns 312 entities.
The simple solution seems to be to filter one the Timestamp. I ran the function that queries by timestamp every 3 minutes overnight, and every 30 minutes no entities are returned. At 5:03 -> data, 5:06 -> data, 5:09 -> data, etc. until 5:30 when 0 entities were returned. At 5:33 I got data. This continued: 5:36 -> data, 5:39 -> data, ..., 5:57 -> data, but at 6:00 zero entities were returned again. But 6:03, 6:06, 6:09, etc all work as expected until 6:30 when I got no data. This pattern held all night long. No data was ever returned at the top and bottom of the hour. This is why I thought I would try to filter by date, but this is not working at all.
Why would the Timestamp query fail every 30 minutes even though there is clearly data that matches the query? Why does my date filter work in Storage Explorer but not in the REST query? Any advice on how to achieve the goal of getting the most recent level reading when date is stored as a string?
I eventually achieved my goal by taking a different approach. Rather than generating a query string to pull the last 60 or 65 minutes of data and processing it to find the most current value, I created an Azure function app and table storage to store the most current value of the sensor reading.
The table, SensorDataExport, is set up so that the device's ID is the PartitionKey and the desired sensor reading is the RowKey. It has one additional property named sensorValue. The function app loads all the entities from SensorDataExport and loads the last 65 minutes of data from the devices historical logs table. It then finds the most current value and writes it back to the SensorDataExport table. I didn't try loading only 60 minutes of data, so maybe 65 minutes is a magic number. My function app runs every minute, but I think I could adjust that back to every 3 minutes. As a bonus, since PartitionKey and RowKey are indexed properties, the query string returns data in 1-2 seconds vs the 4-6 seconds it took before. All of this worked flawlessly for the 48 hour test I did.
Sample entity from the table:
PartitionKey RowKey Timestamp tagValue
1234567 LEVEL 2025-04-11T14:31:30.1186383Z 53.2
And the Azure Function App:
using Microsoft.Azure.Functions.Worker;
using Azure.Data.Tables;
using Azure;
namespace UpdateSensorDataExport
{
public class UpdateSensorDataExport
{
[Function("UpdateSensorDataExport")]
public async Task Run([TimerTrigger("0 * * * * *", RunOnStartup = true)] TimerInfo myTimer)
{
var serviceClient = new TableServiceClient(Environment.GetEnvironmentVariable("AzureWebJobsStorage"));
var globalTable = serviceClient.GetTableClient("SensorDataExport");
await foreach (var entity in globalTable.QueryAsync<TableEntity>())
{
string device = entity.PartitionKey;
string sensorName = entity.RowKey;
string historyTableName = $"{device}HistoryValues";
var historyTable = serviceClient.GetTableClient(historyTableName);
// Get latest history value using string 'date', limited to entities from last 65 minutes
var cutoff = DateTimeOffset.UtcNow.AddMinutes(-65);
var historyEntities = historyTable
.Query<TableEntity>(e => e.GetString("tagName") == sensorName && e.Timestamp >= cutoff)
.Where(e => e.ContainsKey("date"))
.ToList();
var latestHistory = historyEntities
.OrderByDescending(e => DateTime.Parse(e.GetString("date")))
.FirstOrDefault();
if (latestHistory != null)
{
string newTagValue = GetSensorValueAsString(latestHistory);
// Update GlobalTagExport
entity["tagValue"] = newTagValue;
Console.WriteLine(newTagValue);
await globalTable.UpdateEntityAsync(entity, ETag.All, TableUpdateMode.Replace);
}
}
}
private static string GetSensorValueAsString(TableEntity entity)
{
var value = entity["tagValue"];
return value?.ToString();
}
}
}