In a C# project I have a DateTime property. This is being saved to a Sqlite Database as a BigInt. Everything I can find online suggests it’s a unixepoch format, so using an online converter I thought I’d be able to get a human readable format. Unfortunately not!
This is a date in my Sqlite database: 635923872000000000. When I put this into the converter at Epoch Converter the result is Mon, 08 Jul 2171 07:12:00 GMT – I know this isn’t correct as I’m expecting a date around 1st April 2017.
After some random Googling and a zero score answer on StackOverflow I hit upon the golden nugget.
To convert to unixepoch you need to use this formula:
DateColumnValue/10000000 - 62135596800
So for my example it’s:
635923872000000000/10000000 - 62135596800 = 1491087600
If you then paste this into the converter at Epoch Converter the result is Sat, 01 Apr 2017 23:00:00 GMT – exactly as I was expecting.
You shouldn’t need to do this in code as it happens “under the hood” but you may want to run a SQL command to do it when you’re looking at your database, this should do it:
SELECT datetime(dateColumnName/10000000 - 62135596800, 'unixepoch') FROM tableName