CODEWITHSUNDEEP
mysqlsqlselect
alessandro buffoli
alessandro buffoli

Reputation: 701

Select all fields(datetime) where hour is greather than a certain hour and the day is just the current day

probably the title is a little tricky. I have this table:

| id_ordineX | data_start          |
+------------+---------------------+
| ordine1    | 2018-08-06 08:00:00 |
| ordine1    | 2018-08-06 10:45:00 |
| ordine2    | 2018-08-06 16:20:00 |
| ordine2    | 2018-08-07 09:20:00 |

I want to select all fields where data_start > 2018-08-06 09:00:00, in this way I get as a result all the last 3 field but I want to select just the result from the day 2018-08-06, so basically I want to get just the second and the third field. How can I select just the date in a datetime format?

Upvotes: 0

Views: 38

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

I would recommend writing the query as:

SELECT t.*
FROM `table` t
WHERE `data_start` > '2018-08-06 09:00:00' AND  
      `data_start` < '2018-08-07';

By avoiding functions on data_start, the WHERE conditions make it more likely than an index -- if available -- will be used for the query.

I also think this does a very good job of describing the logic you want to implement.

Upvotes: 1

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

Simple datetime functions can be used:

SELECT *
FROM `table`
WHERE `data_start` > '2018-08-06 09:00:00' AND DATE(`data_start`) = '2018-08-06';

Or you could specify a range:

SELECT *
FROM `table`
WHERE `data_start` BETWEEN '2018-08-06 09:00:00' AND '2018-08-06 23:59:59';

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133370

You could use date and hour 

select * 
from my_table 
where date(date_start) = curdate() 
      and hour(date_start) > 9

Upvotes: 2

Related Questions