0

I am working on an energy application where we are monitoring Diesel Generator and Electric Meters of a factory.

We are having this data in the database. Below are some records present in the DB.

ip           meter_id   device_time
===========================================
100.74.61.93    s1  r1  2018-11-01 09:02:42
100.74.61.93    s1  r1  2018-11-01 09:02:57
100.74.61.93    s1  r1  2018-11-01 09:03:12
100.74.61.93    s1  r1  2018-11-01 09:03:27
100.74.61.93    s1  r1  2018-11-01 09:03:42
100.74.61.93    s1  r1  2018-11-01 09:03:57
100.74.61.93    s1  r1  2018-11-01 09:04:17
100.74.61.93    s1  r1  2018-11-01 09:04:27
100.74.61.93    s1  r1  2018-11-01 09:04:47
100.74.61.93    s1  r1  2018-11-01 09:04:57
100.74.61.93    s1  r1  2018-11-01 09:05:12
100.74.61.93    s1  r1  2018-11-01 09:05:27
100.74.61.93    s1  r1  2018-11-01 09:05:47
100.74.61.93    s1  r1  2018-11-01 09:05:57
100.74.61.93    s1  r1  2018-11-01 09:06:12
100.74.61.93    s1  r1  2018-11-01 09:06:27
100.74.61.93    s1  r1  2018-11-01 09:06:42
100.74.61.93    s1  r1  2018-11-01 09:06:57
100.74.61.93    s1  r1  2018-11-01 09:07:12
100.74.61.93    s1  r1  2018-11-01 09:07:27
100.74.61.93    s1  r1  2018-11-01 09:07:42
100.74.61.93    s1  r1  2018-11-01 09:07:57
100.74.61.93    s1  r1  2018-11-01 09:08:12
100.74.61.93    s1  r1  2018-11-01 09:08:27
100.74.61.93    s1  r1  2018-11-01 09:08:47
100.74.61.93    s1  r1  2018-11-01 09:08:57
100.74.61.93    s1  r1  2018-11-01 09:09:12
100.74.61.93    s1  r1  2018-11-01 09:09:27
100.74.61.93    s1  r1  2018-11-01 09:09:42
100.74.61.93    s1  r1  2018-11-01 09:09:57
100.74.61.93    s1  r1  2018-11-01 09:10:12
100.74.61.93    s1  r1  2018-11-01 09:10:27
100.74.61.93    s1  r1  2018-11-01 09:10:42
100.74.61.93    s1  r1  2018-11-01 09:10:57
100.74.61.93    s1  r1  2018-11-01 09:11:12
100.74.61.93    s1  r1  2018-11-01 09:11:27
100.74.61.93    s1  r1  2018-11-01 09:11:42
100.74.61.93    s1  r1  2018-11-01 09:11:57
100.74.61.93    s1  r1  2018-11-01 09:12:12
100.74.61.93    s1  r1  2018-11-01 09:12:27
100.74.61.93    s1  r1  2018-11-01 09:12:42
100.74.61.93    s1  r1  2018-11-01 09:12:57
100.74.61.93    s1  r1  2018-11-01 09:13:12
100.74.61.93    s1  r1  2018-11-01 09:13:27
100.74.61.93    s1  r1  2018-11-01 11:15:42
100.74.61.93    s1  r1  2018-11-01 09:13:42
100.74.61.93    s1  r1  2018-11-01 09:13:57
100.74.61.93    s1  r1  2018-11-01 11:15:58
100.74.61.93    s1  r1  2018-11-01 11:16:12
100.74.61.93    s1  r1  2018-11-01 11:13:42
100.74.61.93    s1  r1  2018-11-01 11:13:57
100.74.61.93    s1  r1  2018-11-01 11:14:12
100.74.61.93    s1  r1  2018-11-01 11:14:27
100.74.61.93    s1  r1  2018-11-01 11:14:42
100.74.61.93    s1  r1  2018-11-01 11:14:57
100.74.61.93    s1  r1  2018-11-01 11:15:12
100.74.61.93    s1  r1  2018-11-01 11:15:27
100.74.61.93    s1  r1  2018-11-01 11:16:27
100.74.61.93    s1  r1  2018-11-01 11:16:42
100.74.61.93    s1  r1  2018-11-01 11:16:57
100.74.61.93    s1  r1  2018-11-01 11:17:13
100.74.61.93    s1  r1  2018-11-01 11:17:27
100.74.61.93    s1  r1  2018-11-01 11:17:42
100.74.61.93    s1  r1  2018-11-01 11:17:57
100.74.61.93    s1  r1  2018-11-01 11:18:12
100.74.61.93    s1  r1  2018-11-01 11:18:28
100.74.61.93    s1  r1  2018-11-01 11:18:42
100.74.61.93    s1  r1  2018-11-01 11:18:58
100.74.61.93    s1  r1  2018-11-01 11:19:12
100.74.61.93    s1  r1  2018-11-01 11:19:27
100.74.61.93    s1  r1  2018-11-01 11:19:42
100.74.61.93    s1  r1  2018-11-01 11:19:58
100.74.61.93    s1  r1  2018-11-01 11:20:13
100.74.61.93    s1  r1  2018-11-01 11:20:27
100.74.61.93    s1  r1  2018-11-01 11:20:43
100.74.61.93    s1  r1  2018-11-01 11:20:58
100.74.61.93    s1  r1  2018-11-01 11:21:13
100.74.61.93    s1  r1  2018-11-01 11:21:28
100.74.61.93    s1  r1  2018-11-01 11:21:43
100.74.61.93    s1  r1  2018-11-01 11:21:58
100.74.61.93    s1  r1  2018-11-01 11:48:58
100.74.61.93    s1  r1  2018-11-01 11:49:13
100.74.61.93    s1  r1  2018-11-01 11:49:28
100.74.61.93    s1  r1  2018-11-01 11:49:43
100.74.61.93    s1  r1  2018-11-01 11:49:58
100.74.61.93    s1  r1  2018-11-01 11:50:13
100.74.61.93    s1  r1  2018-11-01 11:50:28
100.74.61.93    s1  r1  2018-11-01 11:50:43
100.74.61.93    s1  r1  2018-11-01 11:50:58
100.74.61.93    s1  r1  2018-11-01 11:51:13
100.74.61.93    s1  r1  2018-11-01 11:51:28
100.74.61.93    s1  r1  2018-11-01 11:51:43
100.74.61.93    s1  r1  2018-11-01 11:51:58
100.74.61.93    s1  r1  2018-11-01 11:52:13
100.74.61.93    s1  r1  2018-11-01 11:52:28
100.74.61.93    s1  r1  2018-11-01 11:52:43
100.74.61.93    s1  r1  2018-11-01 11:52:58
100.74.61.93    s1  r1  2018-11-01 11:53:13
100.74.61.93    s1  r1  2018-11-01 11:53:28
100.74.61.93    s1  r1  2018-11-01 11:53:43
100.74.61.93    s1  r1  2018-11-01 11:53:58
100.74.61.93    s1  r1  2018-11-01 11:54:13
100.74.61.93    s1  r1  2018-11-01 11:54:28
100.74.61.93    s1  r1  2018-11-01 11:54:43
100.74.61.93    s1  r1  2018-11-01 11:54:58
100.74.61.93    s1  r1  2018-11-01 11:55:13
100.74.61.93    s1  r1  2018-11-01 11:55:28
100.74.61.93    s1  r1  2018-11-01 11:55:43
100.74.61.93    s1  r1  2018-11-01 11:55:58
100.74.61.93    s1  r1  2018-11-01 11:56:13
100.74.61.93    s1  r1  2018-11-01 11:56:28

As you can check the above records from the database, each row is having device_time which means at that said time Diesel Generator was running.

Now I want to count the time in the format like 01 days, 2 hours, 10 min which will tell me that for how much total time it has been running for?

I wrote the query which gives me the result. 

select
    count(device_timestamp) * '1 minute'::interval as operatingtime
from
    (
    select
        time_bucket('1 min',
        device_time at time zone 'Asia/Kolkata') as device_timestamp
    from
        public.schneider_new
    where
        meter_id in ('s1')
        and device_time >= '2018-11-01T00:00:00.000'
        and device_time <= now()
    group by
        device_timestamp
    order by
        device_timestamp as ) as tab

Output:

0 years 0 mons 0 days 30 hours 28 mins 0.00 secs

This gives me the result, but I don't want 0 years 0 mons 0 secs in the above output.

And also, if the hours is >24 i.e, in this case, is 30 hours, it should give the result as 1 day 6 hours 28 mins.

Where I am going wrong? or Is there any other way by which we can count for what time the DG was running for the given time period?

UPDATE:

I tried the query with this 

(count(device_timestamp)::numeric/60)/24 * '1 day'::interval as operatingtime

Output:

0 years 0 mons 1 days 6 hours 28 mins 0.00 secs

I got the correct output i.e 1 days 6 hours 28 mins, the only thing I am looking for now is to remove the other 0 years 0 mons 0.00 secs.

Vishal Shetty
  • 1,618
  • 1
  • 27
  • 40
  • Can't say what's wrong because we don't know what the function `time_bucket` is doing. It's not part of core Postgres, are you using some kind of external interface? – Kaushik Nayak Nov 21 '18 at 11:13
  • Sorry for that I didn't mentioned, I am using the timescaledb, it is using PostgreSQL and they are providing additional time series functions, `time_bucket` is one of that function. – Vishal Shetty Nov 21 '18 at 11:19

0 Answers0