public IEnumerable <Track> GetTracksList(long carID, DateTime start, DateTime end)
{
var startDateTimestamp = TimeConverter.Convert(start) * 1000000;
var endDateTimestamp = TimeConverter.Convert(end) * 1000000;
using (PostgresConnectionProvider _provider = new PostgresConnectionProvider(_configuration.GetConnectionString("DataAccessPostgreSqlProviderRO")))
{
var res = _provider.Connection.Query <Track>(@"
with temp_gaps as (select (unix_timestamp/1000000) as start,
(lead(unix_timestamp) over(order by unix_timestamp))/1000000 as stop,
(lead(unix_timestamp) over(order by unix_timestamp)/1000000) - (unix_timestamp/1000000) as diff,
(min(obd.unix_timestamp) over())/1000000 as interval_start
from raw_data.obd_data obd
where obd.car_id = @CarID
and obd.unix_timestamp >= @start_period
and obd.unix_timestamp < @end_period
order by obd.unix_timestamp
), track as (select coalesce(lag(stop) over (order by stop), temp_gaps.interval_start) as start,
temp_gaps.start as end
from temp_gaps
where diff >= @StopLength
order by 1)
select
track.start,
track.end
from track
",
new { start_period = startDateTimestamp, end_period = endDateTimestamp, CarID = carID, StopLength = _configuration.GetValue <int>("StopLength") });
return(res);
}
}
public DeviceRepository(IConfiguration configuration)
{
_provider = new PostgresConnectionProvider(configuration);
}
public ObdDataRecordRepository(IConfiguration configuration)
{
_provider = new PostgresConnectionProvider(configuration);
}
