Get Every Hour for a Time Range

Get every hour for a time range

If you have a numbers table (click the link to create one if you don't)...

create table test(
    startTime time
,   endTime time
)

insert into test
select '11:00', '14:00'

select
    dateadd(hh, n.n, t.startTime) as times
from test t
  inner join Numbers n
    -- assuming your numbers start at 1 rather than 0
    on n.n-1 <= datediff(hh, t.startTime, t.endTime)

If this is specialized, you can create an hours table with just 24 values.

create table HoursInADay(
    [hours] time not null
,   constraint PK_HoursInADay primary key ([hours])
)

-- insert
insert into HoursInADay select '1:00'
insert into HoursInADay select '2:00'
insert into HoursInADay select '3:00'
insert into HoursInADay select '4:00'
insert into HoursInADay select '5:00'
insert into HoursInADay select '6:00'
insert into HoursInADay select '7:00'
...

select
    h.[hours]
from test t
  inner join HoursInADay h
    on h.[hours] between t.startTime and t.endTime

How to get all times within a time range

Using the date time module might be useful. Here's my idea for your problem if you were to use military time:

import datetime

start = datetime.time(10,0) # 10:00
end = datetime.time(10,5) # 10:05
TIME_FORMAT = "%H:%M" # Format for hours and minutes
times = [] # List of times 
while start <= end:
    times.append(start)
    if start.minute == 59: # Changes the hour at the top of the hour and set the minutes back to 0
        start = start.replace(minute=0) # have to use the replace method for changing the object
        start = start.replace(hour=start.hour + 1)
    else:
        start = start.replace(minute=start.minute + 1)
times = [x.strftime(TIME_FORMAT) for x in times] # Uses list comprehension to format the objects
print(times)

How to select in the query a time range of 1 hour?

You may try extracting the hour component from both your clock column and the current timestamp, and then comparing the difference between the two.

select *
from history 
where
    abs(extract(hour from clock) - extract(hour from current_timestamp)) <= 1;

If you instead want all records within the last hour, then try:

select *
from history
where extract(epoch from current_timestamp - clock) / 3600 <= 1;

How can I extract a specific range of hours from a dataframe in R

Don't separate out the date from the time. When you use format to get the time, you are converting it to a character class, that doesn't know how to do time-based comparisons with > and <.

Instead, use hour to extract the hour component as an integer, and do comparisons on that:

library(lubridate)
Th %>% 
  mutate(DateTime <- as.POSIXct(date, "%Y-%m-%d %H:%M:%S", tz = "")) %>%
  filter(hour(DateTime) >= 6 & hour(DateTime ) < 18)

I'm making some assumptions about your data structure - if you need more help than this please edit your question to share some sample data with dput() as the commenters have requested. dput(Th[1:5, ]) should be plenty.

Note that if you want to do a lot of operations on just the times (ignoring the date part), you could use the times class from the chron package, see here for some more info.

How to generate_series of every hour of every day of 1 year from the current timestamp

This is to similar to your previous question.

Use:

SELECT date_trunc('hour', now()::timestamp) - generate_series(0, 24 * 365) * interval '1 hour' 

This outputs:

2019-07-23 05:00:00
2019-07-23 04:00:00
etc

Getting number of Hours between a Date-Time Range in Python

You might be overthinking it.

from datetime import datetime, timedelta

date_format = "%Y-%m-%d %H:%M:%S"
start_date_time = datetime.strptime("2018-10-16 07:00:00", date_format)
end_date_time = datetime.strptime("2018-10-18 22:00:00", date_format)

def get_delta(l, r):
    return abs(int((l-r).total_seconds())) / 3600

for h in range(int(get_delta(start_date_time, end_date_time))):
    print((start_date_time + timedelta(0, h*3600)).strftime(date_format))

---

Related Topics