CODEWITHSUNDEEP
sqlite
Hariram Nandagopal
Hariram Nandagopal

Reputation: 731

SQLITE query to display the last 1 hour with 10mins interval

I have searched a bit for getting last 1hr details with 10mins interval, as follow

if suppose the current time is 10:30, i am expecting the output as follows , kindly suggest me the solutions. 

  TIME_SLOT

09:31 - 09:40
09:41 - 09:50
09:51 - 10:00
10:01 - 10:10
10:11 - 10:20
10:21 - 10:30

I have the table in the format (CUSTOMER) (FIG 1.1) 

M_CODE  H_CODE END_TIME_OF_SERVICE ADULT ADULT_F
TKLK    LONE     09:19:16           1     2
TKLK    LONE     09:22:11           4     6
TKLK    LONE     09:32:46           2     7
TKLK    LONE     09:32:46           4     9
TKLK    LONE     10:09:36           1     3

and if i run the ( SELECT strftime('%H:%M', end, '-9 minutes') || ' - ' || strftime('%H:%M', end ) AS time_slot FROM end_times ) it gives the correct output as (FIG - 1.2)

09:19 - 09:28
09:29 - 09:38
09:39 - 09:48
09:49 - 09:58
09:59 - 10:08
10:09 - 10:18

Can you guide how to map these two tables, let`s say the the records in CUSTOMER table should be summed up for adult and adult_f column with( FIG 1.2 to FIG 1.1 )

   ADULT    ADULT_F    TIME_SLOT
      5        8      09:19 - 09:28
      6       16      09:29 - 09:38
      0        0      09:39 - 09:48
      0        0      09:49 - 09:58
      0        0      09:59 - 10:08
      1        3      10:09 - 10:18

Kindly guide me a solution, thanks for your time.

Upvotes: 0

Views: 753

Answers (1)

CL.
CL.

Reputation: 180070

SQL queries are designed to handle data that is stored in tables. If you want to create these dates, it might be easiest to just insert these values into a temporary table.

Alternatively, you could construct the values, one by one. First define the offsets from the current time:

CREATE VIEW intervals AS
SELECT -50 AS offset UNION ALL
SELECT -40           UNION ALL
SELECT -30           UNION ALL
SELECT -20           UNION ALL
SELECT -10           UNION ALL
SELECT   0

Then you can construct the time slot end points like this:

CREATE VIEW end_times AS
SELECT datetime('now', offset || ' minutes') AS end
FROM intervals

Then format the time slots like this:

SELECT strftime('%H:%M', end, '-10 minutes') || ' - ' ||
       strftime('%H:%M', end               ) AS time_slot
FROM end_times

Upvotes: 2

Related Questions