I have a list of sensors of many machines, that stores on a second base (i.e., 86400 records each day for each machine). One of the many columns is the datetime of the value (that can also be stored separately as date and time). I'm using PostgreSQL 9.5.

Frequently, I need to find out what dates have sensor's records available for each machine.

I thought about creating a database view of distinct dates for each machine, but after searching for "materialized views" I think I'm ok with updating this list once in a while each day, as data is stored daily.

Would anyone give any advice about doing it?

The table is similar to

ID | Machine_code | Sensor_1 | Sensor_2 | Sensor3 | Datetime
1  | Drimmer      | 12       |  15.8    | 13.4    | 2016-03-03 10:00:03 GMT+1

If it helps anyhow, I'm using Django Framework in front of it.

2 Answers 2

create materialized view foo as 
  select distinct machine_code, date(datetime) from thing;

create unique index on foo(machine_code,date);

Beware that the conversion of datetime to date will occur in the timezone of whoever last created or refreshed the MV. You might want to use this instead:

create materialized view foo as 
  select distinct machine_code, date(datetime at time zone 'GMT+1') from thing;

But that might not do what you want with daylight savings time.

  • thank you for your answer. I think with this materialized view, I'll be able to refresh it daily and have it updated as expected. Thank you. Mar 4, 2016 at 12:33

I thought you wanted something like this

Create table dummy
  Mymachine int,
  Myval int,
  Mysense date

 Insert into dummy Values (1, 2, '2016-02-25');
 Insert into dummy values (1, 3, '2016-02-26');
 Insert into dummy Values (2, 4, '2016-02-24');
 Insert into dummy Values (2, 5, '2016-02-25');
 Insert into dummy Values (2, 6, '2016-02-27');
 Insert into dummy Values (2, 2, '2016-02-25');

And then specify a given range in your query, thus:

SELECT DISTINCT Mymachine, DATE(Mysense) FROM dummy
Where date(Mysense) in
  SELECT (generate_series('2016-02-15', '2016-03-10', '1 day'::interval))::DATE

p.s. don't call a field datetime - it's a reserved word in SQL, plus it makes debugging more difficult!

  • thank you for your answer. I'll follow your recommendation about not using datetime. In fact, right now I'm using timestamp. Thank you so much. Mar 4, 2016 at 12:34
  • 1
    Don't use that either - AFAIK, timestamp is also reserved! As well as making debugging a pain, apps that point at databases with these kind of field names are a nightmare to port. Since you liked my answer, maybe you could see your way to giving me a +1? :-)
    – Vérace
    Mar 4, 2016 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.