Skip to content

Instantly share code, notes, and snippets.

@joshrobb
Created March 27, 2016 01:49
Show Gist options
  • Save joshrobb/5aaafbb2cfcb7fe2591d to your computer and use it in GitHub Desktop.
Save joshrobb/5aaafbb2cfcb7fe2591d to your computer and use it in GitHub Desktop.
Try to figure out how many tests Team city thinks ran between two dates.
--for a given build id - what time was it added/removed from the queue (in UTC + 13)
select *,dateadd(ms, queued_time%(3600*24*1000), dateadd(day, queued_time/(3600*24*1000), '1970-01-01 13:00')), dateadd(ms, remove_from_queue_time%(3600*24*1000), dateadd(day, remove_from_queue_time/(3600*24*1000), '1970-01-01 13:00'))
from build_state b inner join stats s on b.build_id = s.build_id
where dateadd(ms, queued_time%(3600*24*1000), dateadd(day, queued_time/(3600*24*1000), '1970-01-01 13:00')) <= '2016-03-25'
and dateadd(ms, queued_time%(3600*24*1000), dateadd(day, queued_time/(3600*24*1000), '1970-01-01 13:00')) > '2016-03-24'
select sum(test_count), min( dateadd(ms, queued_time%(3600*24*1000), dateadd(day, queued_time/(3600*24*1000), '1970-01-01 13:00'))), max( dateadd(ms, queued_time%(3600*24*1000), dateadd(day, queued_time/(3600*24*1000), '1970-01-01 13:00')))
from build_state b inner join stats s on b.build_id = s.build_id
where dateadd(ms, queued_time%(3600*24*1000), dateadd(day, queued_time/(3600*24*1000), '1970-01-01 13:00')) < '2016-03-26'
and dateadd(ms, queued_time%(3600*24*1000), dateadd(day, queued_time/(3600*24*1000), '1970-01-01 13:00')) > '2016-03-20'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment