Can you give us the values in your table? This works for me on 3.6.23.1 create table t (i integer); insert into t values(63435596800000000); select datetime((i/1000000)-62135596800,'unixepoch') from t as expr1; 2011-03-13 07:06:40 Michael D. Black Senior Scientist Northrop Grumman Mission Systems
________________________________ From: sqlite-users-boun...@sqlite.org on behalf of Roberto Dalmonte Sent: Tue 7/13/2010 10:20 AM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] Ticks to unixepoch date Thanks for pointing that out Israel. I had the right value (-62135596800) but I mistakenly pasted something else. Nonetheless I get the following error: invalid floating point operation Any idea to make that statement work? Best Regards Roberto Dalmonte Il 13/07/2010 16:46, Israel Lins Albuquerque ha scritto: > Let me understand you... > > if tick is 0 the date id 0001/01/01 12:00:00? > if yes the select do you want is > > SELECT datetime((TICK_VALUE / 1000000) - 62135553600, 'unixepoch') AS Expr1 > > > use that site for help! > http://www.epochconverter.com/ > > > > ----- "Roberto Dalmonte"<rob...@tiscali.it> escreveu: > >> In theory it should be possible to do it right now using the following >> syntax ... >> >> SELECT datetime((columnAsTicks / 1000000) - 186796800, >> 'unixepoch') AS Expr1 >> FROM Table >> >> ...unfortunately it doesn't work, at least the way I tried it. >> >> The operation is the following: >> 1) transforms the ticks in seconds (divide ticks per 1 million); >> 2) subtract the seconds passed from date 0001/01/01 (starting date in >> System.DateTime) to 1970/1/1 (starting date in Unixepoch) >> 3) Use the result with the built-in SQLite function datetime. >> >> This way you could build a view and be able to see a normal date instead >> of ticks. >> >> Any idea? >> Roberto >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
