I'm not really reading the details in this thread, but this might be a nice site to have bookmarked: http://www.epochconverter.com/
Cheers / Erik On Wed, Apr 6, 2011 at 1:47 PM, Eike Jordan <[email protected]> wrote: > Cool, Thx alot! Not the first time, i ran into that kind of casting problems > ;-) > Something like "typeof(value)" as in javascript whould be really useful. > > Concerning the differences in time handling when upgrading from 0.11.4 to > 0.12.1 > i struggle with two more SQL expressions that don't give the expected > results anymore: > > 1. calculate the difference between "now" and the ticket changetime and > compare it > with a number (of days) > > ((julianday('now') - julianday(changetime, 'unixepoch')) < 31) > > 2. calculate the difference between "now" and a given date (e.g. ticket_due) > > round(julianday('now') - julianday(strftime("%s", ('2011-01-14'), > 'unixepoch')) as days > > I tried out a similar syntax as in report 23 [1] on the edgewall site, but > > (SELECT ((time.now * 1000000 - changetime) / 1000000.0) AS delta > > resulted in an execution error: "Report execution failed: no such column: > time.now" > > Right now, i don't have a clue what i'm missing :-/ > > > [1] http://trac.edgewall.org/report?id=21&format=sql > > On 04/05/2011 07:21 PM, Christian Boos wrote: >> >> There's a catch however, if you wrote the new query that way, like I >> initially tried: >> >> sqlite> select * from tc >> where tc.time / 1000000 >> between strftime('%s','2011-01-31') >> and strftime('%s','2011-02-05'); >> >> (no results!) >> >> But: >> >> sqlite> select * from tc >> where tc.time / 1000000 >> between strftime('%s','2011-01-31') * 1 >> and strftime('%s','2011-02-05') * 1; >> 1296604800000000 >> 1296691200000000 >> >> >> Go figure ;-) That's why we all love SQL, don't we? >> >> Actually I suppose it's an autoconversion issue. With the old scheme, >> conversion from strftime()'s string output to >> integer was done transparently whereas now with bigger integer values it >> seems it needs some help. With an explicit >> cast, it would work as well: >> >> sqlite> select * from tc >> where tc.time / 1000000 >> between cast(strftime('%s','2011-01-31') as integer) >> and cast(strftime('%s','2011-02-05') as integer); >> 1296604800000000 >> 1296691200000000 >> >> Tested with SQLite 3.6.21 and 3.7.4 on Windows. >> >> -- Christian >> >> >> > > -- > Eike Jordan <[email protected]> > > | FIZ CHEMIE BERLIN > | Franklin Str. 11 ------ ,__o > | 10587 Berlin ------ _-\_<, > | ------ (+)/'(+) > | Tel. : 0049-30-39977 214 > > -- > You received this message because you are subscribed to the Google Groups > "Trac Users" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/trac-users?hl=en. > > -- You received this message because you are subscribed to the Google Groups "Trac Users" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/trac-users?hl=en.
