I'm looking for the efficient way to store week days and times of each event. How do I achieve the following result

Events table

CodePudding user response：

To be honest, store every event by itself.
The reason

1. Su 14:56:59 - this can be any Sunday out of 52 Sundays a year - not very efficient from my point of view
2. If you ever need a report of some sort, you would spend more time extracting dates from a string compared to just read the DB table rows

CodePudding user response：

As other colleagues have suggested: consider using a table that has 1 row for each event. Use an appropriate data type when storing dates & times eg

Table & data

create table events (
  id int auto_increment primary key
, name varchar( 100 )
, datetime timestamp 
) ;

select * from events ;

id  name            datetime
1   Hello World     2022-05-07 09:46:51
2   Hello World     2022-05-08 09:46:51
3   second event    2022-05-09 09:46:51
4   Hello World     2022-05-19 09:46:51
5   second event    2022-05-27 09:46:51
6   Hello World     2022-07-06 09:46:51 

Using timestamp will allow you to extract all the details you need (eg name of day, time etc).

Query

select name as eventname
, group_concat( 
    concat( substring( dayname( datetime ), 1, 3 )
    , ' ' 
    ,time( datetime ) ) 
  ) as dates
from events
group by name ;

-- result
eventname     dates
Hello World   Sat 09:50:49,Sun 09:50:49,Thu 09:50:49,Wed 09:50:49
second event  Mon 09:50:49,Fri 09:50:49

DBfiddle here.