How to split the two timestamp based on interval time in IRIS Database?
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'
Comments
/// 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
}
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.
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 lineset res=[]
for t=t(0):int:t(1) do res.%Push($zdt(t\86400_","_(t#86400),3,1))
quit res
/// new lineset res=""for t=t(0):int:t(1) set res=res_$lb($zdt(t\86400_","_(t#86400),3,1))
quit res
/// orset 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 JSONsh=$zh f i=1:1:1E5 { sr=[] f j=1:1:10 { dr.%Push("abcd") } } w$zh-h,!
/// with %Listsh=$zh f i=1:1:1E6 { sr="" f j=1:1:10 { sr=r_$lb("abcd") } } w$zh-h,!
/// with %Stringsh=$zh f i=1:1:1E6 { sr="" f j=1:1:10 { sr=r_","_"abcd" } } w$zh-h,!Could you please check my updated question!
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.
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?
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!
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 thankand respect you.
Here, is the My Query
SELECT
COALESCE(SUM(CASE WHEN service_log.value_after_changing = 'GENERATE' THEN 1ELSE0 END), 0) AS generatedCount,
COALESCE(SUM(CASE WHEN service_log.value_after_changing = 'FINISH' THEN 1ELSE0 END), 0) AS finishedCount,
COALESCE(SUM(CASE WHEN service_log.value_after_changing = 'DROPOUT' THEN 1ELSE0 END), 0) AS dropoutCount,
COALESCE(SUM(CASE WHEN service_log.value_after_changing = 'CANCEL' THEN 1ELSE0 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
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)| 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 |
| 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)Hello @Vitaliy Serdtsev
Thank you so much for you providing above response. Its really being helpful.
💡 This question is considered a Key Question. More details here.