timestamp without time zone is time zone agnostic, so it is not adjusted to any time zone on display. This data type just doesn't have a time zone.
You can convert it to a certain time zone with AT TIME ZONE:
SELECT TIMESTAMP WITHOUT TIME ZONE '2020-08-01 12:00:00' AT TIME ZONE 'UTC';
When I was arranging a similar setup in the past, I did the following:
Use -AutoCreateTable to generate the basic schema for any commands I wanted to write to the database
Modify the tables to use better data types + added a column with a default GETDATE() value
Pipe any results into the newly modified tables.
This is also helpful since any updates to the ...
I don't know if this is the best way to do it but it works.
I'm using a reference table that contains all the shifts and and running the select against it. In this case, I used variable for the start and end timestamp but you can reproduce using your column.
declare @start as datetime
declare @end as datetime
set @start='2020-07-16 04:54:50'
I love this question because I answered another question like it 7 years ago
June 30, 2013 : Get the Time of Last update of a column
You actually wrote the SQL I had suggested. I am impressed.
YOUR ACTUAL QUESTION
IMHO I would go with Suggestion #3 because its implementation is as normalized as you are going to get. The retrieval of the deltas ...