8

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!

5 Answers5

15

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

Peter
  • 14,221
  • 15
  • 70
  • 110
Menzi
  • 328
  • 2
  • 12
10

Assuming that START_TIME is the standard seconds-since-Unix-epoch, you can use:

select datetime(MyBigIntColumn, 'unixepoch');

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

Ian Kemp
  • 28,293
  • 19
  • 112
  • 138
2

It's hundreds of nanoseconds (1 ns = 1/10^9 second) from the begining of Common Age (CA) or Christmas. To convert date from this format to normal date:

1. First you need to convert 633869892000000000=YourDateHundredsOfNanoSecondsCA of 100'nanoseconds to Unixepoch format (seconds since 1st of January 1970 till Your Date) by 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 - round count of days from Jeasus born till January, 1 1970.

1.2 HundredsOfNanoSecondsOf1970Years=DaysOf1970Years*24*60*60*1E9/100 - hundreds of nanoseconds from the begining of Common Age till the begining of Unix Epoch (1st of January 1970).

1.3 YourDateInUnixEpochSeconds=(YourDateHundredsOfNanoSecondsCA-HundredsOfNanoSecondsOf1970Years)*100/1E9 - count of seconds from 01/01/1970 till Your Date.

2. Convert Unix Epoch format date to readable format by function (I give example for SQLite):

SELECT datetime(YourDateInUnixEpochSeconds, 'unixepoch');

For your example you can copy this string to SQL query editor 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 will get '2010-12-19 17:00:00'

Alekzander
  • 866
  • 3
  • 12
  • 12
  • For me this statement - SELECT datetime((DateColumn-(ROUND(1969*(365+1/4-1/100+1/400),0)-2)*24*60*60*1E9/100)*100/1E9, 'unixepoch') from YourTable; - built the dates with one year ahead. But this second statement suggested by @Gizboy seem to have worked fine (and it's also a bit more clean) - select datetime(DateColumn/10000000 - 62135596800, 'unixepoch') from YourTable; Thank you both anyways! – leoneboaventura Jul 28 '17 at 12:07
2

This date time is stored as Ticks. Following link covers bit more details about ticks.

DateTime.Ticks Property

To convert Ticks into date time, you can use SQL query as

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

Ticks can be converted into DateTime as below in c sharp.

DateTime dt = new DateTime(633869892000000000); //output: 2009-08-27​T17:00:00
Shoaib
  • 81
  • 4
-1

This solution only give me a column of empty values:

select datetime(timestmp, 'unixepoch') from logging_event

Leif Hyrne
  • 1