User Sessions Query Language - use alias in WHERE clause

[plespera]

Hello, I am trying to write a user sessions query to measure the number of user sessions per hour. I wish to only consider data from working days of the week (Monday to Friday).

Point 1

When I run the following query:

SELECT HOUR(startTime) AS timeOfDay, DATETIME (startTime, "E", "1d") as dayOfWeek, COUNT(*) FROM usersession WHERE useraction.application IS 'myApp' AND dayOfWeek IN ('Mon', 'Tue', 'Wed', 'Thu', 'Fri') GROUP BY timeOfDay

I get the error message Unknown field: dayOfWeek, with the field dayOfWeek in the WHERE clause highlighted.

I tried the following as well:

SELECT HOUR(startTime) AS timeOfDay, startTime, COUNT(*) FROM usersession WHERE useraction.application IS 'dxcoe-agora - prod' AND DATETIME(startTime, "E", "1d") IN ('Mon', 'Tue', 'Wed', 'Thu', 'Fri') GROUP BY timeOfDay

And was met with a generic Query parsing error underlining the DATETIME function.

Any way to achieve what I'm trying to do?

Point 2

I was also wodnering if there was any way to display hours on the line chart where there are no suer sessions (for example, if there are no usersessions at 6pm, I'd like the line chart to still show 6pm and the value of 0).

[Mizső]

Hi @plespera 

I think startTime does not include the day of the week. 

I saw this section in the documneation but I have not found any examples for the day of the weeks.

Or how can we change the default format where we can use "E".

Best regards,

János

[plespera]

Hello @Mizső, that's not true, as I am able to select the startTime transformed as a day of week using the DATETIME function.

The following query poses no issues:

SELECT HOUR(startTime) AS timeOfDay, DATETIME(startTime, "E", "1d") as dayOfWeek, COUNT(*) FROM usersession WHERE useraction.application IS 'myApp' GROUP BY timeOfDay

But as soon as I try to use one of the column aliases in the WHERE block, the query fails with the aformentioned issues. I've tried timeOfDay alias and get the same error.

My feeling right now is that it is not possible to reference aliases or use functions in the WHERE block, but I have not found documentation or a similar question confirming that this is the case.

[Mizső]

Hi @plespera,

Good to know. Thanks for sharing it.

Maybe @HannahM can confirm your assumption regarding the useage of aliases in the WHERE block.

Best regards,

János

[HannahM]

Yes, unfortunately WHERE conditions only work on fields.