So you have a field containing date and time, and this field is already interpreted as timestamp by QV when reading in?
And you want to count all records falling into a 24 hours period starting from any date's 8 am, ending 8 am the day after?
Then look into daystart() function, something like
daystart(EventTimestampField, 8/24) as Bucket,
Then create a chart with dimension Bucket and expression
Not sure what you mean with past dates. It should handle all dates the same, no difference between past and future dates except you have some logic in your script to do so.
Format is important, there are some classic blog posts by Henric that explain why and how to handle, e.g.
That's hard to answer without knowing your input field format.
When you look at your field values using e.g. a list box, how are the values displayed?
And when hovering over the field in table view, what meta data are displayed? Does QV recognize the field as Date or Timestamp, having a numeric representation?
Please check Henric's two blog posts, they should explain how to load date / time values just fine.