I have an app that stores a timestamp for each change a user makes, so they can revert back to any point on a very granular level. The problem, as you probably guessed, is that the timestamp is stored as GMT TotalSeconds, and will need to continue to be stored this way for at least a few more years. The data is stored in a SQLite database.
Yes, I know I could just stick with the old date forms, but one of the controls I’m using is the Einhugur Date Control, which only deals with DateTime, not the old Date. Well, actually, it can if I use an older version of the plugin, but I’d prefer to move it forward if possible.
I know I can add or subtract 2082830399 from to convert back and forth between .TotalSeconds and .SecondsSince1970, but I’m open to suggestions for a better way to handle all of this. The user just sees the date and time the change was made, and the app can revert one entry or all of them to any point in time. It can also clear out history before whatever date the user chooses. But the data stored in the DB has to stay old-style TotalSeconds for now.
If you don’t want to use the date class at all maybe write these extension methods:
Function DateFromTotalSec(Extends column as DatabaseColumn) As DateTime
Method DateFromTotalSec(Extends column as DatabaseColumn, Assigns aDate As DateTime)
Method TotalSecondsColumn(Extends Row as DatabaseRow, ColumnName as String, Assigns aDate as DateTime )
Function TotalSecondsColumn(Extends Row as DatabaseRow, ColumnName as String) As DateTime
Method TotalSecondsColumnAt(Extends Row as DatabaseRow, Index as integer, Assigns aDate as DateTime )
Function TotalSecondsColumnAt(Extends Row as DatabaseRow, index as String) As DateTime
and maybe also:
Function ToTotalSeconds(Extends theDate as DateTime) As Double
Global Function DateFromTotalSeconds(TotalSec as Double) as Datetime
Storing as GMT is good. It makes the values nice and predictable. When you read them out, you can create the DateTime objects in any time zone you desire.
You cant simply add or subtract a constant value to convert between TotalSeconds and SecondsFrom1970. TotalSeconds is the number of seconds since 1904-01-01 00:00:00 in the Dates time zone! SecondsFrom1970 is always in GMT. So it doesnt matter what time zone your user is in, you can call Var Moment As New DateTime(ValueFromDatabase, TimeZone.Current) and get the correct value.
Using the long-form constructors (that specify the year, month, day, etc as parameters) is the best way to convert between Date and DateTime.
[quote=459617:@Thom McGrath]Storing as GMT is good. It makes the values nice and predictable. When you read them out, you can create the DateTime objects in any time zone you desire.
You cant simply add or subtract a constant value to convert between TotalSeconds and SecondsFrom1970.
[/quote]
Almost that simple… I decided I’m going to keep using API 1.0 for now but store SecondsFrom1970 in DBs to future proof and so wrote these extension methods for Date to make sure I don’t mess up:
[code]Public Function SecondsFrom1970(Extends aDate as Date) as Double
Const SecondsTo1970 as Double = 2082844800.0
Const SecPerHr As Double = 3600.0
Return aDate.TotalSeconds - aDate.GMTOffset*SecPerHr - SecondsTo1970
End Function
Public Sub SecondsFrom1970(Extends aDate as Date, Assigns SecFrom1970 as Double)
Const SecondsTo1970 as Double = 2082844800.0
Const SecPerHr As Double = 3600.0
aDate.TotalSeconds = SecondsTo1970 + SecFrom1970 + aDate.GMTOffset*SecPerHr
End Sub[/code]
In any case he said needs to keep the dates in the DB as TotalSeconds for now. When he mentioned subtraction I assumed he knew to change GMT to 0 on the date instance before before he stored TotalSeconds, and if using the Date Class to set the GMT to 0 before assigning to TotalSeconds to a date instance and then setting GMT back to local time zone.
if he did store his TotalSeconds with a GMT offset of 0 then that would become:
Var Moment As New DateTime(ValueFromDatabase-SecondsTo1970 , TimeZone.Current)
And that likely is best done with a method so as not to forget to do the subtraction, so it might as well be done with extension methods on the DB classes
[quote=459621:@Karen Atkocius]Almost that simple… I decided I’m going to keep using API 1.0 for now but store SecondsFrom1970 in DBs to future proof and so wrote these extension methods for Date to make sure I don’t mess up:
[code]Public Function SecondsFrom1970(Extends aDate as Date) as Double
Const SecondsTo1970 as Double = 2082844800.0
Const SecPerHr As Double = 3600.0
Return aDate.TotalSeconds - aDate.GMTOffset*SecPerHr - SecondsTo1970
End Function
Public Sub SecondsFrom1970(Extends aDate as Date, Assigns SecFrom1970 as Double)
Const SecondsTo1970 as Double = 2082844800.0
Const SecPerHr As Double = 3600.0
aDate.TotalSeconds = SecondsTo1970 + SecFrom1970 + aDate.GMTOffset*SecPerHr
End Sub[/code]
In any case he said needs to keep the dates in the DB as TotalSeconds for now. When he mentioned subtraction I assumed he knew to change GMT to 0 on the date instance before before he stored TotalSeconds, and if using the Date Class to set the GMT to 0 before assigning to TotalSeconds to a date instance and then setting GMT back to local time zone.
if he did store his TotalSeconds with a GMT offset of 0 then that would become:
Var Moment As New DateTime(ValueFromDatabase-SecondsTo1970 , TimeZone.Current)
And that likely is best done with a method so as not to forget to do the subtraction, so it might as well be done with extension methods on the DB classes
Karen[/quote]
Sorry, Im not trying to contradict your advice. More like adding to it. Really, the best way to store a time stamp in a database is in ISO8601 format, complete with time zone, or at least time zone offset. This why I find it so frustrating that SQLDateTime doesnt support the offset, or that DateTime doesnt have a constructor from that format.
Getting back to the issue at hand… I suspect the database is not taking time zone into account at all. If the user never changes time zones, there will never be a problem. But thats a thing that users do. If Im correct, that means it would be necessary to convert from the total seconds In the database into a DateTime in the users local time zone.
[quote=459617:@Thom McGrath]
You cant simply add or subtract a constant value to convert between TotalSeconds and SecondsFrom1970. TotalSeconds is the number of seconds since 1904-01-01 00:00:00 in the Dates time zone! SecondsFrom1970 is always in GMT. [/quote]
If you know that TotalSeconds is in GMT/UTC then there should be no problem just adding the offset to 1970.
Correct, but thats an extra step. As Karens code demonstrates, you can also use GMTOffset to get the correct adjustment. My point was you cant JUST add or subtract a constant amount.
But still, why go through the trouble when you can just be explicit about your intended timestamp? Sure, the code is tedious and boring, but its also much easier to see the intention and verify behavior.
And if the Feedback reports are to be believed, none of this matters because Date to DateTime will become a for free conversion thanks to an added Operator_Convert.
Then there should have been:
A DateTime.FormatStyles.TimeStamp option overload that dies not require any other parameters in ToString
That format should be supported in FromString as an overload :
DateTime.FRomString(the string as String, TimeStamp as boolean)
Where false returns current SQLDateTime (or teh otehrway around
And/Or adding explicit support for ISO8601 format to the DB Classes
That would have been a more comprehensive solution to date handling considering DBs
[quote=459652:@Karen Atkocius]Then there should have been:
A DateTime.FormatStyles.TimeStamp option overload that dies not require any other parameters in ToString
That format should be supported in FromString as an overload :
DateTime.FRomString(the string as String, TimeStamp as boolean)
Where false returns current SQLDateTime (or teh otehrway around
And/Or adding explicit support for ISO8601 format to the DB Classes
That would have been a more comprehensive solution to date handling considering DBs
-karen[/quote]
Both Date and DateTime are missing a LOT of basic functionality.
[quote=459621:@Karen Atkocius]if he did store his TotalSeconds with a GMT offset of 0 then that would become:
Var Moment As New DateTime(ValueFromDatabase-SecondsTo1970 , TimeZone.Current)
And that likely is best done with a method so as not to forget to do the subtraction, so it might as well be done with extension methods on the DB classes[/quote]
Sorry I forgot to mention that I do store all .TotalSeconds values as GMT time zone, converted to/from whatever zone the user is in. What Karen suggested is exactly what I did to get the 2082844800 number.
All of this info is terrific, I’ll be tackling the dates issue later today, it looks like a few conversion methods will make it pretty straightforward.
Function ParseISO8601Date(dateString As String) As Date
// http://www.w3.org/TR/NOTE-datetime
Dim year, month, day, hour, minute, second, fractionOfASecond, gmtOffset As Integer
Dim stream As New BinaryStream( dateString )
year = stream.read( 4 ).val()
If stream.eof Then GoTo done
If stream.read( 1 ) <> "-" Then GoTo invalid
month = stream.read( 2 ).val()
If stream.eof Then GoTo done
If stream.read( 1 ) <> "-" Then GoTo invalid
day = stream.read( 2 ).val()
If stream.eof Then GoTo done
If stream.read( 1 ) <> "T" Then GoTo invalid
hour = stream.read( 2 ).val()
If stream.read( 1 ) <> ":" Or stream.eof Then GoTo invalid
minute = stream.read( 2 ).val()
If stream.read( 1 ) <> ":" Or stream.eof Then GoTo invalid
second = stream.read( 2 ).val()
If stream.eof Then GoTo invalid
If stream.read( 1 ) = "." Then
// we have a fraction of a second
Dim char As String = stream.read( 1 )
While char.asc >= 48 And char.asc <= 58
fractionOfASecond = fractionOfASecond * 10 + char.val()
char = stream.read( 1 )
Wend
End If
stream.position = stream.position - 1
If stream.read( 1 ) = "Z" Then
gmtOffset = 0
Else
// back up again
stream.position = stream.position -1
gmtOffset = stream.read( 3 ).val() * 100
If stream.read( 1 ) <> ":" Or stream.eof Then GoTo invalid
gmtOffset = gmtOffset + stream.read( 2 ).val()
End If
done:
Dim d As New Date()
d.year = year
d.month = month
d.day = day
d.hour = hour
d.minute = minute
d.second = second
d.GMTOffset = gmtOffset / 100
Return d
invalid:
Raise New UnsupportedFormatException
End Function