# Use SQLite

**URL:** <https://discuss.epiforge.com/t/use-sqlite/45>\
**Category:** Extension Gallery\
**Created:** [March 11, 2020, 3:47pm UTC](https://discuss.epiforge.com/t/use-sqlite/45 "2020-03-11T15:47:09Z")\
**Posts on this page:** 1\
**Showing post:** 6

<div class="post-metadata">

**Author:** ![daniel.henry](https://yyz1.discourse-cdn.com/flex031/user_avatar/discuss.epiforge.com/daniel.henry/32/28_2.png) [@daniel.henry](https://discuss.epiforge.com/u/daniel.henry)\
**Post date:** [March 15, 2020, 5:53pm UTC](https://discuss.epiforge.com/t/use-sqlite/45/6 "2020-03-15T17:53:37Z")

</div>

For anyone wanting to deal with dates in the SQLite database, @donvnielsen came up with a nice solution [here](https://discuss.epiforge.com/t/enhancing-time-slice-with-user-property/37/25).

> [@Enhancing Time Slice with User Property](https://discuss.epiforge.com/t/enhancing-time-slice-with-user-property/37/25):
>
> ```auto
> 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](https://docs.microsoft.com/en-us/dotnet/api/system.datetimeoffset?view=netstandard-2.1) and [TimeSpan](https://docs.microsoft.com/en-us/dotnet/api/system.timespan?view=netstandard-2.1), respectively.

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

---

_[View the full topic](https://discuss.epiforge.com/t/use-sqlite/45)._