CODEWITHSUNDEEP
sqlitedatetime
Nadjib Bendaoud
Nadjib Bendaoud

Reputation: 524

get values between two datetimes, but only considering hours

I have a sqlite database that contains Date column which containt dates every 15 minutes

 Date                   Hourly_value   quart_value

"2015-12-30 03:00:00"   "944.691025"  "987.196765"

"2015-12-30 03:15:00"   "941.060427"

"2015-12-30 03:30:00"   "967.305421"    

"2015-12-30 03:45:00"   "987.196765"    

"2015-12-30 04:00:00"   "1016.272784"   "1017.077076"

I want to get hourly_values only between 2 dates, so when i want to get the values between "2015-12-30 03:00:00" and "2015-12-30 04:00:00", i want to get

Date                   Hourly_value   quart_value

"2015-12-30 03:00:00"   "944.691025"  "987.196765"
"2015-12-30 04:00:00"   "1016.272784"   "1017.077076"

and not values every 15 minutes 

 Date                   Hourly_value   quart_value

 "2015-12-30 03:00:00"   "944.691025"  "987.196765"

 "2015-12-30 03:15:00"   "941.060427"

 "2015-12-30 03:30:00"   "967.305421"    

 "2015-12-30 03:45:00"   "987.196765"    

 "2015-12-30 04:00:00"   "1016.272784"   "1017.077076"

In sql i can't find a way to get the dates between a certain range considering only hours, because using between will give me 4 values (1 value every 15 minutes ).

SELECT * FROM Conso WHERE Date BETWEEN '2015-12-30 03:00:00' AND '2015-12-30 
04:00:00'

Upvotes: 0

Views: 31

Answers (1)

anefeletos
anefeletos

Reputation: 702

A more general solution could be SELECT* From Conso WHERE (Date BETWEEN '2015-12-30 03:00:00' AND '2015-12-30 ) AND (INSTR(Date,':00:00') >0)

Upvotes: 1

Related Questions