Getting TFS Build Schedules

Question

I am trying to write a query for all builds, but failed to understand the column [ScheduleTime] in the table [tbl_Schedule].

SELECT bd.DefinitionName 'Name', s.ScheduleTime 'ScheduleTime', s.ScheduleTime/1800 'Hour'
FROM [TFS_FTPDev].[dbo].[tbl_Schedule] s
inner join [TFS_FTPDev].[dbo].[tbl_BuildDefinition] bd on 
bd.[DefinitionId]=s.[DefinitionId]
where ContinuousIntegrationType in (8,16)
order by s.ScheduleTime asc

If I divide by 3600, I get a sensible data, but it is not valid for all my builds... i.e. Build1 and build2 are scheduled for 13:00 for some days....

name   ScheduleTime Hour
build1 43200    12
build2 43200    12
build3 68400    19
build4 77400    21.30
build5 79200    22
build6 82800    23
build7 84600    23.30
build8 84600    23.30
build9 84600    23.30

and the mapping is:

when 84600 then 23.30 
when 79200 then 22 
when 77400 then 21.30 
when 68400 then 19 
when 43200 then 12

UPDATE: [Using TFS API]

And this is the API results for a manually triggered/scheduled build...

API results for a manually triggered/scheduled build

And this is the subset of results

Subset of Results from TFS API

I recommend that you use the public TFS Api instead... For inspiration: http://stackoverflow.com/questions/5263005/tfs-api-how-to-query-builds-independent-of-which-build-definition-they-belong — Morten Frederiksen, Jun 11 '14 at 15:36
It is perfectly fine if you want to query a build [a finished build which is a dynamic data] and you want to review the status, details about that specific build. I need build definition [more static data]... — demokritos, Jul 07 '14 at 22:55

score 1 · Accepted Answer · answered Jul 07 '14 at 19:40

1

Have you taken a look at the QueryBuilds method in the TFS Build API instead of reading from the database (which is unsupported)?

answered Jul 07 '14 at 19:40

Ed Blankenship

5,235
1
31
31

I am afraid the same numbers come from the [TFS API](http://continuousdevelopment.files.wordpress.com/2014/07/stakoverflow_tfsbuildschedule.png) for a manually triggered/scheduled buid... And this is the [subset of results](http://continuousdevelopment.files.wordpress.com/2014/07/stakoverflow_tfsbuildschedule2.png) – demokritos Jul 10 '14 at 00:37
1

I was missing a simple enabled column to get me the actual schedules on top of your answer. TFS API does the same resultset, but I'll use it, thanks. – demokritos Jul 16 '14 at 07:51

score 1 · Answer 2 · answered Jul 14 '14 at 17:21

1

The documentation for ISchedule.StartTime Property says

Gets or sets the time as the number of seconds past midnight

So you should divide by 3600 to get the starting hour, the remainder divided by 60 is the starting minute within the hour. Am I missing anything?

answered Jul 14 '14 at 17:21

Giulio Vian

8,248
2
33
41

That's correct Giulio, and the timing sounds right, but if I compare this value to the actual build times, I get different behaviour [manually triggered, not scheduled at all] for these builds. Possibly I am missing a simple boolean value to get me [results](http://continuousdevelopment.files.wordpress.com/2014/07/stakoverflow_tfsbuildschedule2.png) – demokritos Jul 16 '14 at 07:44

Just TFS · Answer 3 · 2014-07-11T14:30:21.997

0

I'm not really sure what you are asking but if you use 3,600 (60 seconds x 60 minutes) you get values as expected.

00:00 = 3600
00:30 = 5400
04:00 = 14400
05:30 = 19800
23:00 = 82800

Your question regarding why some have schedules while being manual builds can be replicated by:

create a build definition
Schedule it
Clone the build definition
Rename it and before saving change the trigger type
Even though this build has never been scheduled it still contains a scheduled time

I would therefore suggest the builds that you are enquiring about have actually been cloned from another build and then the meta data changed, scheduled time seems not be cleared when a build is changed from scheduled to Non Scheduled. either that or at some point during their history they have had a scheduled time set, which has since been changed

edited Jul 11 '14 at 14:30

answered Jul 10 '14 at 13:33

Just TFS

4,697
1
18
22

I aggree, 3600 looks like a key... But, that's in my question as wel... I can't explain 85800/3600= 23:50... And I can't explain why some 14400 are manual, why some are scheduled triggers. – demokritos Jul 11 '14 at 13:59
i Have updated my answer to include why the scheduled time is showing for not scheduled builds. And how you can replicate it – Just TFS Jul 14 '14 at 15:12

Getting TFS Build Schedules

3 Answers3