0
+----------------------------+   +----------------------------+
| ID |       Date            |   | ID |       Date            |
+----------------------------+   +----------------------------+
| 1  |   2017-06-13 22:10:01 |   | 1  | 2017-06-20 22:10:50   |
+----------------------------+   +----------------------------+
| 2  |   2017-06-14 13:22:20 |   | 2  | 2017-06-23 22:10:55   |
+----------------------------+   +----------------------------+
| 3  |   2017-06-16 22:10:01 |   | 3  | 2017-06-27 22:10:35   |
+----------------------------+   +----------------------------+
                                 | 4  | 2017-07-04 22:10:07   |
                                 +----------------------------+

I have 2 tables, same columns. The first one will be a sample, having entries on a week basis, each with 1-minute difference (10 080 entries in total). While the other one gets new values everytime (1 min), indefinitely.

What I try to do is compare each new entry with an entry from the first database. But I want to compare depending on weekday and time (same hour and same minutes).

For instance, an entry on Monday at 11:00 (whatever month, day and year) should be compared with one on Monday at 11:00 from the sample database.

What I want to do is get the number of the entry from sample database when it's the same weekday as the entry in the second ​table:

2017-06-20 22:10:50 should return 1.
2017-06-23 22:10:55 should return 3.
2017-06-27 22:10:35 should return 1.
2017-07-04 22:10:07 should return 1.

Edit2:

I think you may understand things better when I explain the purpose of the two tables.

The real tables in my database have more columns: sensors' id and their value.
The first table, sample table, will have data received for a whole week. It's used as a reference.
The second table receives data every minute, when one gets in it should be compared with a record in the sample table to detect if values are equals or not(anormal value). So I want to detect abnormal values by comparing with a record of same weekday, same hours and minutes.

Shin
  • 1
  • 1
  • 1
    Please elaborate. Show the sample data for the second database and the desired outcome. – peterm Jun 13 '17 at 04:16
  • I edited my first post. Hope this will help. – Shin Jun 13 '17 at 05:07
  • *I actually have 2 databases* ... I think you actually mean I have 2 *tables* – Giorgos Betsos Jun 13 '17 at 06:35
  • Also, I can't see any relation between the sample data provided and the expected results. For example there is no `22:10:50` timestamp present in the data of the first table. – Giorgos Betsos Jun 13 '17 at 06:37
  • What happens when the sample table contains multiple matching rows? – CL. Jun 13 '17 at 07:48
  • Yes there are two tables, my mistake. In date/time formats, seconds are irrelevant. There can't be multiple matches as there is a unique entry for the whole week. – Shin Jun 13 '17 at 10:00

1 Answers1

0

Well I think I found what I was looking for. This is a useful link : Get month from DATETIME in sqlite

Here is the way to match timestamps : 

//Matching weekday
select * from table where strftime('%w', Date) = '0';

//Matching weekday + hours and minutes
select * from table where strftime('%w', Date) = '0' and strftime('%H:%M', Date) = '22:10';

Thank you guys anyway.

Shin
  • 1
  • 1