CODEWITHSUNDEEP
sqldb2
jason316
jason316

Reputation: 88

How do you filter to get data that is in between a certain time of day in IBM DB2

I am trying to add a filter condition in the DB2 database. I am new to it and come from an Oracle background. I am trying to get records with dates in between today at 4 AM and today at 5 PM only. I currently have the below query that returns zero results:

db2 => select datetimeColumn from datetimeExample WHERE datetimeColumn  BETWEEN timestamp(current date) - 1 day + 4 hour AND timestamp(current date) - 1 day + 13 hour

DATETIMECOLUMN
--------------------------

0 record(s) selected.

And here is the data in the table that I believe should show but there is something wrong with condition statement, any help is appreciated

db2 => select * from datetimeExample

DATETIMECOLUMN
--------------------------
2016-06-16-09.38.53.759000
1988-12-25-17.12.30.000000
2016-12-25-17.10.30.000000
2016-06-16-04.10.30.000000
2016-06-16-05.10.30.000000
1988-12-25-15.12.30.000000
1988-12-25-14.12.30.000000
2016-06-16-12.10.30.000000
2016-06-16-07.10.30.000000
2016-06-16-08.10.30.000000

10 record(s) selected.

Upvotes: 0

Views: 1709

Answers (1)

data_henrik
data_henrik

Reputation: 17176

The query should work when you leave out the - 1 day. The reason is that timestamp(current date) returns the timestamp for today at zero hours. Then you add 4 hours and are at the required start time. Similar maths for the end time (and 5 pm should be + 17 hours).

select datetimeColumn from datetimeExample
WHERE datetimeColumn  
BETWEEN timestamp(current date) + 4 hours AND timestamp(current date) + 17 hours

Upvotes: 2

Related Questions