How should I interpret the timestamp in OR?

[pacogam]

Hi all,
I have some problems writing/reading timestamps and different timezones. Perhaps I don’t quite understand how I should interpret timestamps in OR, so before I go into more detail my first question is simple. If I have the following TS saved in postgresql (indicates without time zone), and then, if I visualize the data, that same TS is the one used in the graph. So, is that local time or UTC?

At least intuitively, when a graph is presented to a user it is thought to be local time. But is it really?

[pacogam]

Well, as no one seems to be responding I’ll consider the timestamps shown in the OR charts to be local time. In fact, this is how it should be considered if not stated otherwise.

I’m now receiving data through a RESTful API. The first value contains a TS=‘2024-09-20 00:00’, it’s local time. With Java I convert it to Epoch time and also add an offset corresponding to 2 hours:

1st step: 1726783200000 (2024-09-19 22:00+0000) ← This one is correct
2nd step: 1726790400000 (2024-09-20 00:00+0000) ← This one would be false

This last value is the one I use to save it in OR:

datapointService.upsertValue(assetId, CustomAsset.ENERGY_IMPORT.getName(), ai, timestampMillis);

Why am I doing this? This is the way to see ‘2024-09-20 00:00’ in both Postgres and OR charts. Does it make sense?

OK, so let’s imagine now that I want to get OR data from its API. The API returns epoch time, and if in Postgres I see ‘2024-09-20 00:00’, the corresponding timestamp returned by the API is 1726783200000, which is ‘2024-09-19 22:00+0000’. Evidently a timestamp that does not correspond to reality.
Has nobody noticed this? I have a hard time to keep remembering the unequal conversion type as I write or read in OR.

Another example with timezone, now I integrated the electricity prices using an API. I get for the first value:

{
“value”: 59.36,
“datetime”: “2024-10-03T00:00:00.000+02:00”
}

This represents a cost ot 0.05936€/kWh from 00h-01h local time. I can see it perfectly in the graph and in the DB.

However, to write that value, I used a timestamp 1727913600000, as ISO 2024-10-03 00:00+0000, which is wrong in relation to reality again.

[Rich]

Hey @pacogam

Apologies for the lack of response here.

There have been issues around timestamp conversion particularly if the manager backend and postgres instances are configured with different time zones which should be resolved once the pending PR is merged.

Back to your specific issue, the idea is that all time is stored in the DB as UTC, without timezone on the assumption that the asset location can convey location information if needed.

Indeed any UI should take the UTC timestamps and they should be shown in local time, if this is not happening then we can investigate more.