Use SQLite

[daniel.henry]

For anyone wanting to deal with dates in the SQLite database, @donvnielsen came up with a nice solution here.

@donvnielsen

with Items as (
    select wi.Id WorkItem
         , wi.name Description
         , wi.notes Notes
         , lv.name Task
         , TP.name Property
         , TPV.Text Value
    from WorkItems WI
             left join ListValuePropertyValues tasks on tasks.WorkItemId = WI.Id
             left join ListValues lv on lv.Id = tasks.ListValueId
             left join ListValueProperties LVP on lvp.Id = lv.ListValuePropertyId
             left join TextPropertyValues TPV on TPV.WorkItemId = WI.Id
             left join TextProperties TP on TP.Id = TPV.TextPropertyId
)
select W.*,ts.id
    ,datetime(ts.start/10000000-62135596800, 'unixepoch' ) begin
    ,datetime(ts.End/10000000-62135596800, 'unixepoch' ) end
    ,(ts.start - ts.End)/10000000
from Items W
join TimeSlices TS on WorkItemId = W.WorkItem

Apparently taking the bigint for a date and dividing by 10000000 and then subtracting 62135596800 will give you a Unix timestamp. I also believe taking the bigint for the estimate on an assignment and dividing by 10000000 will also give you total seconds.

On the other hand, if you’re dealing with this from a .NET codebase, you can deal with dates & times and durations of time by just using the constructors for DateTimeOffset and TimeSpan, respectively.

Edit: corrected the constant per @donvnielsen‘s reply below.