# Suggestion required in Datetime configuration in Named Queries

**URL:** <https://forum.inductiveautomation.com/t/suggestion-required-in-datetime-configuration-in-named-queries/81453>\
**Category:** General Discussion\
**Tags:** database, ignition81, perspective\
**Created:** [November 3, 2023, 11:35am UTC](https://forum.inductiveautomation.com/t/suggestion-required-in-datetime-configuration-in-named-queries/81453 "2023-11-03T11:35:19Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sudhanthiralakshmi\_M](https://avatars.discourse-cdn.com/v4/letter/s/e495f1/32.png) [@Sudhanthiralakshmi\_M](https://forum.inductiveautomation.com/u/Sudhanthiralakshmi_M)\
**Post date:** [November 3, 2023, 11:35am UTC](https://forum.inductiveautomation.com/t/suggestion-required-in-datetime-configuration-in-named-queries/81453/1 "2023-11-03T11:35:19Z")

</div>

```auto

```

Select  
Case  
when (CAST (getdate() as time )) \>='06:30:00.000' and (CAST (getdate() as time )) \<='14:30:00.000' THEN 'Shift1'  
when (CAST (getdate() as time )) \>='14:30:00.000' and (CAST (getdate() as time )) \<='22:30:00.000' THEN 'Shift2'  
Else 'Shift3' END as Shift

```auto

```

In this query im getting an result as  
shift 1 starts at 6:30AM and ends at14:25 PM in shift 1  
shift 2 starts at 2:30 PM and end at 10:25PM in shift 2

but i expect the result as  
shift 1 starts at 6:30AM and ends at14: 30 PM in shift 1  
shift 2 starts at 2:30 PM and end at 10:30PM in shift 2

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [November 3, 2023, 11:43am UTC](https://forum.inductiveautomation.com/t/suggestion-required-in-datetime-configuration-in-named-queries/81453/2 "2023-11-03T11:43:39Z")

</div>

- Any given row timestamp can only be assigned to a single shift. In a CASE statement like shown, the first match wins.

- You are using `<=` for your shift ending times. That is likely incorrect, as a row exactly on a boundary matches two conditions. You should use half-open intervals.

Side note: This query will run very, very slowly when you get a bunch of rows, because you are converting your timestamps to strings to do your comparisons. This prevents efficient use of an index on your timestamps. Instead, convert your constant strings, **with dates** , to true timestamps, and compare **as timestamps**.

---

<div class="post-metadata">

**Author:** ![Sudhanthiralakshmi\_M](https://avatars.discourse-cdn.com/v4/letter/s/e495f1/32.png) [@Sudhanthiralakshmi\_M](https://forum.inductiveautomation.com/u/Sudhanthiralakshmi_M)\
**Post date:** [November 3, 2023, 12:09pm UTC](https://forum.inductiveautomation.com/t/suggestion-required-in-datetime-configuration-in-named-queries/81453/3 "2023-11-03T12:09:48Z")

</div>

what exact modification i need to do in my query

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [November 3, 2023, 12:12pm UTC](https://forum.inductiveautomation.com/t/suggestion-required-in-datetime-configuration-in-named-queries/81453/4 "2023-11-03T12:12:14Z")

</div>

Ah, wait, you **aren't** comparing the row timestamps, you are using the date/time of **execution** to decide what shift it is, without regard to the data. ):

---

<div class="post-metadata">

**Author:** ![Sudhanthiralakshmi\_M](https://avatars.discourse-cdn.com/v4/letter/s/e495f1/32.png) [@Sudhanthiralakshmi\_M](https://forum.inductiveautomation.com/u/Sudhanthiralakshmi_M)\
**Post date:** [November 3, 2023, 12:24pm UTC](https://forum.inductiveautomation.com/t/suggestion-required-in-datetime-configuration-in-named-queries/81453/5 "2023-11-03T12:24:49Z")

</div>

I understood, But i dont know what exactly need to change in the given Query. you can give suggestion to get the data points in all shifts

Below is the Query I am using it for shiftwise split what possible modifications i can do in my query to get a shiftwise energy  
select events,total from(  
SELECT top(3) cast(Mod\_Date as date) as time  
,CASE WHEN (Shift='Shift1' ) THEN  
(max(convert(float,KWH))-min(convert(float,KWH)))  
END AS Shift1  
,CASE WHEN (Shift='Shift2') THEN  
(max(convert(float,KWH))-min(convert(float,KWH)))  
END AS Shift2  
,CASE WHEN (Shift='Shift3') THEN  
(max(convert(float,KWH))-min(convert(float,KWH)))  
END AS Shift3  
from Main where Mod\_Date between :st and :sp and BG=:bg and Plant=:pl and Line=:ln and Machine =:ma and KWH !='0'  
GROUP BY cast(Mod\_Date as date),Shift  
order by time asc  
) as p  
UNPIVOT  
(total for events IN  
(Shift1,Shift2,Shift3 )  
)AS unpvt;