Repository navigation
Add display format for C#/.Net DateTime Ticks #2378
Description
A lot of C#/.Net projects use an integer value of "Ticks" in Sqlite as it is fast for queries. But it ends up with numbers like 637108979310830000 which is less that easy to read as a human than "02/12/2019 15:38:51".
There is "simple" code at CodeGuru that does this conversion. It's years since I did C++ myself, so I'm not too confident making a pull request, but hope that this is simple enough that it could be incorporated.
Additional info
Please answer these questions before submitting your feature request.
Is your feature request related to an issue? Please include the issue number.
No
Does this feature exist in another product or project? Please provide a link.
There are converters on the web.
Activity
justinclift commentedon Aug 19, 2020
@chrisjlocke Any idea what a "Tick" is here?
Looking at that code example, it seems related to unix epoch time in some way?
mj2015 commentedon Aug 19, 2020
Microsoft documentation on tick
"A single tick represents one hundred nanoseconds or one ten-millionth of a second. There are 10,000 ticks in a millisecond (see TicksPerMillisecond) and 10 million ticks in a second.
The value of this property represents the number of 100-nanosecond intervals that have elapsed since 12:00:00 midnight, January 1, 0001 in the Gregorian calendar, which represents MinValue. It does not include the number of ticks that are attributable to leap seconds. If the DateTime object has its Kind property set to Local, its ticks represent the time elapsed time since 12:00:00 midnight, January 1, 0001 in the local time as specified by the current time zone setting. If the DateTime object has its Kind property set to Utc, its ticks represent the time elapsed time since 12:00:00 midnight, January 1, 0001 in the Coordinated Universal Time. If the DateTime object has its Kind property set to Unspecified, its ticks represent the time elapsed time since 12:00:00 midnight, January 1, 0001 in the unknown time zone."
justinclift commentedon Aug 19, 2020
Cool. That should be fairly straight forward. 😄
Add .NET DateTime.Ticks to date display format
mgrojo commentedon Aug 19, 2020
This display format seems to work:
datetime("your_column" / 10000000 - 62135596800, 'unixepoch')It works for your sample value: 637108979310830000 returns "2019-12-02 15:38:51", so I've added it to the list of predefined display formats. Could you confirm it's working for you in tomorrow's nightly build?
justinclift commentedon Aug 19, 2020
@mgrojo The code change looks simple enough that we could probably cherry-pick it to 3.12.1 too. 😄
That was a nifty "request -> done" time too. 😁
mj2015 commentedon Aug 20, 2020
@mgrojo I can confirm it works as it should. Thank you so very much - I think many people will like this! (I should have asked sooner 8-)
Add .NET DateTime.Ticks to date display format
mj2015 commentedon Mar 10, 2021
@mgrojo I just updated a system to 3.12.0 and it doesn't have this change. Could you explain why it isn't in? I'd really like this on my Linux boxes. Thanks.
chrisjlocke commentedon Mar 10, 2021
3.12.0 came out in Jun 2020 - any changes from there would be in the 3.12.1 release (released in Nov 2020). Additionally, any changes would be in the nightlies, or the code which you can compile yourself (creating a 3.12.99 release).
Hope this makes sense.
mj2015 commentedon Mar 10, 2021
Ah, thanks. Hmm, why is the Ubuntu package on the download page not up to date then I wonder. I suppose I'll have to work out how that gets updated.
chrisjlocke commentedon Mar 10, 2021
Aah, there are some 3.12.1 versions in the 'testing' and nightlies area.
https://launchpad.net/~linuxgndu/+archive/ubuntu/sqlitebrowser-testing
I'm not sure why an 'official' 3.12.1 version isn't available.