So i have this table of reports:
| report_Id | report_date |
|---|---|
| 01 | 2022-12-07 08:00:00 |
| 02 | 2022-12-07 12:00:00 |
| 03 | 2022-12-07 22:00:00 |
What I wanted to do is to get rows that are reported in 05:00:00 between 12:00:00 which I would like to name as AM and rows that are reported in 13:00:00 between 23:00:00 as PM.
This would be the output example for AM:
| report_Id | report_date |
|---|---|
| 01 | 2022-12-07 08:00:00 |
| 02 | 2022-12-07 12:00:00 |
And this would be the output example for PM:
| report_Id | report_date |
|---|---|
| 03 | 2022-12-07 22:00:00 |
Did try to search but it would be really helpful if I can get answers from here. Thank You
CodePudding user response：
Postgres variant:
You can use CASE for creating new column for example to mark AM and PM as following using CTE, where you can filter rows by new column:
With t1 as(
select
report_id,
report_date,
case when report_date::time between '05:00:00'::time and '12:00:00'::time then 'AM' else 'PM' end as time_of_day
from table)
Select * from t1 where time_of_day = 'AM'
CodePudding user response：
In MYSQL ::
FOR AM :
select * from table where report_date between '2022-12-07 00:00:00' and '2022-12-07 11:59:59'
FOR PM:
select * from table where report_date between '2022-12-07 12:00:00' and '2022-12-07 23:59:59'