PostGres SQL - Group By Week Always Starting on Thursday

  • What Grafana version and what operating system are you using?

Grafana 9.2.4 and Linux

  • What are you trying to achieve?
    Group the data by Weeks for a PostGres SQl table

  • How are you trying to achieve it?
    Using timeGroupAlias function

Query: SELECT
$__timeGroupAlias(endtime,1w),
count(DISTINCT(executionid)) AS QUEUE_COUNT, avg(queuetime) as AVG_QUEUETIME
FROM robot_run
WHERE $__timeFilter(endtime) and queuetime > 0
GROUP BY $__timeGroup(endtime,1w)
ORDER BY $__timeGroup(endtime,1w)

  • What happened?

Week always starting on Thursday. tried the workarounds of using GROUP BY time(1w,4d) but getting error for Postgres

  • What did you expect to happen?
    I expect week to start on Monday but not on Thursday.

  • Can you copy/paste the configuration(s) that you are having problems with?
    SELECT
    $__timeGroupAlias(endtime,1w),
    count(DISTINCT(executionid)) AS QUEUE_COUNT, avg(queuetime) as AVG_QUEUETIME
    FROM robot_run
    WHERE $__timeFilter(endtime) and queuetime > 0
    GROUP BY $__timeGroup(endtime,1w)
    ORDER BY $__timeGroup(endtime,1w)

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

db query error: pq: syntax error at or near “w”

Notice the documentation for that timegroupalias function

Sorry I didn’t understand what you are trying to say!! I already tried the example from documentation but no luck.

1 Like

look at the documentation for

$__timeGroupAlias(dateColumn,'5m')

could it be that you need to wrap 1w with single quotes? ‘1w’

It wont make any difference!!