Fetch data based on current time

[its_rajvir]

Dear Experts,

I need help to solve one problem.

Example data : The Day starts at 5:00 AM and ends at next day 3:00 AM (Working Hours)

Date	Hour	Sales Amount	Total Sales(Running Total)
11/12/2022	5	60	60
11/12/2022	6	40	100
11/12/2022	7	200	300
11/12/2022	8	300	600
11/12/2022	9	1000	1600
11/12/2022	10	1600	3200
11/12/2022	11	500	3700
11/12/2022	12	2000	5700
11/12/2022	13	1100	6800
11/12/2022	14	60	6860
11/12/2022	15	40	6900
11/12/2022	16	200	7100
11/12/2022	17	300	7400
11/12/2022	18	1000	8400
11/12/2022	19	1600	10000
11/12/2022	20	500	10500
11/12/2022	21	2000	12500
11/12/2022	22	1100	13600
11/12/2022	23	1600	15200
12/12/2022	0	500	15700
12/12/2022	1	2000	17700
12/12/2022	2	1100	18800
12/12/2022	3	20	18820

Now I want to refresh this data every hour based on the current time. For example if the current time is 12:00 PM it should only show the data from 5 to 12 hrs, if current time is 18:00 it should show data from 5 to 18 hrs and if the current time is 02 AM it should show show data from 5 to 2 hrs.

I hope i am clear with the question 🙂

Kindly help  

[justISO]

Hi, in load script add WHERE clause to filter your data:

LOAD *
FROM your_source
WHERE Hour<=hour(ReloadTime()) and Date=today();

[its_rajvir]

@justISO Thank you very much for the reply, I tried the solution i am getting the data less than the ReloadTime but the result is not exactly the same as required because in this way i am getting all the hours less than the current reload hour 

example: I run the data at 9:00 am and its fetching the data of 0,1,2,3,5,6,7,8,9 but it should only fetch  the data from the start of the day 5,6,7,8,9 as shown in the table.

Please help in this 🙂

[justISO · Accepted solution]

Ok, so basically, as I understand, logic should be so: if reload Hour is <5, you load everything from yesterday and some part of today. If hour is >=5 you take everything from today till that Hour. This can be implemented by 'If..then..elseif..else..end if' statement:

sample:
load * inline [
Date	,	Hour
2022-12-12	,	5
2022-12-12	,	6
2022-12-12	,	7
2022-12-12	,	8
2022-12-12	,	9
2022-12-12	,	10
2022-12-12	,	11
2022-12-12	,	12
2022-12-12	,	13
2022-12-12	,	14
2022-12-12	,	15
2022-12-12	,	16
2022-12-12	,	17
2022-12-12	,	18
2022-12-12	,	19
2022-12-12	,	20
2022-12-12	,	21
2022-12-12	,	22
2022-12-12	,	23
2022-12-13	,	0
2022-12-13	,	1
2022-12-13	,	2
2022-12-13	,	3];

// LET vHour = 1; //for testing
LET vHour = hour(ReloadTime());

IF $(vHour)<5 /*or just <=3*/ THEN

NoConcatenate
main:
LOAD *
resident sample
WHERE Date=today()-1 OR (Hour<=$(vHour) and Date=today());

ELSE

NoConcatenate
main:
LOAD *
resident sample
WHERE Hour<=$(vHour) and Date=today();

END IF

drop table sample;

[chaorenzhu]

@justISO  seems like this is not what OP wanted, as your first where condition will include all records of both  yesterday and today until current hour.

Maybe something like:

load * from XXX

where date(floor(timestamp(date#(Date,'DD/MM/YYYY') + time#(Hour,'hh') - '05:00:00'))) = today() and Hour <= hour(now())

[its_rajvir]

HI @justISO this code is working find for me, now i want to update something in the code can you help me with that?

[its_rajvir]

Hi @justISO,

seeking your suggestion to achieve the required output. Now i have two additional fields in the data: 

Date	Hour	Half Hour	Half Hour 2	Sales Amount
11/12/2022	5	0	05:00-05:29	60
11/12/2022	5	1	05:30-05:59	23
11/12/2022	6	0	06:00-06:29	40
11/12/2022	6	1	06:30-06:59	45
11/12/2022	7	0	07:00-07:29	200
11/12/2022	7	1	07:30-07:59	422
11/12/2022	8	0	08:00-08:29	300
11/12/2022	8	1	08:30-08:59	456
11/12/2022	9	0	09:00-09:29	1000
11/12/2022	9	1	09:30-09:59	356
11/12/2022	10	0	10:00-10:29	1600
11/12/2022	10	1	10:30-10:59	432
11/12/2022	11	0	11:00-11:29	500
11/12/2022	11	1	11:30-11:59	754
11/12/2022	12	0	12:00-12:29	2000
11/12/2022	12	1	12:30-12:59	634
11/12/2022	13	0	13:00-13:29	1100
11/12/2022	13	1	13:30-13:59	456
11/12/2022	14	0	14:00-14:29	60
11/12/2022	14	1	14:30-14:59	346
11/12/2022	15	0	15:00-15:29	40
11/12/2022	15	1	15:30-15:59	234
11/12/2022	16	0	16:00-16:29	200
11/12/2022	16	1	16:30-16:59	232
11/12/2022	17	0	17:00-17:29	300
11/12/2022	17	1	17:30-17:59	123
11/12/2022	18	0	18:00-18:29	1000
11/12/2022	18	1	18:30-18:59	221
11/12/2022	19	0	19:00-19:29	1600
11/12/2022	19	1	19:30-19:59	567
11/12/2022	20	0	20:00-20:29	500
11/12/2022	20	1	20:30-20:59	255
11/12/2022	21	0	21:00-21:29	2000
11/12/2022	21	1	21:30-21:59	2234
11/12/2022	22	0	22:00-22:29	1100
11/12/2022	22	1	22:30-22:59	235
11/12/2022	23	0	23:00-23:29	1600
11/12/2022	23	1	23:30-23:59	1766
12/12/2022	0	0	00:00-00:29	500
12/12/2022	0	1	00:30-00:59	35
12/12/2022	1	0	01:00-01:29	634
12/12/2022	1	1	01:30-01:59	335
12/12/2022	2	0	02:00-02:29	1100
12/12/2022	2	1	02:30-02:59	532
12/12/2022	3	0	03:00-03:29	20
12/12/2022	3	1	03:30-03:59	234

Output should be like for example: 

 If I run the data at 9:30 am it should fetch the data from the start of the day 05 to 9:30 (from 05:00-05:29 to 09:00-09-30). 

It would be really helpful if you can suggest some idea.