CODEWITHSUNDEEP
timescaledb
Kelly McDonald
Kelly McDonald

Reputation: 55

Problem with time_bucket and date_trunc or explainable difference?

I have two different queries with drastically different behavior, but I was thinking they should be the same. The first one gives me what I want, the second doesn't. Can you give an explanation for this?

    with base as (
    select time at time zone 'utc' as t, value as kw
    from metrics
    where site = 'IIHS-Phase2'
      and measurement = 'kw'
      and equipment = 'WattNode'
      and time at time zone 'utc' > '2020-04-1 00:00'
      and time at time zone 'utc' < '2020-05-01 00:00'
      and value > 0),
 quarter as (
         select time_bucket('15min', t) t2, avg(kw) kw
         from base
         group by 1
     )
select date_trunc('day', t2), sum(kw) * .25 kwh
from quarter
group by 1
order by 1

This results in the expected output:

2020-04-03 00:00:00.000000,47.74750179624
2020-04-04 00:00:00.000000,763.0610862812115
2020-04-05 00:00:00.000000,809.9363199208758
2020-04-06 00:00:00.000000,806.3479266995703
2020-04-07 00:00:00.000000,789.4193852521148
2020-04-08 00:00:00.000000,965.5504895999275
2020-04-09 00:00:00.000000,852.1921420684275
2020-04-10 00:00:00.000000,744.5305964113129
2020-04-11 00:00:00.000000,779.511450610154

The other query :

with quarter as (
    select time_bucket('15min', time at time zone 'utc') as localtime, avg(value) * .25 as kwh
    from metrics
    where site = 'IIHS-Phase2'
      and measurement = 'kw'
      and equipment = 'WattNode'
      and time at time zone 'utc' > '2020-04-1 00:00'
      and time at time zone 'utc' < '2020-05-01 00:00'
      and value > 0
    group by 1)
select date_trunc('day', localtime), sum(kwh)
from quarter

Gives completely different output:

0 years 0 mons 0 days 0 hours 0 mins 0.00 secs,22946.408690298373

What am I missing?

Upvotes: 1

Views: 456

Answers (2)

k_rus
k_rus

Reputation: 3219

Your second query uses keyword localtime, which returns current time without timezone. I guess this is the issue. I suggest either to use double quotes around it, i.e., "localtime", or use another name for the identifier.

UDPATE: as mentioned in another reply by davidk, you are missing group by also.

Upvotes: 2

davidk
davidk

Reputation: 1053

It looks like you don't have a group by in the second query?

Upvotes: 0

Related Questions