saving timespan in sql table

  • Thread starter Thread starter cj2
  • Start date Start date
C

cj2

What is the best sql type to store a .net timespan ranging from say 40
seconds to fractions of a second?
 
A big int. What exactly would it be storing--ticks? Cause I figured
I'd store the timevalue.totalseconds in the sql table.

How would I convert a time value stored as a big int to seconds?
 
cj2 said:
A big int. What exactly would it be storing--ticks? Cause I figured
I'd store the timevalue.totalseconds in the sql table.

How would I convert a time value stored as a big int to seconds?
Click to expand...

TimeSpan.FromSeconds? It's all in the help for TimeSpan.

Andrew
 
Hi cj2,

Thanks for your post.

TimeSpan.TotalSeconds would be a good option for your situation. As
mentioned by Andrew, TimeSpan.FromSeconds is the method to convert it back
to a time span.

However, TimeSpan.TotalSecond is not a big int. It is a Double type, which
is usually mapped to a T-SQL Decimal data type. When you convert it back to
TimeSpan, it will be accurate to the nearest millisecond of the value you
passed in. I think this already meets your requirement from 40 seconds to
fractions of a second.

In addition to TimeSpan.TotalSeconds, TimeSpan.Ticks is also strongly
recommended. It is a long (64bit int) type, which is mapped to T-SQL bigint
data type. You can also rever it back by method: TimeSpan.FromTicks. It can
also supports your requirments. Using Ticks will be more accurate than
TotalSecond. Following is a code sample:
-----------------------------------------
Dim ts As TimeSpan = TimeSpan.Parse("15.18:23:53.1570129")
Dim newTs1 As TimeSpan = TimeSpan.FromSeconds(ts.TotalSeconds)
MessageBox.Show(newTs1.ToString) ' Get inaccurate value 15.18:23:53.1570000
Dim newTs2 As TimeSpan = TimeSpan.FromTicks(ts.Ticks)
MessageBox.Show(newTs2.ToString) ' Get accurate value 15.18:23:53.1570129
-----------------------------------------
The example demostrates TotalSeconds will round data to the nearest
millisecond. However, Ticks will not.

Please let me know if you need more information. I wlll be glad to follow
up.

Regards,
Hongye Sun ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within?2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
HI,
I would always store the base value. So store the timespan in ticks. You
work out anything from there

Greg
 
Thank you for your help. I think you have the best answer for my needs.
I'm going to use the decimal data type as I want to store
timespan.totalsecond in a manner that will be readable with a database
query. This table is storing a lot of the processing my program has
done and how long various parts of it took to do. Queries would be done
ad hock on this data to determine if and how often things took too long.

Ticks into a bigint sounds good if I were going to actually use these
values in a program at some point and needed accurate precision.
 
Glad to help.

Regards,
Hongye Sun ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top