CODEWITHSUNDEEP
sqlpostgresql
Jafran J S
Jafran J S

Reputation: 27

Postgresql Where Specific Time On Date

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

Answers (2)

user330315
user330315

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'

Online example

Upvotes: 2

zealous
zealous

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

Related Questions