Re: [sqlite] Ticks to unixepoch date
Thanks for your answer. Please find a test database in the attachment. As you can see I have 1 table with 10 records, and the columns StartDate and EndDate represent a DateTime field, where the values are saved as ticks. There is a view that tries to convert the ticks into unixepoch values, but I receive a floating point error. Can you please take a look at it? Best Regards Roberto Dalmonte ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 / 100) - 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 / 100) - 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] Ticks to unixepoch date
In theory it should be possible to do it right now using the following syntax ... SELECTdatetime((columnAsTicks / 100) - 186796800, 'unixepoch') AS Expr1 FROMTable ...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] SELECT TicksToDateTime(AppointmentStart)
Hi, I'm a C# developer and happy user of a SQLite database. I use System.Data.SQLite dll and I'm saving datetime data asSystem. DateTime.Ticks and I'm very happy with it. The only thing I would like to have is something similar to the existing SELECT datetime(1092941466, 'unixepoch'); I would need something like: SELECT TicksToDateTime(AppointmentStart) where AppointmentStart is an ipothetical Table Column defined as integer in SQLITE (System.Int64 in .Net). This way I could build a view to look my appointment table in a human readable format. Is there such a function or is there a way to build it? Best Regards Roberto Dalmonte ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
