Handling of daylight saving time / string timestamps with timezone: "long series must be sorted"

[quixand]

This might be a grafana error but google is not helping. Here in the UK the clocks changed from BST to GMT at 2 am so my timestamps have rolled back an hour. e.g.

2022-10-30T01:59:13.081467+01:00,Inlet Pipe,13.9
2022-10-30T01:59:13.961973+01:00,Barrel Base,15
2022-10-30T01:00:15.015978+00:00,Barrel Lid,13.6

To Reproduce my grafana query is very simple

select *
from barrel_temp_sensors

up to midnight the graph was working ok, and if I limit the query to dates before midnight or after 2 am it works ok. Any query that returns both BST and GMT timestamps generates the below error in Grafana

long series must be sorted ascending by time to be converted

This query returns data ok

from barrel_temp_sensors
where timestamp > '2022-10-30T02%';

Interestingly, I have another graph that is not affected by this issue, different table but same timestamp format. The timestamp change occurs in the same way, the only difference is that the table has 2 columns not 3

2022-10-30T01:59:14.942960+01:00,159
2022-10-30T01:00:18.625634+00:00,169

SELECT timestamp,
  value as "depth from lid"
from barrel_fluid_height
where value < 1000

• OS: Ubuntu
• Run via docker? no
• Browser firefox
• Grafana Version v9.2.2
• Plugin Version 3.1.0
• Plugin installed via grafana-cli

[quixand]

theres a hole in my graph now, lol

[fr-ser]

Could you provide me with a query containing the data in the query itself (as a subquery or CTE) so that I can reproduce the issue?

[quixand]

so this works ok

with cte(timestamp, sensor_name, value)
as (VALUES ('2022-10-30T01:50:00.000+01:00', 'Inlet Pipe', 1),
           ('2022-10-30T01:59:00.000+01:00', 'Inlet Pipe', 5),
           ('2022-10-30T01:10:00.000+00:00', 'Inlet Pipe', 10),
           ('2022-10-30T01:20:00.000+00:00', 'Barrel Lid', 15))
select * from cte

But if the timestamps are out of sequence in the DB it fails, e.g.

as (VALUES ('2022-10-30T01:50:00.000+01:00', 'Inlet Pipe', 1),
           ('2022-10-30T01:10:00.000+00:00', 'Inlet Pipe', 10),
           ('2022-10-30T01:59:00.000+01:00', 'Inlet Pipe', 5),
           ('2022-10-30T01:20:00.000+00:00', 'Barrel Lid', 15))
select * from cte

if you try to sort that result it also fails

order by timestamp asc;

I suspect that as sqlite doesnt have a timestamp datatype its not sorting the timestamp field correctly?

[fr-ser]

Yeah, SQLite has no date type. Unix timestamps are a great thing, though.

How about this for your CTE

with cte(timestamp, sensor_name, value)
as (VALUES ('2022-10-30T01:50:00.000+01:00', 'Inlet Pipe', 1),
           ('2022-10-30T01:59:00.000+01:00', 'Inlet Pipe', 5),
           ('2022-10-30T01:10:00.000+00:00', 'Inlet Pipe', 10),
           ('2022-10-30T01:20:00.000+00:00', 'Barrel Lid', 15))
select unixepoch(timestamp) AS ts, value from cte
order by 1 asc