On 4/5/2011 1:43 PM, Eike Jordan wrote:
Hello Christian,
could u perhaps give an example of the new syntax based on the query from
mike?
Sure, see below.
thx in advance
~eike
On 02/08/2011 09:12 AM, Christian Boos wrote:
On 2/8/2011 7:42 AM, mike_32 wrote:
Hello!
Recently our Trac was upgraded to 0.12.1 version. After that our custom
reports that contain strftime('%s','YYYY-MM-DD') are not working
anymore.
For example:
.... AND TC.time between strftime('%s','2011-01-31') and
strftime('%s','2011-02-05')
When removing those lines, reports give results. Other way reports
don't
find any tickets.
Any ideas ?
Not sure what "TC" refers to in the above, but in general time values
are now stored as *microseconds* since epoch, no
longer as seconds. This might explain your problem.
For example, starting with:
sqlite> create temp table tc ( time integer );
And after inserting a few entries:
sqlite> insert into tc (time) values (strftime('%s', '2011-02-02'));
sqlite> insert into tc (time) values (strftime('%s', '2011-02-03'));
sqlite> insert into tc (time) values (strftime('%s', '2011-03-03'));
We have the initial content:
sqlite> select * from tc;
1296604800
1296691200
1299110400
The old query gives:
sqlite> select * from tc
where tc.time
between strftime('%s','2011-01-31')
and strftime('%s','2011-02-05');
1296604800
1296691200
Now converting similar to what Trac 0.11 -> Trac 0.12 db upgrade:
sqlite> update tc set time = time * 1000000;
sqlite> select * from tc;
1296604800000000
1296691200000000
1299110400000000
New query gives:
sqlite> select * from tc
where tc.time
between strftime('%s','2011-01-31') * 1000000
and strftime('%s','2011-02-05') * 1000000;
1296604800000000
1296691200000000
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
--
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.
