March 18, 2010 at 8:54 am
I am very new to SQL. IS there a suitable data-type for storing time values, specifically durations. What do I mean, well I am beginning a sports database. For sports, times as durations, laptimes, sectional times etc are captured and reviewed. Also lap comparisons eg faster and slower than previous or best times are recorded.
Does sql feature a data-type suitable for this need? Or would I need to use a data-type like decimal and then create a formula to convert the time to its lowest base for storage and the same back out.
March 18, 2010 at 4:20 pm
So what I am asking is, there a date and time formats, that handle what date it is or what the clock time is. But is there a type in SQL that deals with measured time, so that is measured units of time.
So for example car a took 1:21:30 (mm:ss:hundredths) for lap two, which can be saved and then compared to his/her previous lap and others lap times. Of course in a business environment this would be an handle time (time on call) in a call centre etc.
Lots of potential uses but how is it implemented and used?
March 18, 2010 at 5:06 pm
I have actually found a good resource for time durations and date/time which is very helpful to me. Should be able to help you too.
February 19, 2011 at 5:20 am
Hi
Did you find a solution to this? All the examples I have seen are taken the system time where as all I need to do is input an event time and compare this to a PB time and update as per.
I am doing this on a voluntary bases for a local competitive swim team to aid them in Sqaud development and event entry!
Thanks
February 19, 2011 at 7:23 am
graham.hilton23 (2/19/2011)
HiDid you find a solution to this? All the examples I have seen are taken the system time where as all I need to do is input an event time and compare this to a PB time and update as per.
I am doing this on a voluntary bases for a local competitive swim team to aid them in Sqaud development and event entry!
Thanks
Your question is rather vague, to have someone assist you, post your table definition, sample data and required output following the recommendations/instructions/T-SQL in the article whose link is the first link in my signature block. Doing this will more than likely have someone suggest/provide a tested answer to your question.
February 19, 2011 at 8:38 am
Objective - to record swimming times for a local club per stroke per person. We will have 2 tables one recording the events on an ongoing basis (Swim Meets) and one recording the current Personal bests per distance and stroke.
The swimmers stroke time needs to be entered into a table as: -
minutes:seconds.hundreth of a second ie 1:32.54
Currently this project is in a design stage, but this value will be manually entered by a user and needs to be in the stated format.
My question is how do I record this value as SQL and Access to not appear to understand the hundreth of a second so I do not how who to record this value! so any advice would be fantastic!
Thanks
February 19, 2011 at 1:18 pm
February 19, 2011 at 1:35 pm
The input will change on every event so does this just format the field to mins:secs.hundreths of secs?
Every time the person swims on the clock this will be recorded into the database in the events table per distance and stroke.
The idea then will be another table holding the swimmers PB's will be compared with the event times on update and if the time is faster the PB table will be updated.
Hope this makes sense? So the issue is inputting the swim time in the correct format. The time will not be constant as this depending on distance and stroke but the inputted time will be the format evrytime.
If I can make this work it will help the coaches look at the PB's so the swimmer can be assigned to the right training group and also help the coaches submit swimmers and times to new individual or group events!
The buzz word here is trend analyses. Just need to format a field to enter the correct time format!
Thanks
February 19, 2011 at 4:17 pm
Using the data type TIME(4) test this code to determine if it is what you need.
/*Using your input format think the following will work
to reformat the text field to a TIME data type
(note.01 one hundredth of a seconds = 10 milliseconds */
DECLARE @I VARCHAR(10)
SET @I = '1:32.54'
--Replace the period/decimal point
SET @I = REPLACE(@I,'.',':')
--set the first part of time (hours) to zero
SET @I = '00:'+@I
SELECT @I
SELECT CAST(@I as TIME(4)) -- for testing
-- must edit to correct statement for other
-- data columns and then execute
INSERT INTO table name(column name,columename,racetime)
SELECT somevalue, somevalue, CAST(@I as TIME(4))
February 19, 2011 at 4:51 pm
Cool I will give a try and let you know... thanks for the reply 🙂
February 19, 2011 at 4:55 pm
graham.hilton23
Your welcome, as you work it out come on back and post what you have done so as to help others who may have the same or a similiar situation.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply