topic Re: User session query between 9am to 7pm in a week in Real User Monitoring

User session query between 9am to 7pm in a week

cleoleeps — Thu, 01 Feb 2024 12:40:40 GMT

Normally we pull user session query data by using the timeframe on top right of the page (e.g. 1 Jan 2024 - 20 Jan 2024)

But recently we are requested to show the data between 9am - 7pm for a week.

I was trying to do it via user session query but the moment I added the HOUR(starttime) >= '10' or so, it just gave error: "Query parsing error"

Is the request doable actually?

Is this query something wrong?

SELECT starttime,

DATETIME(starttime), MONTH(starttime), DAY(starttime), HOUR(starttime)

FROM useraction where usersession.country IN ("United Kingdom") And HOUR(starttime)>='10' and HOUR(starttime) <='19'

ORDER BY starttime, HOUR(starttime) ASC

dannemca — Thu, 01 Feb 2024 20:39:11 GMT

The problem is with your Where condition.

Thats the only format it does support:

Format Description Example

—	Unix timestamp as a number in milliseconds	1514152800000
yyyy-MM-dd'T'HH:mm:ssZ	ISO datetime with the time zone	2017-12-24T21:00:00+01:00
yyyy-MM-dd HH:mm:ss	Date with optional time	2017-12-24 21:00
yyyy/MM/dd HH:mm:ss	Date with optional time	2017/12/24 21
MM/dd/yyyy HH:mm:ss	Date with optional time	12/24/2017
dd.MM.yyyy HH:mm:ss	Date with optional time	24.12.2017 21:00:00

cleoleeps — Fri, 02 Feb 2024 12:30:23 GMT

Yeah i read that too about timestamp. But I just wanted to filter by the hour not the date. Hence it's a bit tricky here.

The USQL able to get data for HOUR(starttime) as seen in the result table below. But unfortunately I can't use it to do further filtering.

GosiaMurawska — Tue, 13 Aug 2024 14:06:52 GMT

Hi @cleoleeps
Have you managed to solve the problem? It would be great if you shared the solution with the Community users 🙂