When querying an MS SQL Server database, I usually parse the RowSet and store values into an array. This is by iterating the row and columns of the RowSet and getting the StringValue property of the DatabaseColumn when calling the ColumnAt method.
The trouble is, StringValue truncates Datetime fields from the database to second precision. How can I keep millisecond precision in the StringValue?
store SecondsFrom1970 = gmt/utc from the DateTime class
when you read the value back you set the timezone to local if required and reconstruct it.
Var epochFromDb As Double = rows.Column("mytimestamp").doublevalue
Var tz As Timezone = TimeZone.Current
Var myLocalDate As New DateTime(epochFromDb, tz)
Storing the SQLDateTime can cause issues, as it’s not 100% the time you gave it (as you have found out), also the Timezone may be unknown (since there is no information given) so make sure you define it yourself.
SecondsFrom1970 only gets second precision and doesn’t appear to get the milliseconds from the datetime value in the database?
it does since it’s a double everything after the “.” is nanoseconds on windows it would be less precise than on macos/linux.
https://documentation.xojo.com/api/data_types/datetime.html#datetime-secondsfrom1970
SecondsFrom1970 store values precise and truncated to the “second”.
If you need fractions of second better store such fraction in another field or read the date/time with fractional seconds (the ending ".nnnnnnn " seven nanoseconds digits) as string and process it using your own routines as Xojo does not handle it writing or reading.
we use this mainly (nanoseconds) but FromString doesn’t know this concept.
Xojo’s from and to string is limited to max the year, month, day, hour, minute and seconds.
This is way you want to store the SecondsFrom1970 from where you got the date in the first place.
If you received it as string from somewhere you may want to re-construct using the DateTime constructor and parse the string yourself.
You could try this:
Public Function FromRFC1123(Extends dt as DateTime, input as String) As DateTime
#Pragma Unused dt
Var tmp As String = Input.Trim
tmp = tmp.ReplaceAll(", ", " ")
Var bits() As String = tmp.Split(" ")
Var yearStr As String
Var monthName As String
Var dayName As String
Var dayStr As String
Var year As Integer
Var month As Integer
Var day As Integer
Var timeStr As String
Var hrStr As String
Var minStr As String
Var secStr As String
Var hour As Integer
Var minutes As Integer
Var seconds As Integer
Var tz As String
For i As Integer = 0 To bits.LastIndex
Select Case bits(i)
Case "Sun", "Sunday", "Mon", "Monday", "Tue", "Tuesday", "Wed", "Wednesday", "Thu", "Thursday", "Fri", "Friday", "Sat", "Saturday"
dayName = bits(i)
Case "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
monthName = bits(i)
Else
If (bits(i).IndexOf(":") >= 0) Then
timestr = bits(i)
Var parts() As String = timestr.Split(":")
hrStr = parts(0)
minStr = parts(1)
secStr = parts(2)
Elseif (bits(i).IndexOf("-") >= 0) Then
// style 2 date with - between day mon year ?
Var parts() As String = bits(i).Split("-")
dayStr = parts(0)
monthName = parts(1)
yearStr = parts(2)
Elseif dayStr = "" Then
dayStr = bits(i)
Elseif yearStr = "" Then
yearStr = bits(i)
Else
tz = bits(i)
End If
End Select
Next
If (yearStr.Length <= 2) Then
If Integer.FromString(yearStr) < 50 Then
yearStr = "20" + yearStr
Else
yearStr = "19" + yearStr
End If
End If
year = Integer.FromString(yearStr)
Select Case monthName
Case "Jan"
month = 1
Case "Feb"
month = 2
Case "Mar"
month = 3
Case "Apr"
month = 4
Case "May"
month = 5
Case "Jun"
month = 6
Case "Jul"
month = 7
Case "Aug"
month = 8
Case "Sep"
month = 9
Case "Oct"
month = 10
Case "Nov"
month = 11
Case "Dec"
month = 12
End Select
day = Integer.FromString(dayStr)
hour = Integer.FromString(hrStr)
minutes = Integer.FromString(minStr)
seconds = Integer.FromString(secStr)
Var gmtTZ As New TimeZone(0)
Var tmpDt As New DateTime(year, month, day, hour, minutes, seconds, 0, gmtTZ)
Return tmpDt
End Function
modify as you wish.
SecondsFrom1970 includes nanoseconds but using ToString/FromString only use seconds, no?
1 Like
Nope. It stores just seconds from 1970, not nanoseconds from 1970. The Object DateTime stores Nanoseconds from the constructor, I guess in a property apart.
Seconds from 1970 and a decimal part that includes nanoseconds, no? Your post made me think that if I do dt.SecondsFrom1970 I always get a double with .0
Easy to know. Let me check.
Maybe a difference between Windows and Mac, for example this line of code:
dt = dt.FromString("2022-02-1 12:34:56.013456")
I get:
An exception of class InvalidArgumentException was not handled. The application must shut down.
Exception Message: Parse error: date needs to be in the format of YYYY-MM-DD HH:MM or YYYY-MM-DD
Your day is missing a zero
1 Like
Looks like ignoring nanoseconds
Interesting that it doesn’t change the result (fixing the 0). Interesting too that without the decimals and without the 0 (for 01) no complains:
Are you using Xojo 2022r4.1+ or an earlier one?
On Windows and the current Xojo it parses and ignores the nanoseconds
I guess that is a limit in how .ToString works, please change to something that adds format, like:
.ToString("###########.##########")
to get the MessageBox
2022r4.1 mac
What a mess. Completely unreliable and inconsistent.
I have also needed this requirement and I’ve posted the code I use at https://forum.xojo.com/t/precise-datetime-from-database/74481.
I need to go. But ToString() should return the most complete and correct value possible to the last decimal.
All I can say is: don’t use the current implementation of nanoseconds, make your own.
Var now As DateTime = DateTime.Now
Var value As String = now.SecondsFrom1970.ToString(“0.00000000000”)
and you’ll get data
you can do now.SecondsFrom1970 * 1000 and store into uint64 and revert back once needed.