Postgresql Where Specific Time On Date

Question

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.

What data type is create_time?

user330315
– user330315

2020-04-28 07:38:15 +00:00
Commented Apr 28, 2020 at 7:38 — user330315
– user330315, Commented Apr 28, 2020 at 7:38

score 2 · Accepted Answer · 2020-04-28 07:57:52Z

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

@Abelisto: you are right, but then you shouldn't use between

zealous · Accepted Answer · 2020-04-28 07:51:02Z

0

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 |

edited Apr 28, 2020 at 7:51

answered Apr 28, 2020 at 7:27

zealous

7,5434 gold badges20 silver badges39 bronze badges

2 Comments

Jafran J S Over a year ago

Sorry, what I want to achieve is condition on where clause. I've updated my question

zealous Over a year ago

@JafranJS I have provided you an example. please take a look and let me know if it won't work for you.

Collectives™ on Stack Overflow

Postgresql Where Specific Time On Date

2 Answers 2

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Related