CODEWITHSUNDEEP
mysqlsqldatabase
Erick Skrobot
Erick Skrobot

Reputation: 189

Add hour to date in the mysql query result

I have a query that returns a date, let's use current_date as an example:

SELECT
current_date();

I want to modify this query to return something like:

Date         |  Hour
2017-08-24       0
2017-08-24       1
2017-08-24       2
2017-08-24       3
2017-08-24       4
...

all the way to 23. I need this because i will have a different date every day and I'm trying to avoid creating a table with all dates and hours.

Upvotes: 0

Views: 192

Answers (2)

Erick Skrobot
Erick Skrobot

Reputation: 189

Thanks for your reply. I tried with the procedure but i settled with a more simple solution. I created a auxiliar table aux_hour with numbers from 0 to 23. In my example the soltution would be:

SELECT current_date, hour FROM aux_hour;

That worked for me but thanks to your answer suggesting procedures i learned more about them so thank you again.

Upvotes: 0

Jonathan Willcock
Jonathan Willcock

Reputation: 5245

What you can do is to write a stored procedure to generate all the hours from a given date. For example:

delimiter $$
CREATE PROCEDURE GetAllHoursInDay
(
    in in_date date
) 
BEGIN
SELECT convert (in_date, datetime)
UNION
SELECT in_date + interval 1 hour
UNION
SELECT in_date + interval 2 hour
UNION
SELECT in_date + interval 3 hour
UNION
SELECT in_date + interval 4 hour
UNION
SELECT in_date + interval 5 hour
UNION
SELECT in_date + interval 6 hour
UNION
SELECT in_date + interval 7 hour
UNION
SELECT in_date + interval 8 hour
UNION
SELECT in_date + interval 9 hour
UNION
SELECT in_date + interval 10 hour
UNION
SELECT in_date + interval 11 hour
UNION
SELECT in_date + interval 12 hour
UNION
SELECT in_date + interval 13 hour
UNION
SELECT in_date + interval 14 hour
UNION
SELECT in_date + interval 15 hour
UNION
SELECT in_date + interval 16 hour
UNION
SELECT in_date + interval 17 hour
UNION
SELECT in_date + interval 18 hour
UNION
SELECT in_date + interval 19 hour
UNION
SELECT in_date + interval 20 hour
UNION
SELECT in_date + interval 21 hour
UNION
SELECT in_date + interval 22 hour
UNION
SELECT in_date + interval 23 hour;
END$$
DELIMITER ;

You can then call it e.g. by:

call GetAllHoursInDay(current_date());

Not that AFAIK MySQL does not allow a FUNCTION to return a table, so you cannot have something of the form SELECT * FROM GetAllHoursInDay. However, you can circumvent this by creating a temporary table, filling it with the above procedure and then doing SELECT * from temptable.

Upvotes: 1

Related Questions