MySQL DB Select date removes an hour randomly from the value?-CodePudding

I'm querying a view. And when I select a date value it removes 1 hr from the value? date_sub(date, INTERVAL -1 hr) doesn't work as a workaround. this seems to be happening randomly?

Example of values:

select ticket_id,
                priority,
                status,
                New_status,
                Prev_status,
                message_date, 
                status_time_duration,
                lead(New_status,1) OVER (PARTITION BY ticket_id, priority, status  order by ticket_id,message_date desc) lead_new_status,
                lag(New_status,1) OVER (PARTITION BY ticket_id, priority, status  order by ticket_id,message_date desc) lag_new_status,
                lead(message_date,1) OVER (PARTITION BY ticket_id, priority, status   order by ticket_id, message_date desc) lead_new_message_date,
                lag(message_date,1) OVER (PARTITION BY ticket_id, priority, status   order by ticket_id,message_date desc) lag_new_message_date
            from v_ticket_status_history

Results:

When I put it in a subselect, it changes the datetime for some reason!

select ticket_id,
    priority, 
    status, 
    case when new_status='Open' and prev_status= 'New' then message_date else 0 end as open_ticket_date /*2021-12-17 12:16:39 2021-10-18 06:11:50 */
    from ( 
            select ticket_id,
                priority,
                status,
                New_status,
                Prev_status,
                message_date, 
                status_time_duration,
                lead(New_status,1) OVER (PARTITION BY ticket_id, priority, status  order by ticket_id,message_date desc) lead_new_status,
                lag(New_status,1) OVER (PARTITION BY ticket_id, priority, status  order by ticket_id,message_date desc) lag_new_status,
                lead(message_date,1) OVER (PARTITION BY ticket_id, priority, status   order by ticket_id, message_date desc) lead_new_message_date,
                lag(message_date,1) OVER (PARTITION BY ticket_id, priority, status   order by ticket_id,message_date desc) lag_new_message_date
            from v_ticket_status_history
            )a;

Result:

I really don't understand why this happens. From one value it removes 1 hour, from the second value it removes 2 hrs! Any advice?

Kind regards, Rosa

CodePudding user response：

Add A GROUP BY and a MAX for the date

select ticket_id,
    priority, 
    status, 
   MAX( case when new_status='Open' and prev_status= 'New' then message_date else 0 end) as open_ticket_date /*2021-12-17 12:16:39 2021-10-18 06:11:50 */
    from ( 
            select ticket_id,
                priority,
                status,
                New_status,
                Prev_status,
                message_date, 
                status_time_duration,
                lead(New_status,1) OVER (PARTITION BY ticket_id, priority, status  order by ticket_id,message_date desc) lead_new_status,
                lag(New_status,1) OVER (PARTITION BY ticket_id, priority, status  order by ticket_id,message_date desc) lag_new_status,
                lead(message_date,1) OVER (PARTITION BY ticket_id, priority, status   order by ticket_id, message_date desc) lead_new_message_date,
                lag(message_date,1) OVER (PARTITION BY ticket_id, priority, status   order by ticket_id,message_date desc) lag_new_message_date
            from v_ticket_status_history
            )a
    GROUP BY ticket_id,
    priority, 
    status;

This will show the higest date for all ticket, priority and status

CodePudding user response：

I ended up filtering the subselect and selecting the max(message_date) value. This returned correct values.

select ticket_id,
    priority, 
    status, 
    max(message_date) open_ticket_date-- ,
    -- max(case when new_status='Open' and prev_status= 'New' then message_date else 0 end) as  open_ticket_date /*2021-12-17 12:16:39 2021-10-18 06:11:50 */
    from ( 
            select ticket_id,
                priority,
                status,
                New_status,
                Prev_status,
                message_date,
                status_time_duration,
                lead(New_status,1) OVER (PARTITION BY ticket_id, priority, status  order by ticket_id,message_date desc) lead_new_status,
                lag(New_status,1) OVER (PARTITION BY ticket_id, priority, status  order by ticket_id,message_date desc) lag_new_status,
                lead(message_date,1) OVER (PARTITION BY ticket_id, priority, status   order by ticket_id, message_date desc) lead_new_message_date,
                lag(message_date,1) OVER (PARTITION BY ticket_id, priority, status   order by ticket_id,message_date desc) lag_new_message_date
            from v_ticket_status_history
            wherenew_status='Open'and prev_status= 'New'
            )a  
        group by ticket_id,
    priority, 
    status

Result:

Thank you all! Rosa