Daylight saving - Interpretation of datetimeoffset values seems wrong

[Luther-ch]

Hi, 

I have a database table with primary key defined as "Timestamp datetimeoffset", which I am using in power bi desktop.

Here are the records around the time that daylight saving ended. Here in the UK the clocks went back at 02:00 on Sunday 25 October.  (To verify this see https://www.gov.uk/when-do-the-clocks-change)

So datetimeoffset records at 5 minutes apart looked as follows:

2020-10-25 01:50:00.0000000 +01:00

2020-10-25 01:55:00.0000000 +01:00

2020-10-25 01:00:00.0000000 +00:00

2020-10-25 01:05:00.0000000 +00:00

I have 2 visualations that plot data from this table by direct query. They are not using the data of the hour before the 'repeated hour'. The data for the repeated hour (I mean the hour which started when it was 1am for the second time that day) seems to overwrite the previous hour (i.e. the hour which started when it was 1am for the first time!) ;  Space is allowed on the graph (the standard power bi line chart) for what would seem to be the repeated hour, but nothing is plotted in that space.

Below I'll paste the first two columns of the data from Azure Data Studio. The red lines are the ones not used in the visualisations.

2020-10-25 00:00:00.0000000 +01:00, 21.308168
2020-10-25 00:05:00.0000000 +01:00, 21.319891
2020-10-25 00:10:00.0000000 +01:00, 21.344925
2020-10-25 00:15:00.0000000 +01:00, 21.322361
2020-10-25 00:20:00.0000000 +01:00, 21.342012
2020-10-25 00:25:00.0000000 +01:00, 21.39195
2020-10-25 00:30:00.0000000 +01:00, 21.421347
2020-10-25 00:35:00.0000000 +01:00, 21.43882
2020-10-25 00:40:00.0000000 +01:00, 21.283054
2020-10-25 00:45:00.0000000 +01:00, 21.417086
2020-10-25 00:50:00.0000000 +01:00, 21.308895
2020-10-25 00:55:00.0000000 +01:00, 21.449009

2020-10-25 01:00:00.0000000 +01:00, 21.327456
2020-10-25 01:05:00.0000000 +01:00, 21.303434
2020-10-25 01:10:00.0000000 +01:00, 21.29834
2020-10-25 01:15:00.0000000 +01:00, 21.289528
2020-10-25 01:20:00.0000000 +01:00, 21.214638
2020-10-25 01:25:00.0000000 +01:00, 21.192074
2020-10-25 01:30:00.0000000 +01:00, 21.141777
2020-10-25 01:35:00.0000000 +01:00, 21.142576
2020-10-25 01:40:00.0000000 +01:00, 21.192436
2020-10-25 01:45:00.0000000 +01:00, 21.18807
2020-10-25 01:50:00.0000000 +01:00, 21.067348
2020-10-25 01:55:00.0000000 +01:00, 21.149496
2020-10-25 01:00:00.0000000 +00:00, 21.183054
2020-10-25 01:05:00.0000000 +00:00, 21.20117
2020-10-25 01:10:00.0000000 +00:00, 21.084715
2020-10-25 01:15:00.0000000 +00:00, 21.153135
2020-10-25 01:20:00.0000000 +00:00, 20.980265
2020-10-25 01:25:00.0000000 +00:00, 21.21474
2020-10-25 01:30:00.0000000 +00:00, 21.141851
2020-10-25 01:35:00.0000000 +00:00, 21.20437
2020-10-25 01:40:00.0000000 +00:00, 21.178972
2020-10-25 01:45:00.0000000 +00:00, 21.199717
2020-10-25 01:50:00.0000000 +00:00, 21.190983
2020-10-25 01:55:00.0000000 +00:00, 21.070885  [The graph visualisation suggests there is an hour gap with no data at this point]
2020-10-25 02:00:00.0000000 +00:00, 21.102182
2020-10-25 02:05:00.0000000 +00:00, 20.989737
2020-10-25 02:10:00.0000000 +00:00, 21.094181
2020-10-25 02:15:00.0000000 +00:00, 21.018116
2020-10-25 02:20:00.0000000 +00:00, 21.003271
2020-10-25 02:25:00.0000000 +00:00, 21.017025
2020-10-25 02:30:00.0000000 +00:00, 20.980621
2020-10-25 02:35:00.0000000 +00:00, 20.963245
2020-10-25 02:40:00.0000000 +00:00, 20.949331
2020-10-25 02:45:00.0000000 +00:00, 20.9457
2020-10-25 02:50:00.0000000 +00:00, 20.89339
2020-10-25 02:55:00.0000000 +00:00, 20.980637

Is this a bug or have I missed something? If its a bug can anyone suggest a workaround?!

Thanks

[lbendlin]

Not really a bug - just the quirks of DST changeover.

Have you considered using UTC datetime values instead?