CODEWITHSUNDEEP
sqlpostgresqlamazon-redshift
Ankur Garg
Ankur Garg

Reputation: 2853

Compare date and hour together

I am using Amazon Redshift . In my tables I have two columns (Date and Hour) ,along with other columns with values like

requestdate  requesthour
2016-10-10     1
2016-10-10     2
 ...          ...

Now I want to compare two dates with hours (day between 2016-10-1005 && 2016-11-1109) . Generally comparing with qyery like 

Where date>=xxxx && date<=yyyy && hour>=hhhh && hour<hhhh

will give incomplete results . Can I do something which can be useful here , I tried certain things after searching the web like concat , to_timestamp but could not come up with proper query .

Can anyone help here?

Upvotes: 0

Views: 130

Answers (2)

tata.leona
tata.leona

Reputation: 1058

You can cast your date + hour to timestamp and let database to compare them

select (requestdate ||' '|| requesthour ||':00:00')::timestamp  between ('2016-10-10 05:00:00')::timestamp and ('2016-08-01 04:00:00')::timestamp  from bbt

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Here is one explicit way:

where (date > xxxx or (date = xxxx and hour >= hhhh)) and
      (date < yyyy or (date = yyyy and hour < hhhh))

Another method would use date arithmetic:

where date + hour * interval '1 hour' >= xxxx + hhhh * interval '1 hour' and
      date + hour * interval '1 hour' < yyyy + hhhh * interval '1 hour'

Upvotes: 1

Related Questions