SQLite DateTime format – BigInt to DateTime

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!

An example:

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

Leave a Reply

Your email address will not be published. Required fields are marked *