[sqlite] New INSTR function in 3.7.15.1 DLL/EXE
Discovered a small issue with new INSTR function in both 3.7.15.1 DLL
and Command Line EXE:
Select INSTR('test','blahblahtestblahblah'); returns 0
however
Select INSTR(''blahblahtestblahblah','test'); returns the right answer 9
Either the documentation or function needs to be corrected as
INSTR(X,Y) appear to operate dyslexically as INSTR(Y,X).
http://www.sqlite.org/lang_corefunc.html
instr(X,Y) The instr(X,Y) function finds the first occurrence of
string X within string Y and returns the number of prior characters
plus 1, or 0 if X is nowhere found within Y. Or, if X and Y are both
BLOBs, then instr(X,Y) returns one more than the number bytes prior to
the first occurrence of X, or 0 if X does not occur anywhere within Y.
If both arguments X and Y to instr(X,Y) are non-NULL and are not BLOBs
then both are interpreted as strings. If either X or Y are NULL in
instr(X,Y) then the result is NULL.
Corrected version
instr(X,Y) The instr(X,Y) function finds the first occurrence of
string Y within string X and returns the number of prior characters
plus 1, or 0 if Y is nowhere found within X. Or, if X and Y are both
BLOBs, then instr(X,Y) returns one more than the number bytes prior to
the first occurrence of Y, or 0 if Y does not occur anywhere within X.
If both arguments X and Y to instr(X,Y) are non-NULL and are not BLOBs
then both are interpreted as strings. If either X or Y are NULL in
instr(X,Y) then the result is NULL.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Suggested Improvement of Date Time Function
Suggesting addition of two new modifiers to SQLITE Date Time functions.
Add new modifier(14):javams and modifier(15):javanano similar to
unixepoch modifier but extended to support milliseconds and
nanoseconds.
The javams modifier (14) only works if it immediately follows a
timestring in the D format. This modifier causes the
D to be interpreted not as a Julian day number as it
normally would be, but as JAVA Millisecond Time - the number of
milliseconds since 1970. If the javams modifier does not follow a
timestring of the form D which expresses the number of
milliseconds since 1970 or if other modifiers separate the java
modifier from prior D then the behavior is undefined. Due
to 13 digit precision limitation the javams modifier only works for
dates between 1653-02-10 06:13:21.001 and 2286-11-20 17:46:39.999
(java times of -9 through 9).
The javanano modifier (15) only works if it immediately follows a
timestring in the DDD format. This modifier causes the
DDD to be interpreted not as a Julian day number as it
normally would be, but as JAVA Nano Second Time - the number of
NanoSeconds since 1970. If the javanano modifier does not follow a
timestring of the form DDD which expresses the number
of nanoiseconds since 1970 or if other modifiers separate the java
modifier from prior DDD then the behavior is
undefined. Due to precision limitations imposed by the implementations
use of 64-bit integers the javanano modifier only works for dates
between 1677-09-21 00:12:44.145224192 and 2262-04-11
23:47:16.854775807 (javanano times of -9223372036854775808 through
+9223372036854775807).
These modifier additions will allow easier and seamless support for
storing high resolution times in the database and enhance
functionality for manipulation.
Presently we are able to store and use javams format via concatenation:
javams format
strftime('%s','now')||substr(strftime('%f','now'),-3,3)
strftime('%Y-%m-%d
%H:%M%S',field/1000,'unixepoch')||substr(strftime('%f','field),-3,3)
We do not use javanano format presently but are merely suggesting it
as a possibility for completeness.
javanano format
strftime('%s','now')||substr(strftime('%f','now'),-3,3)||'00'
//'now' limited to millisecond precision
strftime('%Y-%m-%d
%H:%M%S',field/1000,'unixepoch')||substr(strftime('%f','field),-9,9)
JavaMS support is a higher priority than JavaNano.
Any Thoughts or Input on this matter?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggested Improvement of Date Time Function (Larry Brasfield)
On Wed, Dec 19, 2012 at 12:00 PM, sqlite-users-requ...@sqlite.org wrote: Re: Suggested Improvement of Date Time Function (Larry Brasfield) We currently store timestamps in this javams integer format which allows millisecond precision in a fixed 13 character/digit space. Our usage may not be too uncommon as we use this format for storing and graphing financial time series data. I believe FIX 40 messages are millisecond precise but some HFT data streams have microsecond resolution. This format feeds some of our java graphing libraries nicely. There are some advantages in performing integer based time manipulations and calculations over the string conversions. Currently we experience a small disconnect between unixepoch (second precision) and the built in string based date time functions that have ms or better precision. I believe it may be of general interest to at minimum have some integer time supported format that provides millisecond precision and matches java time format. Currently we just concatenate the ms onto the unixepoch time... Don't know if this is the best practice or where it may break other things but it works for our purposes. Our adaptation changes the upper and lower boundaries for sqlites unixepoch format as we restrict to 10 digits. The SQL code used to query and present data is kludgy and I'm certain we're not the only ones band-aiding these issues. Hence we opened this topic for discussion... If there is a better way to do this we are all ears. One of the performance issues we run into is grouping time series data. Ideally we would like the ability to group by timestamp/6 for 1 minute resolution, timestamp/1000 for 1 second or change to any frequency without the query creating a btree for the grouping. When we store the timestamp/6 value in an indexed column the groupings use the index and performs extremely well. Any help on creating dynamic grouped time series without creating btrees for grouping or storing the time calculation in an indexed column... if possible would be appreciated. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
