Tag Historical data - frequency and duration of tag values

Ignition
,
1

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

2
  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.
3
  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.
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.
4

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.

5

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.

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.

6

I would strongly suggest migrating that monolithic expression to at least use the 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?

1 Like
7

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.

8

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.

9

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:

For rotating shifts, this is a comprehensive solution:

4 Likes
10

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?

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

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
image823×475 54.5 KB

11

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.)

1 Like
12

I switched to PostgreSQL v15 to test this out.

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.

13

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

1 Like
14

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.


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.

1 Like
15

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


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
1 Like
16

Completed code:


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

2 Likes
17

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