def count_datasets_through_time_query(self, start, end, period, time_field,
expressions):
raw_expressions = self._alchemify_expressions(expressions)
start_times = select(
(func.generate_series(start, end, cast(
period, INTERVAL)).label('start_time'), )).alias('start_times')
time_range_select = (select((func.tstzrange(
start_times.c.start_time,
func.lead(start_times.c.start_time).over()).label('time_period'),
))).alias('all_time_ranges')
# Exclude the trailing (end time to infinite) row. Is there a simpler way?
time_ranges = (select(
(time_range_select,
)).where(~func.upper_inf(time_range_select.c.time_period))
).alias('time_ranges')
count_query = (select((func.count('*'), )).select_from(
self._from_expression(DATASET, expressions)).where(
and_(
time_field.alchemy_expression.overlaps(
time_ranges.c.time_period), DATASET.c.archived == None,
*raw_expressions)))
return select(
(time_ranges.c.time_period, count_query.label('dataset_count')))
def count_datasets_through_time(self, start, end, period, time_field, expressions):
"""
:type period: str
:type start: datetime.datetime
:type end: datetime.datetime
:type expressions: tuple[datacube.index.postgres._fields.PgExpression]
:rtype: list[((datetime.datetime, datetime.datetime), int)]
"""
raw_expressions = self._alchemify_expressions(expressions)
start_times = select((
func.generate_series(start, end, cast(period, INTERVAL)).label('start_time'),
)).alias('start_times')
time_range_select = (
select((
func.tstzrange(
start_times.c.start_time,
func.lead(start_times.c.start_time).over()
).label('time_period'),
))
).alias('all_time_ranges')
# Exclude the trailing (end time to infinite) row. Is there a simpler way?
time_ranges = (
select((
time_range_select,
)).where(
~func.upper_inf(time_range_select.c.time_period)
)
).alias('time_ranges')
count_query = (
select(
(func.count('*'),)
).select_from(
self._from_expression(DATASET, expressions)
).where(
and_(
time_field.alchemy_expression.overlaps(time_ranges.c.time_period),
DATASET.c.archived == None,
*raw_expressions
)
)
)
results = self._connection.execute(select((
time_ranges.c.time_period,
count_query.label('dataset_count')
)))
for time_period, dataset_count in results:
# if not time_period.upper_inf:
yield Range(time_period.lower, time_period.upper), dataset_count
