# Tag Historical data - frequency and duration of tag values

**URL:** <https://forum.inductiveautomation.com/t/tag-historical-data-frequency-and-duration-of-tag-values/65667>\
**Category:** Ignition\
**Tags:** vision, scripting\
**Created:** [October 25, 2022, 6:02pm UTC](https://forum.inductiveautomation.com/t/tag-historical-data-frequency-and-duration-of-tag-values/65667 "2022-10-25T18:02:53Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![Zach\_Stewart](https://avatars.discourse-cdn.com/v4/letter/z/90ced4/32.png) [@Zach\_Stewart](https://forum.inductiveautomation.com/u/Zach_Stewart)\
**Post date:** [October 25, 2022, 6:02pm UTC](https://forum.inductiveautomation.com/t/tag-historical-data-frequency-and-duration-of-tag-values/65667/1 "2022-10-25T18:02:54Z")

</div>

Here is the dilemma...

1. I am pulling data from PLC for a state code for example...1002 = RN-High...3038 = DN - Servo Machine Open Selector...etc
2. I created an expression tag using switch statements to give the description. ie. tag from PLC is 1002, switch in expression tag converts it to "RN-High". This tag is set up for historical through MySQL type database.
3. I am able to show real time per shift on what the different state codes are.
4. What I want to do is take that dataset and create a new one that shows in a table:

Machine State | Frequency | Total Duration

Any help with this would be MASSIVELY appreciated. I've been pretty stumped on this.

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/f/ff1c735d0fc9412e77ebf1040a1c26a5c95020b6.png)

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [October 25, 2022, 6:28pm UTC](https://forum.inductiveautomation.com/t/tag-historical-data-frequency-and-duration-of-tag-values/65667/2 "2022-10-25T18:28:31Z")

</div>

1. Do we assume that when RN-High turns on that it stays on until the next timestamp? (In that case you want to get the Δt between that timestamp and the next one and add that to the accumulated time for RN-High.)
2. What happens for the last entry? How do you calculate the time that was on for?
3. "_I am able to show real time per shift ..._" That looks more like historical time per shift for each state.

---

<div class="post-metadata">

**Author:** ![Zach\_Stewart](https://avatars.discourse-cdn.com/v4/letter/z/90ced4/32.png) [@Zach\_Stewart](https://forum.inductiveautomation.com/u/Zach_Stewart)\
**Post date:** [October 25, 2022, 6:39pm UTC](https://forum.inductiveautomation.com/t/tag-historical-data-frequency-and-duration-of-tag-values/65667/3 "2022-10-25T18:39:39Z")

</div>

1. Yes we do assume that. Correct. I am not sure where to start to do that.
2. I suppose setting up limits to where it assumes that. I have those set up as properties based on time of day.

```auto
if(timeBetween(now(),{[default]Ignition Tags/AM Shift Start Time},{[default]Ignition Tags/AM Shift End Time}),

{[default]Ignition Tags/AM Shift Start Time},

{[default]Ignition Tags/PM Shift Start Time})

```

1. Correct, I suppose I worded it oddly.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [October 25, 2022, 6:48pm UTC](https://forum.inductiveautomation.com/t/tag-historical-data-frequency-and-duration-of-tag-values/65667/4 "2022-10-25T18:48:07Z")

</div>

Show your SQL query for the database. It may be possible to do it all in the query.

* * *

Tip: use the \</\> code formatting button when posting code. It will indent properly (important for Python) and give syntax highlighting.  
Other tip: don't leave out sentence subjects and verbs such as "I am" in your point 1 on the original post. The sentences are incomplete and have to be read more than once to try to get the meaning. Go for clarity (for an international audience) rather than colloquial.

---

<div class="post-metadata">

**Author:** ![Zach\_Stewart](https://avatars.discourse-cdn.com/v4/letter/z/90ced4/32.png) [@Zach\_Stewart](https://forum.inductiveautomation.com/u/Zach_Stewart)\
**Post date:** [October 25, 2022, 7:00pm UTC](https://forum.inductiveautomation.com/t/tag-historical-data-frequency-and-duration-of-tag-values/65667/5 "2022-10-25T19:00:34Z")

</div>

Thank you for the tips. I will make those edits now.

I actually do not have a query made for the database. I am just using the history feature of the ignition tag. I do not believe that this is the most effective way to accomplish this, but I have found success doing it this way.

```auto
switch({[default]POU102/Core/Machine_State_Code},

1002, 1013, 2100, 2200, 3000, 3010, 3011, 3012, 3013, 3014, 3015, 3016, 3017, 3018, 3019, 3020, 3021, 3022, 3023, 3024, 3025, 3026, 3027, 3028, 3029, 3030, 3031, 3032, 3033, 3034, 3035, 3036, 3037, 3038, 3039, 3040, 3041, 3042, 3043, 3044, 3045, 3046, 3047, 3048, 3049, 3050, 3051, 3052, 3053, 3054, 3055, 3056, 3057, 3058, 3059, 3060, 3061, 3062, 3063, 3064, 3065, 3066, 3067, 3068, 3069, 3070, 3071, 3072, 3073, 3074, 3075, 3076, 3077, 3078, 3079, 3080, 3081, 3082, 3083, 3084, 3085, 3086, 3087, 3088, 3089, 3090, 3091, 3092, 3093, 3094, 3095, 3096, 3097, 3098, 3099, 3100, 3101, 3102, 3103, 3104, 3105, 3106, 3107, 3108, 3109, 3110, 3111, 3112, 3113, 3114, 3115, 3116, 3117, 3118, 3119, 3120, 3121, 3122, 3123, 3124, 3125, 3126, 3127, 3128, 3129, 3130, 3131, 3132, 3133, 3134, 3135, 3136, 3137, 3138, 3139, 3140, 3141, 3142, 3143, 3144, 3145, 3146, 3147, 3148, 3149, 3150, 3151, 3152, 3153, 3154, 3155, 3156, 3157, 3158, 3159, 3160, 3161, 3162, 3163, 3164, 3165, 3166, 3167, 3168, 3169, 3170, 3171, 3172, 3173, 3174, 3175, 3176, 3177, 3178, 3179, 3180, 3181, 3182, 3183, 3184, 3185, 3186, 3187, 3188, 3189, 3190, 3191, 3192, 3193, 3194, 3195, 3196, 3197, 3198, 3199, 3200, 3201, 3202, 3203, 3204, 3205, 3206, 3207, 3208, 3209, 3210, 3211, 3212, 3213, 3214, 3215, 3216, 3217, 3218, 3219, 3220, 3221, 3222, 3223, 3224, 3225, 3226, 3227, 3228, 3229, 3230, 3231, 3232, 3233, 3234, 3235, 3236, 3237, 3238, 3239, 3240, 3241, 3242, 3243, 3244, 3245, 3246, 3247, 3248, 3249, 3250, 3251, 3252, 3253, 3254, 3255, 3256, 3257, 3258, 3259, 3260, 3261, 3262, 3263, 3264, 3265, 3266, 3267, 3268, 3269, 3270, 3271, 3272, 3273, 3274, 3275, 3276, 3277, 3278, 3279, 3280, 3281, 3282, 3283, 3284, 3285, 3286, 3287, 3288, 3289, 3290, 3291, 3292, 3293, 3294, 3295, 3296, 3297, 3298, 3299, 3300, 3301, 3302, 3303, 3304, 3305, 3306, 3307, 3308, 3309, 3310, 3311, 3312, 3313, 3314, 3315, 3316, 3317, 3318, 3319, 3320, 3321, 3322, 3323, 3324, 3325, 3326, 3327, 3328, 3329, 3330, 3331, 3332, 3333, 3334, 3335, 3336, 3337, 3338, 3339, 3340, 3341, 3342, 3343, 3344, 3345, 3346, 3347, 3348, 3349, 3350, 3351, 3352, 3353, 3354, 3355, 3356, 3357, 3358, 3359, 3360, 3361, 3362, 3363, 3364, 3365, 3366, 3367, 3368, 3369, 3370, 3371, 3372, 3373, 3374, 3375, 3376, 3377, 3378, 3379, 3380, 3381, 3382, 3383, 3384, 3385, 3386, 3387, 3388, 3389, 3390, 3391, 3392, 3393, 3394, 3395, 3396, 3397, 3398, 3399, 3400, 3401, 3402, 3403, 3404, 3405, 3406, 3407, 3408, 3409, 3410, 3411, 3412, 3413, 3414, 3415, 3416, 3417, 3418, 3419, 3420, 3421, 3422, 3423, 3424, 3425, 3426, 3427, 3428, 3429, 3430, 3431, 3432, 3433, 3434, 3435, 3436, 3437, 3438, 3439, 3440, 3441, 3442, 3443, 3444, 3445, 3446, 3447, 3448, 3449, 3450, 3451, 3452, 3453, 3454, 3455, 3456, 3457, 3458, 3459, 3460, 3461, 3462, 3463, 3464, 3465, 3466, 3467, 3468, 3469, 3470, 3471, 3472, 3473, 3474, 3475, 3476, 3477, 3478, 3479, 3480, 3481, 3482, 3483, 3484, 3485, 3486, 3487, 3488, 3489, 3490, 3491, 3492, 3493, 3494, 3495, 3496, 3497, 3498, 3499, 3500, 3501, 3502, 3503, 3504, 3505, 3506, 3507, 3508, 3509, 3510, 3511, 3512, 3513, 3514, 3515, 3516, 3517, 3518, 3519, 3520, 3521, 3522, 3523, 3524, 3525, 3526, 3527, 3528, 3529, 3530, 3531, 3532, 3533, 3534, 3535, 3536, 3537, 3538, 3539, 3540, 3541, 3542, 3543, 3544, 3545, 3546, 3547, 3548, 3549, 3550, 3551, 3552, 3553, 3554, 3555, 3556, 3557, 3558, 3559, 3560, 3561, 3562, 3563, 3564, 3565, 3566, 3567, 3568, 3569, 3570, 3571, 3572, 3573, 3574, 3575, 3576, 3577, 3578, 3579, 3580, 3581, 3582, 3583, 3584, 3585, 3586, 3587, 3588, 3589, 3590, 3591, 3592, 3593, 3594, 3595, 3596, 3597, 3598, 3599, 3600, 3601, 3602, 3603, 3604, 3605, 3606, 3607, 3608, 3609, 3610, 3611, 3612, 3613, 3614, 3615, 3616, 3617, 3618, 3619, 3620, 3621, 3622, 3623, 3624, 3625, 3626, 3627, 3628, 3629, 3630, 3631, 3632, 3633, 3634, 3635, 3636, 3637, 3638, 3639, 3640, 3641, 3642, 3643, 3644, 3645, 3646, 3647, 3648, 3649, 3650, 3651, 3652, 3653, 3654, 3655, 3656, 3657, 3658, 3659, 3660, 3661, 3662, 3663, 3664, 3665, 3666, 3667, 3668, 3669, 3670, 3671, 3672, 3673, 3674, 3675, 3676, 3677, 3678, 3679, 3680, 3681, 3682, 3683, 3684, 3685, 3686, 3687, 3688, 3689, 3690, 3691, 3692, 3693, 3694, 3695, 3696, 3697, 3698, 3699, 3700, 3701, 3702, 3703, 3704, 3705, 3706, 3707, 

"RN-High", 
"RN-Manual", 
"ST-Infeed", 
"ST-Discharge", 
"DN-Miscellaneous", 
"DN-Power Off", 
"DN-Stop Pushbutton", 
"DN-Hold Pushbutton", 
"DN-Motion Group Fault", 
"DN-Remote Stop Command", 
"DN-Remote Abort Command", 
"DN-Remote Suspend Command - Blocked", 
"DN-Remote Suspend Command - Starved", 
"DN-Remote Hold Command", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"DN-Emergency Stop PB Screen", 
"DN-Emergency Stop PB Frame", 
"DN-Emergency Stop 3", 
"DN-Emergency Stop 4", 
"DN-External Emergency Stop", 
"DN-Top Reel Brake Guarding Opened", 
"DN-Bottom Reel Brake Guarding Opened", 
"DN-UPS Battery Low", 
"DN-USS Battery Replacement", 
"DN-Machine Guarding 1 Open", 
"DN-Machine Guarding 2 Open", 
"DN-Machine Guarding 3 Open", 
"DN-Machine Guarding 4 Open", 
"DN-Machine Guarding 5 Open", 
"DN-Machine Guarding 6 Open", 
"DN-Machine Guarding 25 Open", 
"DN-Machine Guarding 26 Open", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"DN-Jaws Correction on Limits", 
"DN-Photocell Correction on Limits", 
"DN-Machine Film Servo Not Ready", 
"DN-Main Motor Not Ready", 
"DN-Servo Machine Open Selector", 
"DN-Film Jammed on Scissors", 
"DN-Machine Film Photocell", 
"DN-Machine Film Servo Open", 
"DN-Machine Film Servo Not Ready", 
"DN-Vacuum Pump 1 Overload", 
"DN-Vacuum Pump 2 Overload", 
"DN-Pouch 1 Not Open", 
"DN-Pouch 2 Not Open", 
"DN-Grease Level Alarm", 
"DN-Grease Pressure Alarm", 
"DN-Top Jaws", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"Spare", 
"DN-Front Bottom 1 Temperature Sensor Error", 
"DN-Rear Bottom 1 Temperature Sensor Error", 
"DN-Front Bottom 2 Temperature Sensor Error", 
"DN-Rear Bottom 2 Temperature Sensor Error", 
"DN-Vertical Rear 1 Temperature Sensor Error", 
"DN-Vertical Front 1 Temperature Sensor Error", 
"DN-Vertical Rear 2 Temperature Sensor Error", 
"DN-Vertical Front 2 Temperature Sensor Error", 
"DN-Vertical Rear 3 Temperature Sensor Error", 
"DN-Vertical Front 3 Temperature Sensor Error", 
"DN-Vertical Rear 4 Temperature Sensor Error", 
"DN-Vertical Front 4 Temperature Sensor Error", 
"DN-Vertical 9 Temperature Sensor Error", 
"DN-Vertical 10 Temperature Sensor Error", 
"DN-Vertical 11 Temperature Sensor Error", 
"DN-Vertical 12 Temperature Sensor Error", 
"DN-Vertical 13 Temperature Sensor Error", 
"DN-Vertical 14 Temperature Sensor Error", 
"DN-Vertical 15 Temperature Sensor Error", 
"DN-Vertical 16 Temperature Sensor Error", 
"DN-Front Top 1 Temperature Sensor Error", 
"DN-Rear Top 1 Temperature Sensor Error", 
"DN-Rear Top 2 Temperature Sensor Error", 
"DN-Front Top 2 Temperature Sensor Error", 
"DN-Front Top 3 Temperature Sensor Error", 
"DN-Rear Top 3 Temperature Sensor Error", 
"DN-Front Top 4 Temperature Sensor Error", 
"DN-Rear Top 4 Temperature Sensor Error", 
"DN-Front Top 9 Temperature Sensor Error", 
"DN-Rear Top 9 Temperature Sensor Error", 
"DN-Front Top 10 Temperature Sensor Error", 
"DN-Rear Top 10 Temperature Sensor Error", 
"DN-Front Top 11 Temperature Sensor Error", 
"DN-Rear Top 11 Temperature Sensor Error", 
"DN-Front Top 12 Temperature Sensor Error", 
"DN-Rear Top 12 Temperature Sensor Error", 
"DN-Cap Front Pointer 1 Temperature Sensor Error", 
"DN-Cap Rear Pointer 1 Temperature Sensor Error", 
"DN-Cap Front Pointer 6 Temperature Sensor Error", 
"DN-Cap Rear Pointer 6 Temperature Sensor Error", 
"DN-Cap Front Pointer 3 Temperature Sensor Error", 
"DN-Cap Rear Pointer 3 Temperature Sensor Error", 
"DN-Cap Front Pointer 4 Temperature Sensor Error", 
"DN-Cap Rear Pointer 4 Temperature Sensor Error", 
"DN-Cap Front Pointer 2 Temperature Sensor Error", 
"DN-Cap Rear Pointer 2 Temperature Sensor Error", 
"DN-Cap Front Pointer 1 Temperature Sensor Error", 
"DN-Cap Rear Pointer 1 Temperature Sensor Error", 
"DN-Front Bottom 1 High Temperature", 
"DN-Rear Bottom 1 High Temperature", 
"DN-Front Bottom 2 High Temperature", 
"DN-Rear Bottom 2 High Tempeature", 
"DN-Vertical Rear 1 High Tempeature", 
"DN-Vertical Front 1 High Tempeature", 
"DN-Vertical Rear 2 High Tempeature", 
"DN-Vertical Front 2 High Tempeature", 
"DN-Vertical Rear 3 High Tempeature", 
"DN-Vertical Front 3 High Tempeature", 
"DN-Vertical Rear 4 High Tempeature", 
"DN-Vertical Front 4 High Tempeature", 
"DN-Vertical 9 High Tempeature", 
"DN-Vertical 10 High Tempeature", 
"DN-Vertical 11 High Tempeature", 
"DN-Vertical 12 High Tempeature", 
"DN-Vertical 13 High Temperature", 
"DN-Vertical 14 High Temperature", 
"DN-Vertical 15 High Temperature", 
"DN-Vertical 16 High Temperature", 
"DN-Front Top 1 High Temperature", 
"DN-Rear Top 1 High Temperature", 
"DN-Rear Top 2 High Temperature", 
"DN-Front Top 2 High Temperature", 
"DN-Front Top 3 High Temperature", 
"DN-Rear Top 3 High Temperature", 
"DN-Front Top 4 High Temperature", 
"DN-Rear Top 4 High Temperature", 
"DN-Front Top 9 High Temperature", 
"DN-Rear Top 9 High Temperature", 
"DN-Front Top 10 High Temperature", 
"DN-Rear Top 10 High Temperature", 
"DN-Front Top 11 High Temperature", 
"DN-Rear Top 11 High Temperature", 
"DN-Front Top 12 High Temperature", 
"DN-Rear Top 12 High Temperature", 
"DN-Cap Front Pointer 1 High Temperature", 
"DN-Cap Rear Pointer 1 High Temperature", 
"DN-Cap Front Pointer 6 High Temperature", 
"DN-Cap Rear Pointer 6 High Temperature", 
"DN-Cap Front Pointer 3 High Temperature", 
"DN-Cap Rear Pointer 3 High Temperature", 
"DN-Cap Front Pointer 4 High Temperature", 
"DN-Cap Rear Pointer 4 High Temperature", 
"DN-Cap Front Pointer 1 High Temperature", 
"DN-Cap Rear Pointer 1 High Temperature", 
"DN-Cap Front Pointer 2 High Temperature", 
"DN-Cap Rear Pointer 2 High Temperature", 
"DN-Front Bottom 1 Low Temperature", 
"DN-Rear Bottom 1 Low Temperature", 
"DN-Front Bottom 2 Low Temperature", 
"DN-Rear Bottom 2 Low Temperature", 
"DN-Vertical Rear 1 Low Temperature", 
"DN-Vertical Front 1 Low Temperature", 
"DN-Ertical Rear 2 Low Temperature", 
"DN-Vertical Front 2 Low Temperature", 
"DN-Vertical Rear 3 Low Temperature", 
"DN-Vertical Front 3 Low Temperature", 
"DN-Vertical Rear 4 Low Temperature", 
"DN-Vertical Front 4 Low Temperature", 
"DN-Vertical 9 Low Temperature", 
"DN-Vertical 10 Low Temperature", 
"DN-Vertical 11 Low Temperature", 
"DN-Vertical 12 Low Temperature", 
"DN-Vertical 13 Low Temperature", 
"DN-Vertical 14 Low Temperature", 
"DN-Vertical 15 Low Temperature", 
"DN-Vertical 16 Low Temperature", 
"DN-Front Top 1 Low Temperature", 
"DN-Rear Top 1 Low Temperature", 
"DN-Rear Top 2 Low Temperature", 
"DN-Front Top 2 Low Temperature", 
"DN-Front Top 3 Low Temperature", 
"DN-Rear Top 3 Low Temperature", 
"DN-Front Top 4 Low Temperature", 
"DN-Rear Top 4 Low Temperature", 
"DN-Front Top 9 Low Temperature", 
"DN-Rear Top 9 Low Temperature", 
"DN-Front Top 10 Low Temperature", 
"DN-Rear Top 10 Low Temperature", 
"DN-Front Top 11 Low Temperature", 
"DN-Rear Top 11 Low Temperature", 
"DN-Front Top 12 Low Temperature", 
"DN-Rear Top 12 Low Temperature", 
"DN-Cap Front Pointer 1 Low Temperature", 
"DN-Cap Rear Pointer 1 Low Temperature", 
"DN-Cap Front Pointer 6 Low Temperature", 
"DN-Cap Rear Pointer 6 Low Temperature", 
"DN-Cap Front Pointer 3 Low Temperature", 
"DN-Cap Rear Pointer 3 Low Temperature", 
"DN-Cap Front Pointer 4 Low Temperature", 
"DN-Cap Rear Pointer 4 Low Temperature", 
"DN-Cap Front Pointer 1 Low Temperature", 
"DN-Cap Rear Pointer 1 Low Temperature", 
"DN-Cap Front Pointer 2 Low Temperature", 
"DN-Cap Rear Pointer 2 Low Temperature", 
"DN-Front Bottom 1 Heater Burned", 
"DN-Rear Bottom 1 Heater Burned", 
"DN-Front Bottom 2 Heater Burned", 
"DN-Rear Bottom 2 Heater Burned", 
"DN-Vertical 1 Heater Burned", 
"DN-Vertical 2 Heater Burned", 
"DN-Vertical 3 Heater Burned", 
"DN-Vertical 4 Heater Burned", 
"DN-Vertical 5 Heater Burned", 
"DN-Vertical 6 Heater Burned", 
"DN-Vertical 7 Heater Burned", 
"DN-Vertical 8 Heater Burned", 
"DN-Vertical 9 Heater Burned", 
"DN-Vertical 10 Heater Burned", 
"DN-Vertical 11 Heater Burned", 
"DN-Vertical 12 Heater Burned", 
"DN-Vertical 13 Heater Burned", 
"DN-Vertical 14 Heater Burned", 
"DN-Vertical 15 Heater Burned", 
"DN-Vertical 16 Heater Burned", 
"DN-Rear Top 1 Heater Burned", 
"DN-Front Top 1 Heater Burned", 
"DN-Rear Top 2 Heater Burned", 
"DN-Front Top 2 Heater Burned", 
"DN-Front Top 3 Heater Burned", 
"DN-Rear Top 3 Heater Burned", 
"DN-Front Top 4 Heater Burned", 
"DN-Rear Top 4 Heater Burned", 
"DN-Front Top 9 Heater Burned", 
"DN-Rear Top 9 Heater Burned", 
"DN-Front Top 10 Heater Burned", 
"DN-Rear Top 10 Heater Burned", 
"DN-Front Top 11 Heater Burned", 
"DN-Rear Top 11 Heater Burned", 
"DN-Front Top 12 Heater Burned", 
"DN-Rear Top 12 Heater Burned", 
"DN-Cap Front Pointer 1 Heater Burned", 
"DN-Cap Rear Pointer 1 Heater Burned", 
"DN-Cap Front Pointer 6 Heater Burned", 
"DN-Cap Rear Pointer 6 Heater Burned", 
"DN-Film Front Pointer Heater Burned", 
"DN-Film Rear Pointer Heater Burned", 
"DN-Cap Front Pointer 4 Heater Burned", 
"DN-Cap Rear Pointer 4 Heater Burned", 
"DN-Cap Front Pointer 1 Heater Burned", 
"DN-Cap Rear Pointer 1 Heater Burned", 
"DN-Cap Front Pointer 2 Heater Burned", 
"DN-Cap Rear Pointer 2 Heater Burned", 
"DN-Front Bottom 1 Temperature Sensor Error", 
"DN-Rear Bottom 1 Temperature Sensor Error", 
"DN-Front Bottom 2 Temperature Sensor Error", 
"DN-Rear Bottom 2 Temperature Sensor Error", 
"DN-Vertical 1 Temperature Sensor Error", 
"DN-Vertical 2 Temperature Sensor Error", 
"DN-Vertical 3 Temperature Sensor Error", 
"DN-Vertical 4 Temperature Sensor Error", 
"DN-Vertical 5 Temperature Sensor Error", 
"DN-Vertical 6 Temperature Sensor Error", 
"DN-Vertical 7 Temperature Sensor Error", 
"DN-Vertical 8 Temperature Sensor Error", 
"DN-Vertical 9 Temperature Sensor Error", 
"DN-Vertical 10 Temperature Sensor Error", 
"DN-Vertical 11 Temperature Sensor Error", 
"DN-Vertical 12 Temperature Sensor Error", 
"DN-Vertical 13 Temperature Sensor Error", 
"DN-Vertical 14 Temperature Sensor Error", 
"DN-Vertical 15 Temperature Sensor Error", 
"DN-Vertical 16 Temperature Sensor Error", 
"DN-Front Top 1 Temperature Sensor Error", 
"DN-Rear Top 1 Temperature Sensor Error", 
"DN-Front Top 2 Temperature Sensor Error", 
"DN-Rear Top 2 Temperature Sensor Error", 
"DN-Front Top 3 Temperature Sensor Error", 
"DN-Rear Top 3 Temperature Sensor Error", 
"DN-Front Top 4 Temperature Sensor Error", 
"DN-Rear Top 4 Temperature Sensor Error", 
"DN-Front Top 9 Temperature Sensor Error", 
"DN-Rear Top 9 Temperature Sensor Error", 
"DN-Front Top 10 Temperature Sensor Error", 
"DN-Rear Top 10 Temperature Sensor Error", 
"DN-Front Top 11 Temperature Sensor Error", 
"DN-Rear Top 11 Temperature Sensor Error", 
"DN-Front Top 12 Temperature Sensor Error", 
"DN-Rear Top 12 Temperature Sensor Error", 
"DN-Cap Front Pointer 5 Temperature Sensor Error", 
"DN-Cap Rear Pointer 5 Temperature Sensor Error", 
"DN-Cap Front Pointer 6 Temperature Sensor Error", 
"DN-Cap Rear Pointer 6 Temperature Sensor Error", 
"DN-Cap Front Pointer 3 Temperature Sensor Error", 
"DN-Cap Rear Pointer 3 Temperature Sensor Error", 
"DN-Cap Front Pointer 4 Temperature Sensor Error", 
"DN-Cap Rear Pointer 4 Temperature Sensor Error", 
"DN-Cap Front Pointer 2 Temperature Sensor Error", 
"DN-Cap Rear Pointer 2 Temperature Sensor Error", 
"DN-Cap Front Pointer 1 Temperature Sensor Error", 
"DN-Cap Rear Pointer 1 Temperature Sensor Error", 
"DN-Front Bottom 1 High Temperature", 
"DN-Rear Bottom 1 High Temperature", 
"DN-Front Bottom 2 High Temperature", 
"DN-Rear Bottom 2 High Temperature", 
"DN-Vertical 1 High Temperature", 
"DN-Vertical 2 High Temperature", 
"DN-Vertical 3 High Temperature", 
"DN-Vertical 4 High Temperature", 
"DN-Vertical 5 High Temperature", 
"DN-Vertical 6 High Temperature", 
"DN-Vertical 7 High Temperature", 
"DN-Vertical 8 High Temperature", 
"DN-Vertical 9 High Temperature", 
"DN-Vertical 10 High Temperature", 
"DN-Vertical 11 High Temperature", 
"DN-Vertical 12 High Temperature", 
"DN-Vertical 13 High Temperature", 
"DN-Vertical 14 High Temperature", 
"DN-Vertical 15 High Temperature", 
"DN-Vertical 16 High Temperature", 
"DN-Front Top 1 High Temperature", 
"DN-Rear Top 1 High Temperature", 
"DN-Front Top 2 High Temperature", 
"DN-Rear Top 2 High Temperature", 
"DN-Front Top 3 High Temperature", 
"DN-Rear Top 3 High Temperature", 
"DN-Front Top 4 High Temperature", 
"DN-Rear Top 4 High Temperature", 
"DN-Front Top 9 High Temperature", 
"DN-Rear Top 9 High Temperature", 
"DN-Front Top 10 High Temperature", 
"DN-Rear Top 10 High Temperature", 
"DN-Front Top 11 High Temperature", 
"DN-Rear Top 11 High Temperature", 
"DN-Front Top 12 High Temperature", 
"DN-Rear Top 12 High Temperature", 
"DN-Cap Front Pointer 5 High Temperature", 
"DN-Cap Rear Pointer 5 High Temperature", 
"DN-Cap Front Pointer 6 High Temperature", 
"DN-Cap Rear Pointer 6 High Temperature", 
"DN-Cap Front Pointer 3 High Temperature", 
"DN-Cap Rear Pointer 3 High Temperature", 
"DN-Cap Front Pointer 4 High Temperature", 
"DN-Cap Rear Pointer 4 High Temperature", 
"DN-Cap Front Pointer 1 High Temperature", 
"DN-Cap Rear Pointer 1 High Temperature", 
"DN-Cap Front Pointer 2 High Temperature", 
"DN-Cap Rear Pointer 2 High Temperature", 
"DN-Front Bottom 1 Low Temperature", 
"DN-Rear Bottom 1 Low Temperature", 
"DN-Front Bottom 2 Low Temperature", 
"DN-Rear Bottom 2 Low Temperature", 
"DN-Vertical 1 Low Temperature", 
"DN-Vertical 2 Low Temperature", 
"DN-Vertical 3 Low Temperature", 
"DN-Vertical 4 Low Temperature", 
"DN-Vertical 5 Low Temperature", 
"DN-Vertical 6 Low Temperature", 
"DN-Vertical 7 Low Temperature", 
"DN-Vertical 8 Low Temperature", 
"DN-Vertical 9 Low Temperature", 
"DN-Vertical 10 Low Temperature", 
"DN-Vertical 11 Low Temperature", 
"DN-Vertical 12 Low Temperature", 
"DN-Vertical 13 Low Temperature", 
"DN-Vertical 14 Low Temperature", 
"DN-Vertical 15 Low Temperature", 
"DN-Vertical 16 Low Temperature", 
"DN-Front Top 1 Low Temperature", 
"DN-Rear Top 1 Low Temperature", 
"DN-Front Top 2 Low Temperature", 
"DN-Rear Top 2 Low Temperature", 
"DN-Front Top 3 Low Temperature", 
"DN-Rear Top 3 Low Temperature", 
"DN-Front Top 4 Low Temperature", 
"DN-Rear Top 4 Low Temperature", 
"DN-Front Top 9 Low Temperature", 
"DN-Rear Top 9 Low Temperature", 
"DN-Front Top 10 Low Temperature", 
"DN-Rear Top 10 Low Temperature", 
"DN-Front Top 11 Low Temperature", 
"DN-Rear Top 11 Low Temperature", 
"DN-Front Top 12 Low Temperature", 
"DN-Rear Top 12 Low Temperature", 
"DN-Cap Front Pointer 5 Low Temperature", 
"DN-Cap Rear Pointer 5 Low Temperature", 
"DN-Cap Front Pointer 6 Low Temperature", 
"DN-Cap Rear Pointer 6 Low Temperature", 
"DN-Cap Front Pointer 3 Low Temperature", 
"DN-Cap Rear Pointer 3 Low Temperature", 
"DN-Cap Front Pointer 4 Low Temperature", 
"DN-Cap Rear Pointer 4 Low Temperature", 
"DN-Cap Front Pointer 1 Low Temperature", 
"DN-Cap Rear Pointer 1 Low Temperature", 
"DN-Cap Front Pointer 2 Low Temperature", 
"DN-Cap Rear Pointer 2 Low Temperature", 
"DN-Front Bottom 1 Heater Burned", 
"DN-Rear Bottom 1 Heater Burned", 
"DN-Front Bottom 2 Heater Burned", 
"DN-Rear Bottom 2 Heater Burned", 
"DN-Vertical 1 Heater Burned", 
"DN-Vertical 2 Heater Burned", 
"DN-Vertical 3 Heater Burned", 
"DN-Vertical 4 Heater Burned", 
"DN-Vertical 5 Heater Burned", 
"DN-Vertical 6 Heater Burned", 
"DN-Vertical 7 Heater Burned", 
"DN-Vertical 8 Heater Burned", 
"DN-Vertical 9 Heater Burned", 
"DN-Vertical 10 Heater Burned", 
"DN-Vertical 11 Heater Burned", 
"DN-Vertical 12 Heater Burned", 
"DN-Vertical 13 Heater Burned", 
"DN-Vertical 14 Heater Burned", 
"DN-Vertical 15 Heater Burned", 
"DN-Vertical 16 Heater Burned", 
"DN-Front Top 1 Heater Burned", 
"DN-Rear Top 1 Heater Burned", 
"DN-Front Top 2 Heater Burned", 
"DN-Rear Top 2 Heater Burned", 
"DN-Front Top 3 Heater Burned", 
"DN-Rear Top 3 Heater Burned", 
"DN-Front Top 4 Heater Burned", 
"DN-Rear Top 4 Heater Burned", 
"DN-Front Top 9 Heater Burned", 
"DN-Rear Top 9 Heater Burned", 
"DN-Front Top 10 Heater Burned", 
"DN-Rear Top 10 Heater Burned", 
"DN-Front Top 11 Heater Burned", 
"DN-Rear Top 11 Heater Burned", 
"DN-Front Top 12 Heater Burned", 
"DN-Rear Top 12 Heater Burned", 
"DN-Cap Front Pointer 5 Heater Burned", 
"DN-Cap Rear Pointer 5 Heater Burned", 
"DN-Cap Front Pointer 6 Heater Burned", 
"DN-Cap Rear Pointer 6 Heater Burned", 
"DN-Film Front Pointer Heater Burned", 
"DN-Film Rear Pointer Heater Burned", 
"DN-Cap Front Pointer 4 Heater Burned", 
"DN-Cap Rear Pointer 4 Heater Burned", 
"DN-Cap Front Pointer 1 Heater Burned", 
"DN-Cap Rear Pointer 1 Heater Burned", 
"DN-Cap Front Pointer 2 Heater Burned", 
"DN-Cap Rear Pointer 2 Heater Burned", 
"DN-Encoder Not Ready", 
"DN-Temperature Controller Communication Fault ", 
"DN-Temperature Controller Communication Fault", 
"DN-Resistance Transformer Isolation Alarm", 
"DN-Resistance Transformer Isolation Alarm", 
"DN-Resistance Transformer Isolation Alarm", 
"DN-Spare", 
"DN-Low General Air Pressure", 
"DN-External Stop", 
"DN-Spare", 
"DN-Spare", 
"DN-Remote Io Communication Fault", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Servo Comm. Lost", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-24vdc Power Supply Error", 
"DN-Laminate Unwinder Index Servo Comm. Lost", 
"DN-Laminate Unwinder Index Servo Not Reay", 
"DN-Laminate Unwinder Index Photocell Error", 
"DN-Spare", 
"DN-Unwinder Index Opened", 
"DN-Laminate Unwinder Top Limit Alarm", 
"DN-Laminate Unwinder Bottom Limit Alarm", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Coder Not Ready", 
"DN-Spare", 
"DN-Bottom Splicer Open Alarm", 
"DN-Top Splicer Open Alarm", 
"DN-Spot Not Found On Splice Cycle ", 
"DN-Splicer Knife Go Left Timeout ", 
"DN-Splicer Knife Go Right Timeout ", 
"DN-Top Reel End - Bottom Splice Not Ready ", 
"DN-Bottom Reel End - Top Splice Not Ready ", 
"DN-No Film ", 
"DN-Spare", 
"DN-Laminate Unwinder Motor Roller Comm. Lost ", 
"DN-Laminate Unwinder Motor Roller Driver Not Ready", 
"DN-Laminate Unwinder Motor Roller Open", 
"DN-Unwinder Accumulator At Minimum ", 
"DN-Selector Abrir Freno Bobina Superior Activo ", 
"DN-Selector Abrir Freno Bobina Inferior Activo ", 
"DN-Film Unwinder Arm Selector Active", 
"DN-Brazo Desbobinador Al Máximo ", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Unwinder Top Reel Brake Cover Open", 
"DN-Unwinder Bottom Reel Brake Cover Open ", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Remote Io Communication Fault", 
"DN-Remote Pneumatic Module Communication Fault", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Jaws Corrections Out Of Limits", 
"DN-Spare", 
"DN-Photocell Corrections Out Of Limits ", 
"DN-Main Index - Manual Positioning Needed", 
"DN-Unwinder Index - Manual Positioning Needed", 
"DN-Spare", 
"DN-Machine Index Servo Comm. Lost ", 
"DN-Machine Index Servo Not Ready ", 
"DN-Film Accumulation In Scissors ", 
"DN-Machine Index Photocell Error ", 
"DN-Spare", 
"DN-Open Laminate Index Selector Active ", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Main Motor Comm. Lost", 
"DN-Main Motor Inverter Not Ready ", 
"DN-Vacuum Pump 1 Overload ", 
"DN-Vacuum Pump 2 Overload ", 
"DN-Spare", 
"DN-No Opened Top Pouch 1 Error", 
"DN-No Opened Top Pouch 2 Error", 
"DN-No Opened Top Pouch 3 Error", 
"DN-No Opened Top Pouch 4 Error", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Low Grease Level", 
"DN-Greaser Pressure Alarm", 
"DN-Scissors Safety Detector 1", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Nozzle Applicator 1 - Feeder Not Ready", 
"DN-Nozzle Applicator 2 - Feeder Not Ready", 
"DN-Nozzle Applicator 3 - Feeder Not Ready", 
"DN-Nozzle Applicator 4 - Feeder Not Ready", 
"DN-Spare", 
"DN-Spare", 
"DN-Nozzle Applicator 1 - Clapet In Bad Position", 
"DN-Nozzle Applicator 1 - Clapet In Bad Position", 
"DN-Nozzle Applicator 1 - Clapet In Bad Position", 
"DN-Nozzle Applicator 1 - Clapet In Bad Position ", 
"DN-Spare", 
"DN-Spare", 
"DN-Pouch 1 Without Nozzle ", 
"DN-Pouch 2 Without Nozzle ", 
"DN-Pouch 3 Without Nozzle ", 
"DN-Pouch 4 Without Nozzle ", 
"DN-Spare", 
"DN-Spare", 
"DN-Nozzle Applicator 1 - Drain Selector Active", 
"DN-Nozzle Applicator 2 - Drain Selector Active", 
"DN-Nozzle Applicator 3 - Drain Selector Active", 
"DN-Nozzle Applicator 4 - Drain Selector Active", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Feedr 1 Max. Time Load Caps", 
"DN-Feedr 3 Max. Time Load Caps", 
"DN-Feedr 2 Max. Time Load Caps", 
"DN-Feedr 4 Max. Time Load Caps", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Spare", 
"DN-Com. Fault Servo Cap Gripper 1", 
"DN-Servo Cap Gripper 1 Not Ready", 
"DN-Interlock Alarm Servo Cap Gripper 1", 
"DN-Com. Fault Servo Cap Gripper 2", 
"DN-Servo Cap Gripper 2 Not Ready", 
"DN-Interlock Alarm Servo Cap Gripper 2", 
"DN-Com. Fault Servo Cap Gripper 3", 
"DN-Servo Cap Gripper 3 Not Ready", 
"DN-Interlock Alarm Servo Cap Gripper 3", 
"DN-Com. Fault Servo Cap Gripper 4", 
"DN-Servo Cap Gripper 4 Not Ready", 
"DN-Interlock Alarm Servo Cap Gripper 4", 
"DN-Spare", 
"DN-Spare", 
"DN-Channel Feedr 1 -3 Max. Time Load Caps", 
"DN-Spare", 
"DN-Spare", 
"DN-Channel Feedr 2 - 4 Max. Time Load Caps", 
"DN-Spare", 
"DN-Spare", 
"DN-Channel Feedr 1,2,3,4 Max. Time Load Caps", 
"DN-Spare", 
"DN-Spare", 

"Unknown")

```

This is just the expression within the expression tag.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [October 25, 2022, 7:04pm UTC](https://forum.inductiveautomation.com/t/tag-historical-data-frequency-and-duration-of-tag-values/65667/6 "2022-10-25T19:04:44Z")

</div>

I would _strongly_ suggest migrating that monolithic expression to _at least_ use the [`case`](https://docs.inductiveautomation.com/display/DOC81/case) function for a much easier editing experience if any of those values need to change down the road. Or, even better, a dataset tag and the `lookup` expression function.

Another option, potentially better, would be to store it in a DB somewhere.

Are you using transaction groups, the tag historian, or some other mechanism to actually store these values?

---

<div class="post-metadata">

**Author:** ![Zach\_Stewart](https://avatars.discourse-cdn.com/v4/letter/z/90ced4/32.png) [@Zach\_Stewart](https://forum.inductiveautomation.com/u/Zach_Stewart)\
**Post date:** [October 25, 2022, 7:07pm UTC](https://forum.inductiveautomation.com/t/tag-historical-data-frequency-and-duration-of-tag-values/65667/7 "2022-10-25T19:07:22Z")

</div>

Thank you. I will read up on the `lookup` expression function.

How could I store into a DB somewhere? I am using MySQL but have not set up too many Databases.  
I am currently using the tag historian.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [October 25, 2022, 7:10pm UTC](https://forum.inductiveautomation.com/t/tag-historical-data-frequency-and-duration-of-tag-values/65667/8 "2022-10-25T19:10:35Z")

</div>

If you're using the tag historian, storing in a DB isn't as valuable, because you can't easily join the data table and the "aliases" table together. If you were using something like transaction groups to store data, upon _retrieval_ you could automatically tie the IDs together with the aliases.

I would say to move to a dataset tag. Call it whatever you like and then enter all these possible values into it. Then you can use the curly brace syntax to get the tag's value, and the `lookup` function to retrieve the specific alias you want.

---

<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:** [October 25, 2022, 7:25pm UTC](https://forum.inductiveautomation.com/t/tag-historical-data-frequency-and-duration-of-tag-values/65667/9 "2022-10-25T19:25:40Z")

</div>

This not a job for the tag historian. Events should be recorded in a dedicated table with timestamp, preferably as just the numeric code. A transaction group would be fine, or just a tag change event script. A separate table should have the association between code and text. Don't record the text (terribly wasteful of DB space).

Once you have events recorded, your database's `lead()` or `lag()` functions can help you obtain precise intervals, which the database can then `sum()`, with or without splitting intervals across shift boundaries. The shift boundaries can come from other tables, possibly with support for rotating shifts (also in the DB). The DB's `lead()` or `lag()` functions can also clean up any intervals where disruptions cause the same value to be recorded as the previous one.

Some of the DB operations can be complex, but regular scheduled maintenance operations can insert the results into pre-computed aggregate tables most useful for reports.

There are many ways to compute pieces of this task. All others are nightmares to assemble into the final result you indicate you want. This is a database task. Use the database.

For durations from event recordings in a time interval, see this:

> [@Calculating Total Time in Seconds](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/28):
>
> Put the following into a named query, with value parameters begin\_ts and end\_ts of type DateTime: SELECT stopCode, count(stopCode) as Qty, sum(CASE WHEN t\_stamp1 \> :end\_ts THEN :end\_ts ELSE t\_stamp1 END - CASE WHEN t\_stamp \< :begin\_ts THEN :begin\_ts ELSE t\_stamp END) AS Duration FROM ( SELECT t\_stamp, stopCode, coalesce(lead(t\_stamp) OVER (ORDER BY t\_stamp), CASE WHEN current\_timestamp \< :end\_ts THEN current\_timestamp ELSE :end\_ts END) AS t\_stamp1 WHERE t\_stamp \>= coalesce(( SELE…

For rotating shifts, this is a comprehensive solution:

> [@Script for periodically changing shift time](https://forum.inductiveautomation.com/t/script-for-periodically-changing-shift-time/51058/3):
>
> Hi Berkay, I like to solve this kind of problem with a smart database, like PostgreSQL, where I can define a pattern and repeat it with table generating functions. I tweaked one of my implementations to serve as an example: create table shift\_schedules (shift\_id serial, shift\_name varchar(255), refdate date, repeat int, tzname varchar(255), primary key (shift\_id)); insert into shift\_schedules (shift\_name, refdate, repeat, tzname) values ('Berkay Cinar 3wk', '2021-07-26', 21, 'America/Ne…

---

<div class="post-metadata">

**Author:** ![Zach\_Stewart](https://avatars.discourse-cdn.com/v4/letter/z/90ced4/32.png) [@Zach\_Stewart](https://forum.inductiveautomation.com/u/Zach_Stewart)\
**Post date:** [October 26, 2022, 12:56pm UTC](https://forum.inductiveautomation.com/t/tag-historical-data-frequency-and-duration-of-tag-values/65667/10 "2022-10-26T12:56:07Z")

</div>

I was able to set up the transaction group to create a table and use the lookup function from another table. This is a much easier and efficient way to do this. Thank you very much for the suggestion.

I am currently trying to get the code to work. I am receiving an error about the syntax and have tried numerous ways around it but can't seem to get it. Would you able to assist with what I am missing?

```auto
SELECT machine_state_code,
	count(machine_state_code) as Qty,
	sum(CASE WHEN t_stamp1 > :end_ts THEN :end_ts ELSE t_stamp1 END -
		CASE WHEN t_stamp < :begin_ts THEN :begin_ts ELSE t_stamp END) AS Duration
FROM (
	SELECT t_stamp,
		machine_state_code,
		coalesce(lead(t_stamp) OVER (ORDER BY t_stamp),
			CASE WHEN current_timestamp < :end_ts THEN current_timestamp ELSE :end_ts END) AS t_stamp1
	WHERE t_stamp >= coalesce((
		SELECT TOP 1 t_stamp
		FROM myTable
		WHERE t_stamp < :begin_ts
		ORDER BY t_stamp DESC), '1900-01-01')
	  AND t_stamp <= coalesce((
		SELECT TOP 1 t_stamp
		FROM myTable
		WHERE t_stamp > :end_ts
		ORDER BY t_stamp), '2100-01-01')
) subq
WHERE t_stamp < :end_ts AND t_stamp1 > :begin_ts
GROUP BY machine_state_code
ORDER BY count(machine_state_code) desc

```

```auto
com.inductiveautomation.ignition.client.gateway_interface.GatewayException: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 t_stamp
		FROM myTable
		WHERE t_stamp < '2022-10-26 06:00:00'
		ORDER BY t_st' at line 11
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:351)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:325)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:278)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.invoke(GatewayInterface.java:954)
	at com.inductiveautomation.ignition.designer.db.namedquery.workspace.NamedQueryTestingPanel$1$1.doInBackground(NamedQueryTestingPanel.java:336)
	at com.inductiveautomation.ignition.designer.db.namedquery.workspace.NamedQueryTestingPanel$1$1.doInBackground(NamedQueryTestingPanel.java:311)
	at java.desktop/javax.swing.SwingWorker$1.call(Unknown Source)
	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
	at java.desktop/javax.swing.SwingWorker.run(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.lang.Exception: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 t_stamp
		FROM myTable
		WHERE t_stamp < '2022-10-26 06:00:00'
		ORDER BY t_st' at line 11
	at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:407)
	at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor.execute(NamedQueryExecutor.java:196)
	at com.inductiveautomation.ignition.gateway.db.namedquery.GatewayNamedQueryManager.execute(GatewayNamedQueryManager.java:121)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.NamedQueryFunctions.executeNamedQuery(NamedQueryFunctions.java:67)
	at jdk.internal.reflect.GeneratedMethodAccessor363.invoke(null)
	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(null)
	at java.lang.reflect.Method.invoke(null)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.AbstractGatewayFunction.invoke(AbstractGatewayFunction.java:228)
	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:431)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
	at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:86)
	at org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1450)
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:799)
	at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1631)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:548)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:600)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1624)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1440)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:501)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1594)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1355)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
	at com.inductiveautomation.catapult.handlers.RemoteHostNameLookupHandler.handle(RemoteHostNameLookupHandler.java:121)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
	at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:322)
	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:59)
	at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:146)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
	at org.eclipse.jetty.server.Server.handle(Server.java:516)
	at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:487)
	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:732)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:479)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:277)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:105)
	at org.eclipse.jetty.io.ChannelEndPoint$1.run(ChannelEndPoint.java:104)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:338)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:315)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:173)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:131)
	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:409)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:883)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1034)
	at java.lang.Thread.run(null)

Ignition v8.1.20 (b2022082313)
Java: Azul Systems, Inc. 11.0.15

```

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/2/62abcc1048c746ebcc06f0ec310815cd098b5176.png)

---

<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:** [October 26, 2022, 1:07pm UTC](https://forum.inductiveautomation.com/t/tag-historical-data-frequency-and-duration-of-tag-values/65667/11 "2022-10-26T13:07:30Z")

</div>

That SQL was for SQL Server (the TOP clause). In MySQL or other databases, you generally put a LIMT clause after the ORDER BY.

(If your MySQL is v5.x, lead() and lag() won't work either. Consider changing to PostgreSQL, where the SQL language is closest to fully implemented.)

---

<div class="post-metadata">

**Author:** ![Zach\_Stewart](https://avatars.discourse-cdn.com/v4/letter/z/90ced4/32.png) [@Zach\_Stewart](https://forum.inductiveautomation.com/u/Zach_Stewart)\
**Post date:** [October 26, 2022, 2:31pm UTC](https://forum.inductiveautomation.com/t/tag-historical-data-frequency-and-duration-of-tag-values/65667/12 "2022-10-26T14:31:04Z")

</div>

I switched to PostgreSQL v15 to test this out.

```auto
SELECT * FROM POU102_MachineStateCode,
	count(machine_state_code) as Qty,
	sum(CASE WHEN t_stamp1 > :end_ts THEN :end_ts ELSE t_stamp1 END -
		CASE WHEN t_stamp < :begin_ts THEN :begin_ts ELSE t_stamp END) AS Duration
FROM (
	SELECT t_stamp,
		machine_state_code,
		coalesce(lead(t_stamp) OVER (ORDER BY t_stamp),
			CASE WHEN current_timestamp < :end_ts THEN current_timestamp ELSE :end_ts END) AS t_stamp1
	WHERE t_stamp >= coalesce((
		SELECT TOP 1 t_stamp
		FROM myTable
		WHERE t_stamp < :begin_ts
		ORDER BY t_stamp DESC), '1900-01-01')
	  AND t_stamp <= coalesce((
		SELECT TOP 1 t_stamp
		FROM myTable
		WHERE t_stamp > :end_ts
		ORDER BY t_stamp), '2100-01-01')
) subq
WHERE t_stamp < :end_ts AND t_stamp1 > :begin_ts
GROUP BY machine_state_code
ORDER BY count(machine_state_code) desc  

```

with the fault `org.postgresql.util.PSQLException: ERROR: syntax error at or near "FROM" Position: 195`

I do not see the obvious issue. I have tried several different methods and read the FROM clause syntax. I am very grateful for your support.

---

<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:** [October 26, 2022, 2:34pm UTC](https://forum.inductiveautomation.com/t/tag-historical-data-frequency-and-duration-of-tag-values/65667/13 "2022-10-26T14:34:37Z")

</div>

PostgreSQL also uses LIMIT, not TOP. TOP is a Microsoft thing.

---

<div class="post-metadata">

**Author:** ![Zach\_Stewart](https://avatars.discourse-cdn.com/v4/letter/z/90ced4/32.png) [@Zach\_Stewart](https://forum.inductiveautomation.com/u/Zach_Stewart)\
**Post date:** [October 26, 2022, 7:22pm UTC](https://forum.inductiveautomation.com/t/tag-historical-data-frequency-and-duration-of-tag-values/65667/14 "2022-10-26T19:22:10Z")

</div>

I hit it from ground zero and was able to get the counting feature working correctly. When I integrate the subq is when I am receiving some issues.

```auto

SELECT "Machine_State_Code",

COUNT ("Machine_State_Code") AS "FREQUENCY",

SUM(CASE WHEN t_stamp1 > :end_ts THEN :end_ts ELSE t_stamp1 END - CASE WHEN "t_stamp" < :begin_ts THEN :begin_ts ELSE "t_stamp" END) AS "DURATION"

FROM (
	SELECT 
	"t_stamp",
	"Machine_State_Code",
		coalesce(LEAD("t_stamp") OVER (ORDER BY "t_stamp"),
			CASE WHEN current_timestamp < :end_ts 
				THEN current_timestamp 
				ELSE :end_ts 
				END) AS t_stamp1
		WHERE "t_stamp" >= coalesce(( 
			SELECT "t_stamp" 
				FROM pou102_machinestatecode 
				WHERE "t_stamp" < :begin_ts 
				ORDER BY "t_stamp" DESC), "Tue Oct 26 18:00:00 EDT 2021")
				LIMIT 1 
		AND "t_stamp" <= coalesce(( 
			SELECT "t_stamp" 
			FROM pou102_machinestatecode 
			WHERE "t_stamp" > :end_ts  
			ORDER BY "t_stamp" DESC), "Fri Oct 26 18:00:00 EDT 2029")
			LIMIT 1
	) subq
WHERE "t_stamp" < :end_ts AND t_stamp1 > :begin_ts	

GROUP BY "Machine_State_Code"
ORDER BY count("Machine_State_Code") DESC

```

It is not recognizing the LIMIT function nor the "t\_stamp" and "Machine\_State\_Code" when I remove to the LIMIT feature while troubleshooting. I read some tutorials online and I do not see any examples on using them within a coalesce function.

I feel like I am close to the answer, but it must be obvious.

---

<div class="post-metadata">

**Author:** ![Zach\_Stewart](https://avatars.discourse-cdn.com/v4/letter/z/90ced4/32.png) [@Zach\_Stewart](https://forum.inductiveautomation.com/u/Zach_Stewart)\
**Post date:** [October 26, 2022, 7:29pm UTC](https://forum.inductiveautomation.com/t/tag-historical-data-frequency-and-duration-of-tag-values/65667/15 "2022-10-26T19:29:53Z")

</div>

Update: I believe I found the LIMIT answer through trial and error. Now I believe that I am missing the FROM declaration somewhere.

```auto

SELECT "Machine_State_Code",

COUNT ("Machine_State_Code") AS "FREQUENCY",

SUM(CASE WHEN t_stamp1 > :end_ts THEN :end_ts ELSE t_stamp1 END - CASE WHEN "t_stamp" < :begin_ts THEN :begin_ts ELSE "t_stamp" END) AS "DURATION"

FROM (
	SELECT 
	"t_stamp",
	"Machine_State_Code",
		coalesce(LEAD("t_stamp") OVER (ORDER BY "t_stamp"),
			CASE WHEN current_timestamp < :end_ts 
				THEN current_timestamp 
				ELSE :end_ts 
				END) AS t_stamp1
		WHERE "t_stamp" >= coalesce(( 
			SELECT "t_stamp" 
				FROM pou102_machinestatecode 
				WHERE "t_stamp" < :begin_ts 
				ORDER BY "t_stamp" DESC LIMIT 1), "Tue Oct 26 18:00:00 EDT 2021")
				
		AND "t_stamp" <= coalesce(( 
			SELECT "t_stamp" 
			FROM pou102_machinestatecode 
			WHERE "t_stamp" > :end_ts  
			ORDER BY "t_stamp" DESC LIMIT 1), "Fri Oct 26 18:00:00 EDT 2029")
			
	) subq
WHERE "t_stamp" < :end_ts AND t_stamp1 > :begin_ts	

GROUP BY "Machine_State_Code"
ORDER BY count("Machine_State_Code") DESC

```

---

<div class="post-metadata">

**Author:** ![Zach\_Stewart](https://avatars.discourse-cdn.com/v4/letter/z/90ced4/32.png) [@Zach\_Stewart](https://forum.inductiveautomation.com/u/Zach_Stewart)\
**Post date:** [October 26, 2022, 7:33pm UTC](https://forum.inductiveautomation.com/t/tag-historical-data-frequency-and-duration-of-tag-values/65667/16 "2022-10-26T19:33:27Z")

</div>

Completed code:

```auto

SELECT "Machine_State_Code",

COUNT ("Machine_State_Code") AS "FREQUENCY",

SUM(CASE WHEN t_stamp1 > :end_ts THEN :end_ts ELSE t_stamp1 END - CASE WHEN "t_stamp" < :begin_ts THEN :begin_ts ELSE "t_stamp" END) AS "DURATION"

FROM (
	SELECT 
	"t_stamp",
	"Machine_State_Code",
		coalesce(LEAD("t_stamp") OVER (ORDER BY "t_stamp"),
			CASE WHEN current_timestamp < :end_ts 
				THEN current_timestamp 
				ELSE :end_ts 
				END) AS t_stamp1
		FROM pou102_machinestatecode 
		WHERE "t_stamp" >= coalesce(( 
			SELECT "t_stamp" 
				FROM pou102_machinestatecode 
				WHERE "t_stamp" < :begin_ts 
				ORDER BY "t_stamp" DESC LIMIT 1), 'Tue Oct 26 18:00:00 EDT 2021')
				
		AND "t_stamp" <= coalesce(( 
			SELECT "t_stamp" 
			FROM pou102_machinestatecode 
			WHERE "t_stamp" > :end_ts  
			ORDER BY "t_stamp" DESC LIMIT 1), 'Fri Oct 26 18:00:00 EDT 2029')
			
	) subq
WHERE "t_stamp" < :end_ts AND t_stamp1 > :begin_ts	

GROUP BY "Machine_State_Code"
ORDER BY count("Machine_State_Code") DESC

```

Now to tie in descriptions/lookups...

Thank you for the help everyone

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [October 26, 2022, 9:09pm UTC](https://forum.inductiveautomation.com/t/tag-historical-data-frequency-and-duration-of-tag-values/65667/17 "2022-10-26T21:09:20Z")

</div>

> [@paul-griffith](#):
>
> would _strongly_ suggest migrating that monolithic expression to _at least_ use the [`case`](https://docs.inductiveautomation.com/display/DOC81/case)

I was about to say the same thing. The switch statement is _ **super** _ ugly and impossible to read