CODEWITHSUNDEEP
sqlselect
iie
iie

Reputation: 501

SQL statement, selecting data from specific range of hours

Intro: I have an database with table which contains column with hours, for example

08:30:00
08:45:00
09:30:00
12:20:00
...
18:00:00

the datatype in this column is "time". Question: is it possible with SQL to select all hours [ for eg. 10:00:00, 10:30:00, 11:00:00 etc. ] from range 08:00:00 to 18:00:00, which are not on the list?

edit: Well, after thinking it out, the solution which You [ thanks! ] is not entirely perfect [ well it is perfect with details I gave u :) ]. My application is allowing users to make an appointments which takes half an hour [ in this version of app ]. The column with time I gave u in my first topic is quite optimistic, because I didn't put cover meetings for hours such as 11:45:00 or 13:05:00, which will end at 12:15:00 and 13:35:00, so I don't really know from the top which hours I would be able to put in this extra table.

Upvotes: 1

Views: 550

Answers (3)

vyegorov
vyegorov

Reputation: 22925

Ugly, but possible:

CREATE TABLE thours (h time);
INSERT INTO thours VALUES
    ('08:30:00'), ('08:45:00'), ('09:30:00'),
    ('12:20:00'), ('18:00:00');

CREATE VIEW hrange AS
SELECT 8 as h UNION ALL SELECT  9 UNION ALL SELECT 10 UNION ALL
SELECT 11     UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL
SELECT 14     UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL
SELECT 17     UNION ALL SELECT 18;

CREATE VIEW hours AS
SELECT cast(concat(cast(h as char), ':00:00') as time) AS h FROM hrange
UNION ALL
SELECT cast(concat(cast(h as char), ':30:00') as time)
  FROM hrange WHERE h < 18
 ORDER BY 1;

SELECT h.h AS ranged, t.h AS scheduled
  FROM hours h
  LEFT join thours t ON h.h = t.h;

If you'll add WHERE t.h IS NULL to the query, you'll get a list of wanted hours.

I created views as MySQL cannot dynamically generate series. Try out here.

Upvotes: 1

A B
A B

Reputation: 4158

First, create a table T_REF with a column COL_HR with dates that have all the hours you want to report if not found in the original table.

In MYSQL doing something like this should work

SELECT DATE_FORMAT(COLUMN_NAME, '%H') FROM T_REF
MINUS
SELECT DATE_FORMAT(COLUMN_NAME, '%H') FROM ORIG_TABLE

This will get and compare only the hours and report the ones that are not in your table (ORIG_TABLE).

Upvotes: 1

Andrew Carmichael
Andrew Carmichael

Reputation: 3113

Are you only expecting to find every hour and half hour? Without knowing which times you're expecting to find or not find, I'd suggest the following:

Create a separate table with all possible times in it and then run a query to find which times don't exist in the original table.

Upvotes: 0

Related Questions