grafana-interval-variable-to-seconds-questdb.sql

grafana-interval-variable-to-seconds-questdb.sql

# This is free and unencumbered software released into the public domain.
#
# Anyone is free to copy, modify, publish, use, compile, sell, or
# distribute this software, either in source code form or as a compiled
# binary, for any purpose, commercial or non-commercial, and by any
# means.
#
# In jurisdictions that recognize copyright laws, the author or authors
# of this software dedicate any and all copyright interest in the
# software to the public domain. We make this dedication for the benefit
# of the public at large and to the detriment of our heirs and
# successors. We intend this dedication to be an overt act of
# relinquishment in perpetuity of all present and future rights to this
# software under copyright law.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
# EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
# MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
# IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY CLAIM, DAMAGES OR
# OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,
# ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
# OTHER DEALINGS IN THE SOFTWARE.
#
# For more information, please refer to <https://unlicense.org>

-- Environment: Grafana, QuestDB

-- The issue:
-- Interval variable is used instead of $__interval.
-- https://grafana.com/docs/grafana/latest/variables/variable-types/add-interval-variable/
-- How to convert an interval value (like "2m") to seconds.
-- In other words, how to get $_interval_ms for a self defined interval variable.

-- Solution:
with
    interval_s as (
        select (case
            when '$interval' like '%s' then cast(left('$interval', -1) as int)*1
            when '$interval' like '%m' then cast(left('$interval', -1) as int)*60
            when '$interval' like '%h' then cast(left('$interval', -1) as int)*3600
            when '$interval' like '%d' then cast(left('$interval', -1) as int)*86400
            when '$interval' like '%w' then cast(left('$interval', -1) as int)*604800
            when '$interval' like '%M' then cast(left('$interval', -1) as int)*2678400
            when '$interval' like '%y' then cast(left('$interval', -1) as int)*31536000
            else 0 end
        ) as value
    )
select value from interval_s