Select records between two dates with pre-established minute intervals

Asked

Viewed 227 times

0

I have a table that stores data collected at five-minute intervals. I would like to select the records between an initial date/time and the final date/time. So far I can do it. Only that I would like to include a third parameter in the query that would be the time interval between collections. Ex. Records from 30-01-2018 00:00:00 until 30-01-2018 12:00:00 in 10 minute intervals.

sql

  • example of data ? example of how you want the output ? table structure ? use Sqlfiddle if possible

  • The table has only two fields: date (datetime), tag (varchar(30). time tag 2016-04-01 00:01:03.580 PDT_1_T13@PV 2016-04-01 00:06:03.580 PDT_1_T13@PV 2016-04-01 00:11:03.580 PDT_1_T13@PV 2016-04-01 00:16:03.580 PDT_1_T13@PV 2016-04-01 00:21:03.580 PDT_1_T13@PV 2016-04-01 00:26:03.580 PDT_1_T13@PV 2016-04-01 00:31:03.580 PDT_1_T13@PV 2016-04-01 00:36:03.580 PDT_1_T13@PV

  • What is the database manager?

1 answer

-1


Insert the following clause into the WHERE:

DATEPART(MINUTE,[CAMPO DATA]) % 10 < 5

Explaining what is being done: I am isolating last digit of the minute of your datetime field and checking whether it is contained in {0,1,2,3,4}. With this I believe will return the expected values. Example (items will be returned in bold:

  • 30/01/2018 08:00
    30/01/2018 08:05
    30/01/2018 08:11
    30/01/2018 08:17
    30/01/2018 08:23
    30/01/2018 08:28
    30/01/2018 08:33
    30/01/2018 08:38

The table has only two fields:
datahora (datetime) tag (varchar(30)
Table content:
date tag
2016-04-01 00:01:03.580 PDT_1_T13@PV
2016-04-01 00:06:03.580 PDT_1_T13@PV
2016-04-01 00:11:03.580 PDT_1_T13@PV
2016-04-01 00:16:03.580 PDT_1_T13@PV
2016-04-01 00:21:03.580 PDT_1_T13@PV
If the selection is 01-04-2016 00:00:00 until 01-04-2016 00:35:00
Break of 10 minutes.
Expected Departure
date tag 2016-04-01 00:01:03.580 PDT_1_T13@PV
2016-04-01 00:11:03.580 PDT_1_T13@PV
2016-04-01 00:21:03.580 PDT_1_T13@PV

  • 1

    The table has only two fields: datatime (datetime) tag (varchar(30) Table content: datatime tag 2016-04-01 00:01:03.580 PDT_1_T13@PV 2016-04-01 00:06:03.580 PDT_1_T13@PV 2016-04-01 00:11:03.580 PDT_1_T13@PV 2016-04-01 00:16:03.580 PDT_1_T13@PV 2016-04-01 00:21:03.580 PDT_1_T13@PV If the selection is 01-04-2016 00:00:00 until 01-04-2016 00:35:00 10 minute interval. Expected Output datatime tag 2016-04-01 00:01:03.580 PDT_1_T13@PV 2016-04-01 00:11:03.580 PDT_1_T13@PV 2016-04-01 00:21:03.580 PDT_1_T13@PV

  • @Sergiopaiva could not understand his comment, I believe it is better you edit the question and put more structured there. Came to test by adding the clause I put in the answer in Where? It worked?

  • I put the answer in the above item.

  • I did not understand if you came to test with the code I passed above or not, because I understand that you would have the expected result. Or what problem has occurred.

  • Felipe, as in the bank I have records in the following hours (hh:mm) at: 00:01, 00:06, 00:11, 00:16 and 00:21. If my selection is from 00:00 to 00:35 with 10 minute intervals, the records that should return are: 00:01, 00:11, 00:21.

  • For the tests I have done here, adding in your Where " AND DATEPART(MINUTE, date) %10 < 5 " would bring exactly the result you expect.

  • Perfect Felipe. It really worked! I was wrong.

Show 2 more comments

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.