ID extraction across mysql work data dates

The following data is available:

USER_NAME,ID,START_TIME,END_TIME
AAAA, 1, 2017/8/99:00, 2017/8/918:00
AAAA, 5, 2017/8/9 12:00, 2017/8/9 13:00
BBBB, 2, 2017/8/9 22:00, 2017/8/1000:00
BBBB, 2, 2017/8/1000:00, 2017/8/1000 07:00
BBBB, 5, 2017/8/1000 03:00, 2017/8/1000 04:00

*START_TIME, END_TIME are of DATETIME type.
ID [1] is the working hours, and [5] is the one-hour break.

This time should be separated every 15 minutes and the time should show which ID is applicable.
USER_NAME was able to obtain AAAA's data every 15 minutes, but BBBB's [BBBB, 2, 2017/8/9 22:00, 2017/8/1000:00] I couldn't extract the ID well when the end_TIME date was the next day.

I don't think it will be extracted because the following writing method only extracts time, but how exactly can I rewrite it to extract the ID even if the date is the next day?

SELECT distinct
       USER_NAME,
       ID,
       case when (DATE_FORMAT(CONVERT_TZ(END_TIME, '+00:00', 'Asia/Tokyo'), '%H:%i')>'00:00' and (DATE_FORMAT(CONVERT_TZ(START_TIME, '+00:00', 'Asia/Tokyo'), '%H:%i')>INVER ID 15_INVED)
            case when (DATE_FORMAT(CONVERT_TZ(END_TIME, '+00:00', 'Asia/Tokyo', '%H:%i')>'00:15' and (DATE_FORMAT(CONVERT_TZ(START_TIME, '+00:00', 'Asia/Tokyo', '%i')>INVERTER ID:30_INVED))
.
.
.
. Change the time every 15 minutes until 23:59.
case when (DATE_FORMAT(CONVERT_TZ(END_TIME, '+00:00', 'Asia/Tokyo', '%H:%i')>'23:30' and (DATE_FORMAT(CONVERT_TZ(START_TIME, '+00:00', 'Asia/Tokyo', '%i')>INVERTER_45_INVED))
            case when (DATE_FORMAT(CONVERT_TZ(END_TIME, '+00:00', 'Asia/Tokyo', '%H:%i')>'23:45' and (DATE_FORMAT(CONVERT_TZ(START_TIME, '+00:00', 'Asia/Tokyo', '%i')>INVERTER ID:59_INVED))
FROM Table 1
WHERE 
    DATE_FORMAT(CONVERT_TIME, '+00:00', 'Asia/Tokyo', '%Y%m%d') = DATE_FORMAT(CONVERT_TZ(CURRENT_TIMESTAMP, '+00:00', 'Asia/Tokyo', '%Y%m%d');

mysql

2022-09-30 21:23

1 Answers

If you are crossing midnight on the same USER_NAME, you may want to add END_TIME times to the conditions in each column if they match 00:00 because they seemed to register the records until 23:59 the previous day and after 00:00 the next day.(Example below)

 case when(
    (DATE_FORMAT(CONVERT_TZ(END_TIME, '+00:00', 'Asia/Tokyo', '%H:%i')>'00:00'
     OR
     DATE_FORMAT(CONVERT_TZ(END_TIME, '+00:00', 'Asia/Tokyo', '%H:%i') = '00:00'
     )
     and
     DATE_FORMAT(CONVERT_TZ(START_TIME, '+00:00', 'Asia/Tokyo', '%H:%i')<'00:15')
     then SCHEDULE_INTERVAL_TYPE_ID end as '00:00',
/* Abbreviated */
case when(
    (DATE_FORMAT(CONVERT_TZ(END_TIME, '+00:00', 'Asia/Tokyo', '%H:%i')>'23:45'
     OR
     DATE_FORMAT(CONVERT_TZ(END_TIME, '+00:00', 'Asia/Tokyo', '%H:%i') = '00:00'
    )
    and
    DATE_FORMAT(CONVERT_TZ(START_TIME, '+00:00', 'Asia/Tokyo', '%H:%i')<'23:59')
    then SCHEDULE_INTERVAL_TYPE_ID end as '23:45'

When START_TIME and END_TIME cross the day in #1 records, I thought that the UNIX_TIMESTAMP function should be used to see the difference in total seconds, but I answered a simple question.

2022-09-30 21:23

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656