How to efficiently query for records that share a year

datesqlite

I've got data that looks like

data
uid  id  date        value
1    a   1780-01-01  10
2    a   1780-01-02  11
3    a   1780-01-03  12
4    b   1780-01-01  10
...
999  a   1980-01-01  10

I'd like to know which ids have records between 1880 and 1900. I currently
generate this big query (which I believe works):

SELECT year1880.id FROM
(SELECT DISTINCT id FROM data WHERE strftime("%Y", date) = "1880") as year1880

  INNER JOIN
  (SELECT DISTINCT id FROM data WHERE strftime("%Y", date) = "1881") as year1881
  ON year1880.id = year1881.id

  INNER JOIN
  (SELECT DISTINCT id FROM data WHERE strftime("%Y", date) = "1882") as year1882
  ON year1880.id = year1882.id

  ...

  INNER JOIN
  (SELECT DISTINCT id FROM data WHERE strftime("%Y", date) = "1900") as year1900
  ON year1880.id = year1900.id
  ;

Is this the most efficient way to make this query? Could it be done in a closed
form so I don't need to generate a giant query with python?

The database is sqlite.

Best Answer

This should return all the ids that have at least one record for each year in your date range:

SELECT
  id
FROM
  <your table>
WHERE
  CAST(strftime("%Y", date) as decimal) BETWEEN <min year> AND <max year>
GROUP BY
  id
HAVING
  COUNT(DISTINCT strftime("%Y", date)) = <max year> - <min year> + 1
;

Related Solutions

Showing data in a table horizontally

It looks to me like you are trying to 'pivot' the data, which can be done with multiple case statements:

test data:

create table timestream(
  idTimestream integer primary key autoincrement
, time int not null
, value float not null
, idSensor integer not null);

insert into timestream(time, value, idSensor) values(1,0.1,100);

insert into timestream(time, value, idSensor) values(1,0.2,101);

insert into timestream(time, value, idSensor) values(1,0.3,102);

insert into timestream(time, value, idSensor) values(2,0.4,101);

query:

select time, sum(case idSensor when 100 then value end) as s100,
             sum(case idSensor when 101 then value end) as s101,
             sum(case idSensor when 102 then value end) as s102
from timestream
group by time;

time | s100 | s101 | s102
:--- | :--- | :--- | :---
1    | 0.1  | 0.2  | 0.3 
2    | null | 0.4  | null

dbfiddle here

SQLite – How to Get Data According to Last Hours

Finally I found the solution to my own question

Following is the code which worked for me

DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = new Date();
        Calendar calendar = Calendar.getInstance();
        String startDate = dateFormat.format(date);
        String endDate = "";
        for (int i = 1; i <= 24; i++) {
            System.out.println("Start Date:- " + startDate);
            calendar.add(Calendar.HOUR_OF_DAY, -1);
            date = calendar.getTime();
            endDate = dateFormat.format(date);
            System.out.println("End Date:- " + endDate);
            String data = dbAdapter.getOutDoorHourlyData(startDate, endDate);
            System.out.println("Hourly Average:- " + data);
            startDate = endDate;
            endDate = "";
        }

public String getOutDoorHourlyData(String startDate, String endDate) {
        double outdoorHourly = 0;

        Cursor cursor = _sqliteDB.rawQuery("Select AVG("
                + COLOUMN_NAME + ") from (Select * FROM "
                + TABLE_NAME + " where " + COLOUMN_NAME + " >= '"
                + endDate + "' and " + COLOUMN_NAME + " < '" + startDate
                + "')", null);

        try {

            if (cursor != null) {
                if (cursor.getCount() > 0) {
                    cursor.moveToFirst();
                    do {
                        outdoorHourly = cursor.getDouble(0);
                    } while (cursor.moveToNext());
                }
                cursor.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        String hourlyData = decimalFormat.format(outdoorHourly);
        hourlyData = hourlyData.replace(",", ".");
        return hourlyData;

    }

 }

I hope it will help someone in future

Best Answer

Related Solutions

Showing data in a table horizontally

SQLite – How to Get Data According to Last Hours

Related Question