How to split the two timestamp based on interval time in IRIS Database?

Question

Question

I have two timestamp values and also I have interval time, I need to split the timestamp based on the interval time. Like

Start Time is '2024-01-01T10:00:00'
End Time is '2024-01-01T11:00:00'
Interval Time is 15 minutes

I expected the result is:

'2024-01-01T10:15:00'
'2024-01-01T10:30:00'
'2024-01-01T10:45:00'
'2024-01-01T11:00:00'

Discussion (11)1

Log in or sign up to continue

Julius Kavay · Mar 28

/// start, end: timestamp format
/// int       : the interval in seconds
/// mod       : 0 = use the time value as is
///            +1 = round-up the timestamp to a multiple of <int>
///            -1 = round-down the timestamp to a multiple of <int>
///            
/// return an JSON array: [time1, time2, ... timeN]
/// 
ClassMethod Intervals(start, end, int = 15*60, mod = 0) As %DynamicArray
{
    set res=[], t(0)=$zdth(start,3,1), t(1)=$zdth(end,3,1)
    
    for i=0,1 {
        set t(i)=t(i)*86400+$p(t(i),",",2) 
        if mod,t(i)#int { set t(i)=t(i)-(t(i)#int) set:mod>0 t(i)=t(i)+int }
    }

    for t=t(0):int:t(1) do res.%Push($zdt(t\86400_","_(t#86400),3,1))
    quit res
}

0 0

Julius Kavay · Mar 28

If you process thousands of time splits, it's a good idea using %List or just a simple %String instead of JSON - you can have a time savings by factor of about 10!

/// old line
set res=[]
for t=t(0):int:t(1) do res.%Push($zdt(t\86400_","_(t#86400),3,1))
quit res

/// new line
set res=""
for t=t(0):int:t(1) set res=res_$lb($zdt(t\86400_","_(t#86400),3,1))
quit res

/// or
set res=""
for t=t(0):int:t(1) set res=res_","_$zdt(t\86400_","_(t#86400),3,1)
quit $e(res,2,*)

To see the differences, try loops like this

/// with JSON
s h=$zh f i=1:1:1E5 { s r=[] f j=1:1:10 { d r.%Push("abcd") } } w $zh-h,!

/// with %List
s h=$zh f i=1:1:1E6 { s r="" f j=1:1:10 { s r=r_$lb("abcd") } } w $zh-h,!

/// with %String
s h=$zh f i=1:1:1E6 { s r="" f j=1:1:10 { s r=r_","_"abcd" } } w $zh-h,!

0 0

Vitaliy Serdtsev · Mar 29

See Defining Custom Class Queries

Example of a stored procedure

Class dc.test [ Abstract ]
{

Query Intervals(
start As %TimeStamp,
end As %TimeStamp,
minute As %TinyInt) As %Query(ROWSPEC = "intStart:%PosixTime,intEnd:%PosixTime") [ SqlName = Intervals, SqlProc ]
{
}

ClassMethod IntervalsExecute(
ByRef qHandle As %Binary,
start As %TimeStamp,
end As %TimeStamp,
minute As %TinyInt) As %Status
{
  s qHandle(0)=##class(%PosixTime).OdbcToLogical(start),
    qHandle(1)=##class(%PosixTime).OdbcToLogical(end),
    qHandle=minute
  
  q $$$OK
}

ClassMethod IntervalsFetch(
ByRef qHandle As %Binary,
ByRef Row As %List,
ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = IntervalsExecute ]
{
  i qHandle(0)<=qHandle(1) {
    s j=$system.SQL.Functions.DATEADD("minute",qHandle,qHandle(0)),
      Row=$lb(qHandle(0),$s(j>=qHandle(1):qHandle(1),1:j-1)),
      qHandle(0)=j=qHandle(1)+j
  }else{
    s Row="", AtEnd=$$$YES
  }
  q $$$OK
} 

ClassMethod IntervalsClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = IntervalsExecute ]
{
  q $$$OK
}
}

The result of calling a stored procedure in the Management Portal:

SELECT * FROM dc.Intervals({ts '2024-01-01 10:00:00'},{ts '2024-01-01 11:00:00'},15)

Display/ODBC Mode
intStart	intEnd
2024-01-01 10:00:00	2024-01-01 10:14:59.999999
2024-01-01 10:15:00	2024-01-01 10:29:59.999999
2024-01-01 10:30:00	2024-01-01 10:44:59.999999
2024-01-01 10:45:00	2024-01-01 11:00:00.000000

Logical Mode
intStart	intEnd
1154625607806846976	1154625608706846975
1154625608706846976	1154625609606846975
1154625609606846976	1154625610506846975
1154625610506846976	1154625611406846976

Accordingly, your query needs to be rewritten, for example:

SELECT
intStart,
intEnd,
COALESCE((SELECT SUM(CASE WHEN value_after_changing = 'GENERATE' THEN 1 ELSE 0 END) FROM service_log WHERE id = 11 AND created_at BETWEEN intStart AND intEnd), 0) generatedCount,
COALESCE((SELECT SUM(CASE WHEN value_after_changing = 'FINISH' THEN 1 ELSE 0 END) FROM service_log WHERE id = 11 AND created_at BETWEEN intStart AND intEnd), 0) finishedCount,
COALESCE((SELECT SUM(CASE WHEN value_after_changing = 'DROPOUT' THEN 1 ELSE 0 END) FROM service_log WHERE id = 11 AND created_at BETWEEN intStart AND intEnd), 0) dropoutCount,
COALESCE((SELECT SUM(CASE WHEN value_after_changing = 'CANCEL' THEN 1 ELSE 0 END) FROM service_log WHERE id = 11 AND created_at BETWEEN intStart AND intEnd), 0) cancelledCount
FROM dc.Intervals({ts '2024-01-01 10:00:00'},{ts '2024-01-01 11:00:00'},15)

1 0

Developer Commu... · Apr 9

💡 This question is considered a Key Question. More details here.

0 0

score 0 · Answer 1 · 2024-03-28T10:44:42-04:00

Hai @Julius Kavay ,

Thank you for your suggestion,

I have retrieved the huge records, so it will little more complicated compared with query calls, Maybe possibly causing the performance issue of using this implementation. So we must use the database query.

score 0 · Answer 2 · 2024-03-28T12:44:58-04:00

Parameshwaran M... · Mar 28

Could you please check my updated question!

0 0

score 0 · Answer 3 · 2024-03-28T14:33:47-04:00

Can you please ensure, my answer matches the original question? Thanks! Usually one makes a new question instead of changing an old question to a new one. Currently both my answers hang around without an context to the above question.

score 0 · Answer 4 · 2024-03-28T14:58:44-04:00

Hello @Julius Kavay

SORRY for the inconvenience!! I have updated my query in the below thread. I am not sure your answers match my question, because my expected answer is different. But I would like to thank and respect to you. Can you please assist me?

score 0 · Answer 5 · 2024-03-28T13:48:20-04:00

PLEASE!
if you have a different question then PLEASE don't change the original content
this breaks the total information flow.
Existing replies will look like nonsense!

score 0 · Answer 6 · 2024-03-28T14:46:26-04:00

Hello @Robert Cemper

SORRY for the inconvenience!! I made some additional changes to the original content to add more clarity, that is the reason I have changed the content. This won't happen again in the future. I have updated the original content the same as previously. But I would like to thank and respect you.

Here, is the My Query

SELECT
COALESCE(SUM(CASE WHEN service_log.value_after_changing = 'GENERATE' THEN 1 ELSE 0 END), 0) AS generatedCount,
COALESCE(SUM(CASE WHEN service_log.value_after_changing = 'FINISH' THEN 1 ELSE 0 END), 0) AS finishedCount,
COALESCE(SUM(CASE WHEN service_log.value_after_changing = 'DROPOUT' THEN 1 ELSE 0 END), 0) AS dropoutCount,
COALESCE(SUM(CASE WHEN service_log.value_after_changing = 'CANCEL' THEN 1 ELSE 0 END), 0) AS cancelledCount
FROM
service_log
WHERE
service_log.id = 11 AND
service_log.created_at BETWEEN '2024-03-28T07:00:00' AND '2024-03-28T08:00:00'
GROUP BY
FLOOR(DATEDIFF('MINUTE', TO_TIMESTAMP('2024-03-28T07:00:00', 'YYYY-MM-DDTHH:MI:SS'), created_at) / 15 (i.e.,interval time));

This query implementation currently retrieve the data when available in each interval period if data not available in respective interval period no providing any data but my expectation is when data not available need to provide all counts as 0 value.

This is current response for fourth interval (07:46 to 08:00) :
[
{
"cancelled": 0,
"dropOut": 0,
"finished": 16,
"issued": 2
}
]
Expected response like this:
[
{
"cancelled": 0,
"dropOut": 0,
"finished": 0,
"issued": 0
},
{
"cancelled": 0,
"dropOut": 0,
"finished": 0,
"issued": 0
},
{
"cancelled": 0,
"dropOut": 0,
"finished": 0,
"issued": 0
},
{
"cancelled": 0,
"dropOut": 0,
"finished": 2,
"issued": 16
}
]

Thanks once again! @Robert Cemper for guidance

score 0 · Answer 7 · 2024-04-01T10:43:36-04:00

Parameshwaran M... · Apr 1

Hello @Vitaliy Serdtsev

Thank you so much for you providing above response. Its really being helpful.

0 0