I am trying to query this data set of hourly price date. The dataset defined daily prices at 12am - 12am UTC time, I am trying to define the days at 4pm - 4pm UTC time. Therefore I need to get the high and the low prices for each day between ex: '2021-12-15 16:00:00' and '2021-12-16 15:00:00' as that would be the open and close of the trading day.

I have this right now:

SELECT convert(date,dateadd(S, TimeStamp/1000, '1970-01-01')) as 'date' 
,symbol
,Max([high])    as 'Max'
,Min([low])     as 'Min'
FROM [Crypto].[tblMessariPriceHistory]
WHERE symbol = 'DOGE'
and dateadd(S, TimeStamp/1000, '1970-01-01') between '2021-12-15 16:00:00' and '2021-12-16 15:00:00'
Group By convert(date,dateadd(S, TimeStamp/1000, '1970-01-01')),symbol

But it results like this:

I could just group by Symbol but I want to be able to do this over multiple days, and that wouldn't work.

Any ideas on how to define a select date range as a group or table over multiple days?

CodePudding user response：

If you think about it, subtracting 16 h off every time would slide the time back to some time within the "starting day"

Monday 16:00 becomes midnight Monday
Monday 23:59 becomes 7:59 Monday
Tuesday 00:00 becomes 8:00 Monday
Tuesday 15:59 becomes 23:59 Monday
Tuesday 16:00 becomes midnight Tuesday

Anyway, once you've slid your time backwards 16h, you can just chop the time part off by dividing the unix time stamp by the number of milliseconds in a day and all the trades between Monday 16:00 and Tuesday 15:59:59.999 will go down as "Monday". If it were a DateTime we could cast it to a Date to achieve the same thing. It's handy to find ways of treating datetimes as decimal numbers where the integral is the date and the fractional is the time because chopping it to an int discards the time and allows daily aggregating. If you wanted hourly aggregating, adjusting the number so it represents the number of hours and fractions of an hour (divide by 3600000, the number of milliseconds in an hour) helps to the same end

--take off 16h then truncate to number of days since epoch 
SELECT 
  (timestamp-57600000)/86400000 as timestamp, 
  symbol,
  min(low) as minlow,
  max(high) as maxhigh 
FROM trades
GROUP BY (timestamp-57600000)/86400000 as timestamp, symbol