Convert Sqlite BigInt to Date

sqlite date bigint
Mel picture Mel · Aug 27, 2009 · Viewed 10.4k times · Source

I have a Sqlite database that I am using as an ado.net job store for my Quartz.net scheduler jobs. In one table, a column called START_TIME is of type big int.

Is there a way to cast or convert a bigint to a date value? I would like to be able to query the database to see which jobs are scheduled at what date/time and a value such as 633869892000000000 is meaningless.

Thanks!

Answer

Menzi picture Menzi · Jan 12, 2017

This seemed to work for me; select datetime(DateColumn/10000000 - 62135596800, 'unixepoch') from YourTable

Credits to ; https://forums.xamarin.com/discussion/54282/sqlite-datetime-convert-to-net-datetime

Related questions

Best way to work with dates in Android SQLite

I'm having some trouble working with dates on my Android application that uses SQLite. I have a couple questions: What type should I use to store dates in SQLite (text, integer, ...)? Given the best way to store dates how do …

Read More
Sqlite convert string to date

I have date stored as string in an sqlite database like "28/11/2010". I want to convert the string to date. Specifically I have to convert lots of string dates between two dates. In postgresql, I use to_date('30/11/2010','dd/…

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More