Select between times, fill in null hours

Question

I have a database here

| TIME | STOCK
|9:00  |24
|12:00 | 15

I want to display the time 6am now until 4am tomorrow

I want to put it in a mysql query where i will put null in specific time that not similar in my database

like this

ID   | STOCK
6:00 |NULL
7:00 |NULL
8:00 |NULL
9:00 |24
10:00|NULL
11:00|NULL
12:00|15

and so on...

Is there any body knows the solution . Thank you in advance.

this is not possible. you have to use your querying program language to add the missing date values — Michael Walter, Jul 15 '13 at 09:06
How can you determine what is today and tomorrow if you only have a time variable without any date in your table? — Bjoern, Jul 15 '13 at 09:06
@MichaelWalter Do you have any sample for this program. Thanks and Regards — student, Jul 15 '13 at 09:10
nope. that's allways a big deal for me. i've written this in php a long time ago. but now i try to prevent this everyime ^^'. the logic is simple: get the first datetime of the day and loop to the end of the day. if you cant find a value in your result, than give it a 0 — Michael Walter, Jul 15 '13 at 09:12
Sounds rather like a homework assignment to me. Lets see what you have already tried so we can help you as SO is about helping you reach your solution not about giving it to you on a plate. — Anigel, Jul 15 '13 at 09:12
I just don't know how can i put the for loop in a sql query. I know that it is easy for all of you , but i am a student who needs your help . — student, Jul 15 '13 at 09:18
This category of question is asked often enough to have its own tag. It is called [tag:gaps-and-islands]. I have edited your question to add that tag. Please look through some of those questions. Then please edit your question to [show what you have tried](http://stackoverflow.com/questions/how-to-ask). — 000, Jul 15 '13 at 09:49

score 1 · Answer 1 · answered Jul 15 '13 at 10:49

One way is like you can create temporary table for all the time-slots you need and a left join will do the trick.

stock table:

create table stock (TIME varchar(5), STOCK int(2));
insert into stock values
('9:00',24),
('12:00',15);

timings table (need to be temporary)

  create TEMPORARY table timings (timings varchar(5));
    insert into timings values
    ('6:00'),
    ('7:00'),
    ('8:00'),
    ('9:00'),
    ('10:00'),
    ('11:00'),
    ('12:00'),
    ('13:00'),
    ('14:00'),
    ('15:00'),
    ('16:00'),
    ('17:00'),
    ('18:00');

And your select query will be

select timings,STOCK from timings t left join
stock s on t.timings = s.TIME;

See the fiddle

Select between times, fill in null hours

1 Answers1