# How to convert intervals to seconds

**URL:** <https://community.grafana.com/t/how-to-convert-intervals-to-seconds/11854>\
**Category:** MySQL\
**Tags:** timestamp, mysql, query-help\
**Created:** [November 8, 2018, 3:10pm UTC](https://community.grafana.com/t/how-to-convert-intervals-to-seconds/11854 "2018-11-08T15:10:26Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![apetzold](https://avatars.discourse-cdn.com/v4/letter/a/858c86/32.png) [@apetzold](https://community.grafana.com/u/apetzold)\
**Post date:** [November 8, 2018, 3:10pm UTC](https://community.grafana.com/t/how-to-convert-intervals-to-seconds/11854/1 "2018-11-08T15:10:26Z")

</div>

Hi,

My dashboard has a interval variable which is used instead of $\_\_interval. What I’m looking for is a way to convert my custom interval from things like “2m” to “120” i.e. the number of seconds. $\_\_interval\_ms does this for $\_\_interval but I can do this for my custom interval.

Thanks,  
Andreas

---

<div class="post-metadata">

**Author:** ![swimmingtiger](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/swimmingtiger/32/8345_2.png) [@swimmingtiger](https://community.grafana.com/u/swimmingtiger)\
**Post date:** [October 6, 2019, 8:11pm UTC](https://community.grafana.com/t/how-to-convert-intervals-to-seconds/11854/2 "2019-10-06T20:11:09Z")

</div>

I use this in MySQL, it’s ugly but works. I think that Grafana really should add a conversion macro, which is really a breeze.

```sql
IF('$interval' LIKE '%m', '$interval' * 60, IF('$interval' LIKE '%h', '$interval' * 3600, IF('$interval' LIKE '%d', '$interval' * 86400, '$interval')))

```

Full SQL:

```sql
SELECT
  $__unixEpochGroupAlias(timestamp,$interval,0),
  pool_name AS metric,
  sum(diff / IF('$interval' LIKE '%m', '$interval' * 60, IF('$interval' LIKE '%h', '$interval' * 3600, IF('$interval' LIKE '%d', '$interval' * 86400, '$interval')))) AS "hashrate"
FROM shares
WHERE
  $__unixEpochFilter(timestamp)
GROUP BY 1,2
ORDER BY $__unixEpochGroup(timestamp,$interval,0)

```

---

<div class="post-metadata">

**Author:** ![cabbo](https://avatars.discourse-cdn.com/v4/letter/c/e47774/32.png) [@cabbo](https://community.grafana.com/u/cabbo)\
**Post date:** [April 15, 2022, 2:59pm UTC](https://community.grafana.com/t/how-to-convert-intervals-to-seconds/11854/3 "2022-04-15T14:59:45Z")

</div>

For SQL Server I use:

```auto
DECLARE @time_interval_s INT = CASE WHEN '${time_interval}' LIKE '%m' THEN LEFT('${time_interval}',DATALENGTH('${time_interval}')-1) * 60
            WHEN '${time_interval}' LIKE '%h' THEN LEFT('${time_interval}',DATALENGTH('${time_interval}')-1) * 3600
            WHEN '${time_interval}' LIKE '%d' THEN LEFT('${time_interval}',DATALENGTH('${time_interval}')-1) * 86400
            ELSE '${time_interval}'
       END

```

---

<div class="post-metadata">

**Author:** ![westonforbes](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/westonforbes/32/26085_2.png) [@westonforbes](https://community.grafana.com/u/westonforbes)\
**Post date:** [September 12, 2022, 9:35pm UTC](https://community.grafana.com/t/how-to-convert-intervals-to-seconds/11854/4 "2022-09-12T21:35:24Z")

</div>

This guy for the win…Saved me a buncha monkeying around.