Define time interval as a dimension

[#1]

Good morning/evening everyone !

I tried today to define as a dimension time intervals having the date of events in a table in excel. I want to count the number of events that happened between 8h and 8h for instance. What is the dimension to define.

Tahnk you

[swuehl · Accepted solution]

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

LOAD

     Event,

     EventTimestampField,

     daystart(EventTimestampField, 8/24) as Bucket,

     ....

FROM ...;

Then create a chart with dimension Bucket and expression

=count(Event)

[#3]

Thank you,

Does it consider the past dates? And how about format?

[swuehl]

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.

Get the Dates Right

The Date Function

[#5]

Also, how can I make QV recognize my date as a timestamp?

[swuehl]

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.