Convert Sqlite BigInt to Date

Question

I have a Sqlite database that I use as the ado.net job repository for my Quartz.net scheduler jobs. In one table, the START_TIME column is of type large int.

Is there a way to pass or convert bigint to a date value? I would like to be able to query the database to see which tasks are scheduled on which day / time, and a value such as 633869892000000000 does not make sense.

Thanks!

Answer 1

Assuming START_TIME is a standard second from the Unix era, you could use:

 select datetime(MyBigIntColumn, 'unixepoch'); 

See http://www.sqlite.org/lang_datefunc.html , section "Modifiers".

Answer 2

This worked for me; select date and time (DateColumn / 10000000 - 62135596800, 'unixepoch') from YourTable

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

Answer 3

This is hundreds of nanoseconds (1 ns = 1/10 ^ 9 seconds) from the beginning of Common Age (CA) or Christmas. To convert a date from this format to a normal date:

1. First you will need to convert 633869892000000000 = YourDateHundredsOfNanoSecondsCA from 100'nanoseconds to Unixepoch format (seconds from January 1, 1970 to your date) according to the formula:

(YourDateHundredsOfNanoSecondsCA- (ROUND (1969 * (365 + 1 / 4-1 / 100 + 1/400), 0) -2) * 24 * 60 * 60 * 1E9 / 100) * 100 / 1E9,

Where:

1.1 ROUND (1969 * (365 + 1 / 4-1 / 100 + 1/400), 0) -2) = DaysOf1970Years - the round number of days from Jeasus born before January 1, 1970.

1.2 HundredsOfNanoSecondsOf1970Years = DaysOf1970Years * 24 * 60 * 60 * 1E9 / 100 - hundreds of nanoseconds from the beginning of the Common Age to the beginning of the Unix era (January 1, 1970).

1.3. YourDateInUnixEpochSeconds = (YourDateHundredsOfNanoSecondsCA-HundredsOfNanoSecondsOf1970Years) * 100 / 1E9 - the number of seconds from 01/01/1970 to your date.

2. Converting a date in Unix Epoch format for reading in a format by function (I gave an example for SQLite):

SELECT datetime (YourDateInUnixEpochSeconds, 'unixepoch');

As an example, you can copy this line into an SQL query in SQLiteStudio and run:

SELECT datetime ((633869892000000000- (ROUND (1969 * (365 + 1 / 4-1 / 100 + 1/400), 0) -2) * 24 * 60 * 60 * 1E9 / 100) * 100 / 1E9, 'unixepoch ');

and as a result you get '2010-12-19 17:00:00'

Answer 4

This date and time is stored as marks. The following link contains more information on ticks.

DateTime.Ticks Property

To convert ticks to date, you can use SQL query as

 select datetime(START_TIME/10000000 - 62135596800, 'unixepoch') --output 2009-08-27 17:00:00 

Ticks can be converted to DateTime, as shown below before a sharp.

 DateTime dt = new DateTime(633869892000000000); //output: 2009-08-27​T17:00:00