Retrive sessions between hours interval

Analytics and Teneo Query Language
1

Hi,
I know how to query sessions between 2 specific DateTime periods, for example:
la t.e.userInput : s.beginTime == in {“2015-01-01T00:00”…“2015-01-01T01:00”}

But is it possible to query between hours period without the date? For example:
Sth like: la t.e.userInput : s.beginTime == in {“T00:00”…“T09:00”}

Because I want to track all sessions which happened after work for example.

Thank you

2

Hi sc_chatbot!

We have a page in Teneo Developers that refers to this exact use case,

It can be found here and includes two example queries: Use relative dates in queries | Teneo Developers

Please let me know if you have any other questions in this regard, I’m more than happy to help you out!

Warm regards,

3

Hi Ramazan!

Thanks for your answers. I checked the examples which you provided, It is still about the specific duration.For example:

la s.beginTime as timestamp, t.id as transactionId, t.e1.userInput as userInput, t.e2.answerText as botResponse: 
    s.beginTime == in {"now-0h/h"}

I would like to query the periodical duration, such as the session number between 5 pm to 9 pm everyday, not only for today.

Could you give me some ideas about how to do it?

Best regards

4

Hi sc_chatbot!

I modified a query that is on Teneo Developers for you. This will most likely solve your issue.

la s.id , hour, t.e1.userInput as userInput: 
  catd(pattern="HH") s.beginTime as hour,  
  hour == in {"01".."20"}                  
  order by hour

The hours might differ from the country you are currently in and therefore I would recommend you to add timezone to your query as seen here: TQL Reference Guide | Teneo Developers

The query can be found here: Transformer | Teneo Developers

Please let me know if you still have questions or need any further clarifications.

Warm regards,

5

Hi Ramazan!
Thanks for your support. I just tested it, it works fine.
I have one more question about the timezone you mentioned, you mentioned the hours might differ from the country, does it mean that all of these displayed time in this query is based on the default value(UTC time)?
If I want to see the local time when the session happens, if I need to do something like this:
la s.id , hour,zone, t.e1.userInput as userInput:
catd(pattern=“HH”, timezone="+02:00") s.beginTime as hour,
hour == in {“01”…“20”}
order by hour

To convert the time to the local time zone(CET here).

Best regards

6

Hi sc_chatbot!

Yes, that is correct, the times in Inquire are displayed as UTC and you will need to use the timezone parameter to change it, just like you did above.

Edit: adding an example query here:

la s.id , hour, t.e1.userInput as userInput: 
  catd(pattern="HH", timezone="+02:00") s.beginTime as hour,  
  hour == in {"01".."20"}                  
  order by hour

Warm regards,

7

This was a helpful tip! In our case we just wanted the time in queries to generally be adjusted to CET. It worked out by adding this catd pattern:

catd(pattern=“yyyy.MM.dd hh:mm:ss”, timezone="+02:00") s.beginTime as Start_Time

3 Likes