Database Schema - time_spent returning no results

[Morfix]

Hello All in Matomo Forum,

I wanted use a query built like that in order to retrieve the “time_spent” fields (table “matomo_log_link_visit_action” ) information:

"
SELECT matomo_log_action.idaction AS idaction,
matomo_log_action.name AS URL,
matomo_log_action.url_prefix AS url_prefix,

LEFT(CONVERT(matomo_log_link_visit_action.server_time, CHAR), 10) AS “Day”,

matomo_log_link_visit_action.time_spent AS “Time on Page”,

matomo_log_visit.idvisit AS “Visit ID”,

matomo_log_visit.idsite AS “Live Site ID”

FROM matomo_log_visit
LEFT JOIN matomo_log_link_visit_action
ON matomo_log_link_visit_action.idvisit = matomo_log_visit.idvisit
LEFT JOIN matomo_log_action
ON matomo_log_link_visit_action.idaction_url = matomo_log_action.idaction
WHERE matomo_log_link_visit_action.idsite = <Parameters.Matomo Live> AND

matomo_log_visit.idsite = <Parameters.Matomo Live>"

However, the result of “time_spent” field is always “Null” - there seem to be no recorded information there. This is inconsistent with how information is presented in our Matomo instance where I can see “time spent on page” information.

If anyone could give a hint as for where this information is hidden in the database schema and why the field time_spent in the matomo_log_link_visit_action table returns no information I would be very grateful.

[heurteph-ei (Philippe)]

Hi @Morfix

@Morfix

where this information is hidden in the database schema

https://developer.matomo.org/guides/database-schema

@Morfix

why the field time_spent in the matomo_log_link_visit_action table returns no information

On the page above, is written:

The time_spent_ref_action column contains the time spent by the visitor on their previous pageview. The previous pageview’s Page URL as defined by idaction_url_ref and previous pageview’s Page Title as defined by idaction_name_ref. For example, to get the Time spent on a particular Page URL: first get the corresponding idaction_url value for this Page URL, then query eg. SELECT count(*) as page_hits, sum(time_spent_ref_action) as total_time_spent_in_seconds FROM log_link_visit_action WHERE idaction_url_ref = IDACTION_URL_ID_HERE. Note: to ensure you collect accurate time spent on each page, enable the Heartbeat timer.

[Morfix]

Thank you very much Philippe!
I still not quite understand how the filed “time_spent” is supposed to work “set by the CustomDimensions plugin”. However, the workaround with the “time_spent_ref_action” is definitely very helpful.