Reputation: 27
I've table structure like this:
---------------------------
No | Data | create_time
---------------------------
1 | Data1 | 2020-04-28 00:01:30
2 | Data2 | 2020-04-28 13:04:00
3 | Data3 | 2020-04-27 01:01:30
4 | Data4 | 2020-04-27 14:04:00
How to query But with condition: Date 27 April Until 28 April And Time 00:00 Until 12:00 What I've tried so far:
SELECT * FROM mytable WHERE ((date(create_time) >= '2020-04-27' AND date(2020-04-27) <= '2020-04-28'
AND TO_CHAR(create_time,'HH24:MI:SS') BETWEEN '00:00:00' AND '12:00:00'))
And
SELECT * FROM mytable WHERE ((date(create_time) >= '2020-04-27' AND date(2020-04-27) <= '2020-04-28'
AND TO_CHAR(create_time,'HH24:MI:SS') =>'00:00:00' AND TO_CHAR(create_time,'HH24:MI:SS') <= '12:00:00'))
What I want to achieve is, I want to get the data from that date but the time is only on 00:00:00 until 12:00:00 (24 Hours Format) But it's still not working, The Date is correct but the time that I want is not.
Upvotes: 0
Views: 540
Reputation:
Note: this answer assumes that create_time
is correctly defined as timestamp
.
You can combine conditions on the date and time part:
select *
from mytable
where create_time::date between date '2020-04-27'and date '2020-04-28'
and create_time::time between time '00:00' and time '12:00'
Alternatively you can use a range condition without casting the column:
select *
from mytable
where create_time >= date '2020-04-27'
and create_time < date '2020-04-29'
and create_time::time between time '00:00' and time '12:00'
That can use an index on create_time
.
If you don't want to include times at precisely 12:00, you need to change the "time" condition as well:
and create_time::time >= time '00:00'
and create_time::time < time '12:00'
Upvotes: 2
Reputation: 7503
Try the following and here is the demo.
with cte as
(
select
*,
cast (create_time::timestamp as time) as hour
from times
)
select
no,
data
from cte
where date(create_time) >= '2020-04-27'
and date(create_time) <= '2020-04-28'
and hour between '00:00:00' and '12:00:00'
Output:
| No| Data |
*----------*
|1 | Data1 |
|3 | Data3 |
Upvotes: 0