date - Create a time (by half hours) table in MySQL -

for reporting purposes need time table acts lookup table lists every half hour in day. function same way date table except it's time.

i found great example of 24 hour table:

    create table hourly_data (     id int primary key auto_increment,     date date not null,     hour int not null,     f1 int not null,     f2 int not null,     f3 int not null,     f4 int not null,     f5 int not null,     f6 int not null,     inserted timestamp not null default current_timestamp on update current_timestamp ) 

but rather every half hour. ideas on how this? shifts , create specialized reports. in advance!

in mariadb can direct use sequence engine this:

select   date('2010/01/01') + interval (seq * 30) minute mydate seq_0_to_10; 

sample

mariadb [mysql]> select date('2010/01/01') + interval (seq * 30) minute mydate seq_0_to_10; +---------------------+ | mydate              | +---------------------+ | 2010-01-01 00:00:00 | | 2010-01-01 00:30:00 | | 2010-01-01 01:00:00 | | 2010-01-01 01:30:00 | | 2010-01-01 02:00:00 | | 2010-01-01 02:30:00 | | 2010-01-01 03:00:00 | | 2010-01-01 03:30:00 | | 2010-01-01 04:00:00 | | 2010-01-01 04:30:00 | | 2010-01-01 05:00:00 | +---------------------+ 11 rows in set (0.00 sec)  mariadb [mysql]> 

old way without sequence engine

select   date('2010/01/01') + interval (nr * 30) minute mydate (   select d2.a*10+d1.a  nr (    select 0  union select 1 union select 2 union select 3 union select 4     union select 5 union select 6 union select 7 union select 8  union select 9) d1    cross join (    select 0 union select 1 union select 2 union select 3 union select 4  ) d2 d2.a*10+d1.a between 0 , 47 ) parameter order nr; 

sample

mariadb [(none)]> select     ->   date('2010/01/01') + interval (nr * 30) minute mydate     -> (     ->   select d2.a*10+d1.a  nr (     ->    select 0  union select 1 union select 2 union select 3 union select 4     ->    union select 5 union select 6 union select 7 union select 8  union select 9) d1     ->    cross join (     ->    select 0 union select 1 union select 2 union select 3 union select 4  ) d2     -> d2.a*10+d1.a between 0 , 47     -> ) parameter     -> order nr; +---------------------+ | mydate              | +---------------------+ | 2010-01-01 00:00:00 | | 2010-01-01 00:30:00 | | 2010-01-01 01:00:00 | | 2010-01-01 01:30:00 | | 2010-01-01 02:00:00 | | 2010-01-01 02:30:00 | | 2010-01-01 03:00:00 | | 2010-01-01 03:30:00 | | 2010-01-01 04:00:00 | | 2010-01-01 04:30:00 | | 2010-01-01 05:00:00 | | 2010-01-01 05:30:00 | | 2010-01-01 06:00:00 | | 2010-01-01 06:30:00 | | 2010-01-01 07:00:00 | | 2010-01-01 07:30:00 | | 2010-01-01 08:00:00 | | 2010-01-01 08:30:00 | | 2010-01-01 09:00:00 | | 2010-01-01 09:30:00 | | 2010-01-01 10:00:00 | | 2010-01-01 10:30:00 | | 2010-01-01 11:00:00 | | 2010-01-01 11:30:00 | | 2010-01-01 12:00:00 | | 2010-01-01 12:30:00 | | 2010-01-01 13:00:00 | | 2010-01-01 13:30:00 | | 2010-01-01 14:00:00 | | 2010-01-01 14:30:00 | | 2010-01-01 15:00:00 | | 2010-01-01 15:30:00 | | 2010-01-01 16:00:00 | | 2010-01-01 16:30:00 | | 2010-01-01 17:00:00 | | 2010-01-01 17:30:00 | | 2010-01-01 18:00:00 | | 2010-01-01 18:30:00 | | 2010-01-01 19:00:00 | | 2010-01-01 19:30:00 | | 2010-01-01 20:00:00 | | 2010-01-01 20:30:00 | | 2010-01-01 21:00:00 | | 2010-01-01 21:30:00 | | 2010-01-01 22:00:00 | | 2010-01-01 22:30:00 | | 2010-01-01 23:00:00 | | 2010-01-01 23:30:00 | +---------------------+ 48 rows in set (0.00 sec)