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.
