Re: [sqlite] Re: Re: ip2long
Kevin Waterson wrote: This one time, at band camp, John Stanton <[EMAIL PROTECTED]> wrote: Are you actually searching for membership in a class A, B or C network or perhaps a subnet? If you are you can use the dotted format to your advantage. No, The search is for which registry owns the block of IPs the that the IP block belongs to. These are gained from arin, apnic, ripe and friends. Kind regards Kevin I suspect that you might find these blocks to be networks and subnets and consequently identified by the octets in the dotted IP. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: ip2long
This one time, at band camp, John Stanton <[EMAIL PROTECTED]> wrote: > Are you actually searching for membership in a class A, B or C network > or perhaps a subnet? If you are you can use the dotted format to your > advantage. No, The search is for which registry owns the block of IPs the that the IP block belongs to. These are gained from arin, apnic, ripe and friends. Kind regards Kevin -- "Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: ip2long
Kevin Waterson wrote: This one time, at band camp, John Stanton <[EMAIL PROTECTED]> wrote: It looks as if you are looking to see if an address belongs to a particular class B network. Are all your searches done that way? If they are you may be able to use a regular expression to do your search. well, the range could be 256, or 16, or 131072 or 2048 or any other number really. but the IP will always be a valid IPv4 address. Given a valid IPv4 address, I wish to find the range it falls into. eg: SELECT * FROM table WHERE start < 203.7.136.123 AND 203.7.136.123 < (start plus range); I guess the main issue is getting the start value plus the range value. Kind regards Kevin Are you actually searching for membership in a class A, B or C network or perhaps a subnet? If you are you can use the dotted format to your advantage. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: ip2long
This one time, at band camp, John Stanton <[EMAIL PROTECTED]> wrote: > It looks as if you are looking to see if an address belongs to a > particular class B network. Are all your searches done that way? > > If they are you may be able to use a regular expression to do your search. well, the range could be 256, or 16, or 131072 or 2048 or any other number really. but the IP will always be a valid IPv4 address. Given a valid IPv4 address, I wish to find the range it falls into. eg: SELECT * FROM table WHERE start < 203.7.136.123 AND 203.7.136.123 < (start plus range); I guess the main issue is getting the start value plus the range value. Kind regards Kevin -- "Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: ip2long
Kevin Waterson wrote: This one time, at band camp, "Igor Tandetnik" <[EMAIL PROTECTED]> wrote: I don't quite understand. To continue your example, exactly which IP addresses are considered to fall into the range "65536"? How do you figure 1.2.3.4 is in this range, if indeed it is? The begging IP may be 81.163.0.0 with 65536 IP's. This represents a /16 block beginning with 81.163.0.0 Kind regards Kevin It looks as if you are looking to see if an address belongs to a particular class B network. Are all your searches done that way? If they are you may be able to use a regular expression to do your search. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] ip2long
I would break each octet into separate fields then compare octet by octet Kevin Waterson wrote: How can I SELECT an IP within a range? All my IP's are stored as 1.2.3.4 but I need to check if each is within a range of long IP's. Can this be done with SQLite? or do I need some sort of external ip2long() function to throw at it? Kind regards kevin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: Re: ip2long
This one time, at band camp, "Igor Tandetnik" <[EMAIL PROTECTED]> wrote: > ip between '81.163.0.0' and '81.163.999.999' This would require me know the value of the range before selecting. The addittion of the IP plus the range is what gets me. How to go about it? Kind regards Kevin -- "Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Re: ip2long
Kevin Waterson <[EMAIL PROTECTED]> wrote: This one time, at band camp, "Igor Tandetnik" <[EMAIL PROTECTED]> wrote: I don't quite understand. To continue your example, exactly which IP addresses are considered to fall into the range "65536"? How do you figure 1.2.3.4 is in this range, if indeed it is? The begging IP may be 81.163.0.0 with 65536 IP's. This represents a /16 block beginning with 81.163.0.0 Well then, a simple string comparison would work - something like this: ip between '81.163.0.0' and '81.163.999.999' Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: ip2long
This one time, at band camp, "Igor Tandetnik" <[EMAIL PROTECTED]> wrote: > I don't quite understand. To continue your example, exactly which IP > addresses are considered to fall into the range "65536"? How do you > figure 1.2.3.4 is in this range, if indeed it is? The begging IP may be 81.163.0.0 with 65536 IP's. This represents a /16 block beginning with 81.163.0.0 Kind regards Kevin -- "Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: ip2long
The way I am dealing with this is... First convert the IP to a 32bit integer. (In the system it will represented as a 32bit integer) Store it in the database. Perform the comparison operations on this stored data. Thanks, Lloyd On Mon, 2006-12-04 at 22:07 -0500, Igor Tandetnik wrote: > Kevin Waterson <[EMAIL PROTECTED]> wrote: > > This one time, at band camp, "Igor Tandetnik" > > <[EMAIL PROTECTED]> wrote: > > > >> How is the range specified? > > > > > > In the database the IP is 1.2.3.4 and the range is eg: 65536 > > I don't quite understand. To continue your example, exactly which IP > addresses are considered to fall into the range "65536"? How do you > figure 1.2.3.4 is in this range, if indeed it is? > > Igor Tandetnik > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: ip2long
Kevin Waterson <[EMAIL PROTECTED]> wrote: This one time, at band camp, "Igor Tandetnik" <[EMAIL PROTECTED]> wrote: How is the range specified? In the database the IP is 1.2.3.4 and the range is eg: 65536 I don't quite understand. To continue your example, exactly which IP addresses are considered to fall into the range "65536"? How do you figure 1.2.3.4 is in this range, if indeed it is? Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: ip2long
This one time, at band camp, "Igor Tandetnik" <[EMAIL PROTECTED]> wrote: > How is the range specified? In the database the IP is 1.2.3.4 and the range is eg: 65536 Kind regards Kevin -- "Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Deleting rows with duplicate in one field
RB Smissaert <[EMAIL PROTECTED]> wrote: Thanks, that works. Couldn't get it to work with an alias t1: delete from table1 t1 where exists ( select * from table1 t2 where t1.fieldB = t2.fieldB and t1.OID <> t2.OID Any what is wrong with that? It's not a valid syntax for DELETE statement. Also it is a bit slow despite an index of fieldB. Any ideas how to speed this up? The operation is O(n log n) with an index, O(n^2) without. I don't see how it can be done any faster. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: ip2long
Kevin Waterson <[EMAIL PROTECTED]> wrote: How can I SELECT an IP within a range? All my IP's are stored as 1.2.3.4 but I need to check if each is within a range of long IP's. How is the range specified? Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] ip2long
How can I SELECT an IP within a range? All my IP's are stored as 1.2.3.4 but I need to check if each is within a range of long IP's. Can this be done with SQLite? or do I need some sort of external ip2long() function to throw at it? Kind regards kevin -- "Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Deleting rows with duplicate in one field
Thanks, that works. Couldn't get it to work with an alias t1: delete from table1 t1 where exists ( select * from table1 t2 where t1.fieldB = t2.fieldB and t1.OID <> t2.OID Any what is wrong with that? Also it is a bit slow despite an index of fieldB. Any ideas how to speed this up? RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 04 December 2006 23:42 To: SQLite Subject: [sqlite] Re: Deleting rows with duplicate in one field RB Smissaert <[EMAIL PROTECTED]> wrote: > Trying to delete rows in a SQLite table where > One particular field has a duplicate value. > > Tried this query, using a self-join: > > DELETE > FROM > table1 t1 INNER JOIN table1 t2 ON > (t1.fieldB = t2.fieldB) > WHERE > t1.OID <> t2.OID > > But I get an error near t1 delete from table1 where exists ( select * from table1 t2 where table1.fieldB = t2.fieldB and table1.OID <> t2.OID ); Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Deleting rows with duplicate in one field
RB Smissaert <[EMAIL PROTECTED]> wrote: Trying to delete rows in a SQLite table where One particular field has a duplicate value. Tried this query, using a self-join: DELETE FROM table1 t1 INNER JOIN table1 t2 ON (t1.fieldB = t2.fieldB) WHERE t1.OID <> t2.OID But I get an error near t1 delete from table1 where exists ( select * from table1 t2 where table1.fieldB = t2.fieldB and table1.OID <> t2.OID ); Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Deleting rows with duplicate in one field
Trying to delete rows in a SQLite table where One particular field has a duplicate value. Tried this query, using a self-join: DELETE FROM table1 t1 INNER JOIN table1 t2 ON (t1.fieldB = t2.fieldB) WHERE t1.OID <> t2.OID But I get an error near t1 Any suggestion what I am doing wrong here? RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Dealing with dates in the format yyyymmdd
> You might want to check the records for the patients with DOB > before 1900-01-01 Already taken care of that long time ago. As said, I am more familiar with Excel than with SQLite, but catching up. RBS -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: 04 December 2006 21:23 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd RB Smissaert wrote: > I am dealing with a clinical (patients) database and there are only 8 > patients with a date of birth before 1900-02-28, so not a major problem. > Clinical data is all well after that. I will see what the speed penalty is > from doing the when then else and if it is small then apply it to the date > of birth only. Thanks again for getting me on the right track. > > Bart, You might want to check the records for the patients with DOB before 1900-01-01, since these dates can't be expressed as excel date serial numbers (the lowest valid value is 1 or 1900-01-01). Note, that julian day numbers can express dates back to 4700 BC, but Excel can't. The testing needed to fix the dates for excel's bug only applies to dates in the first two month of the year 1900, so it can probably be safely skipped. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Dealing with dates in the format yyyymmdd
RB Smissaert wrote: I am dealing with a clinical (patients) database and there are only 8 patients with a date of birth before 1900-02-28, so not a major problem. Clinical data is all well after that. I will see what the speed penalty is from doing the when then else and if it is small then apply it to the date of birth only. Thanks again for getting me on the right track. Bart, You might want to check the records for the patients with DOB before 1900-01-01, since these dates can't be expressed as excel date serial numbers (the lowest valid value is 1 or 1900-01-01). Note, that julian day numbers can express dates back to 4700 BC, but Excel can't. The testing needed to fix the dates for excel's bug only applies to dates in the first two month of the year 1900, so it can probably be safely skipped. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Dealing with dates in the format yyyymmdd
It is a very common algorithm. From memory it is implemenented in Sqlite, for example. [EMAIL PROTECTED] wrote: can convert to seconds since start of epoch, do the arithmetic, then convert back Don't get you. It would be quite a calculation, with leap years etc. RBS On Sun, Dec 03, 2006 at 05:31:55PM +, RB Smissaert wrote: Still have the problem though how to compare dates in SQLite when the format is the integer mmdd. Maybe I will need some custom SQLite function. What's difficult about comparing integers of the form mmdd? Comparing them is easy: normal integer comparison operators work just fine. Date arithmetic with mmdd integers, OTOH, is more difficult; but you can convert to seconds since start of epoch, do the arithmetic, then convert back. Nico -- - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] is blob compression worth it
What are you using for compression? Have you checked that you get a useful degree of compression on that numeric data? You might find that it is not particularly amenable to compression. Hickey, Larry wrote: I have a blob structure which is primarily doubles. Is there anyone with some experience with doing data compression to make the blobs smaller? Tests I have run so far indicate that compression is too slow on blobs of a few meg to be practical. I get now at least 20 to 40 inserts per second but if a single compression takes over a second, it's clearly not worth the trouble. Does anybody have experience with a compression scheme with blobs that consist of mostly arrays of doubles? Some schemes ( ibsen) offer lightening speed decompression so if the database was primarily used to read, this would be good choice but very expensive to do the compression required to make it. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Dealing with dates in the format yyyymmdd
They are in the same format as Interbase dates. Could there be a schema error and they have been wrongly identified? Can you transfer them to be ing correct date types in Interbase, then you just use very conventional tools for your transfer? [EMAIL PROTECTED] wrote: Well, these fields I am talking about hold date information but they have nil to do with dates in that you can't do any date manipulation on the data. There also are proper date-time fields in the database and they hold something entirely different. The first field (with mmdd) hold a long data type and the second one holds a date data type. RBS The Interbase date type is a simple integer! RB Smissaert wrote: No, these particular fields don't use the IB date type, but a simple integer. I have managed to get the Interbase dll ib_udf.dll working now and that has a substr function that seems to do the job. So now I can make 03-12-2006 type of dates and hopefully SQLite will accept that as a date. In a way it would still be better though to make Excel integer dates as that will save some trouble when dumping data in the sheets. Maybe I could make an Interbase UDF for that. Or maybe the SQLite dates are fine as well when dumped in an Excel sheet. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 04 December 2006 00:41 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd I suspect that you are not using the Interbase date type, which is a 64 bit object encapsulating date and time and having an epoch November 17, 1898. You seem to have some private packed format. Interbase has a rich set of date and time handling functions built in, provided you use the date type. You have two options in your application. The first is to just do with Sqlite what you did with interbase and have your own private date format. The second is to transform your dates into the Sqlite date format. It depends upon your application and reuse of legacy code. RB Smissaert wrote: Don't know that much about it myself, but there are no functions for this that I know of. There are third party UDF's though and that is probably the best way forward. We are still on Interbase 5.6 and I think the latest is 7.5, so that might explain something. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 23:30 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd I know nothing of Interbase, but cannot imagine that it does not have a set of date manipulation functions. RB Smissaert wrote: If I try to convert the integer Interbase date with standard SQL I get something like this: CAST(CAST((E.ADDED_DATE - (CAST(E.ADDED_DATE / 1 AS INT) * 1)) - " & _ "CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS INT) * 1) AS INT) AS INT) || '/' || " & "CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS INT) * 1) / 100 AS INT) || '/' || " & "CAST(E.ADDED_DATE / 1 AS INT) AS VARCHAR(10)) But I haven't got it working yet. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 21:05 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd Is the date function as in: Function Overview Five date and time functions are available, as follows: 1. date( timestring, modifier, modifier, ...) ? Will give that a go. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 20:46 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd Why not use Interbase to transform the date into a character string, like an ISI8601 date or yymmdd, dd-mm- etc and then use the Sqlite date function to insert it into Sqlite? RB Smissaert wrote: It looks like Interbase uses a traditional date format based on an epoch. It just does year * 1 + month * 100 + day I suppose I could something like this in SQL: ((lIBDate - ((lIBDate \ 1) * 1 + ((lIBDate - (lIBDate \ 1) * 1) \ 100) * 100)) + _ (153 * (((lIBDate - (lIBDate \ 1) * 1) \ 100) + _ 12 * ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12) - 3) + 2) / 5 + _ ((lIBDate \ 1) + 4800 - ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) * _ 365 + ((lIBDate \ 1) + 4800 - _ ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) / 4 - 32083) - _ 2415033 Except it looks a mess and it is one day out, I take it due to not declaring variables as long as in my previously posted function. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 20:03 To: sqlite-users@sqlite.org Subject: Re: [sqlite] De
RE: [sqlite] Dealing with dates in the format yyyymmdd
I am dealing with a clinical (patients) database and there are only 8
patients with a date of birth before 1900-02-28, so not a major problem.
Clinical data is all well after that. I will see what the speed penalty is
from doing the when then else and if it is small then apply it to the date
of birth only. Thanks again for getting me on the right track.
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 04 December 2006 18:44
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Dealing with dates in the format mmdd
[EMAIL PROTECTED] wrote:
> Great stuff.
> For your information it needs to be:
> julianday('1899-12-30')
>
>
Yes, that will accomplish the same thing.
There are really two issues, the fact that excel uses 1 (not 0) as the
serial number for 1900-01-01, and the fact that it incorrectly assumes
that 1900 was a leap year and therefore that there was a day 1900-02-29
(the excel date bug).
The real base date is 1900-01-01, but you need to add one to the
difference between the julian day numbers to correct for the base serial
number offset, and you need to add another one for any date after
1900-02-28 to correct for the leap year bug.
select case
when julianday('now') <= julianday('1900-02-28')
then julianday('now') - julianday('1900-01-01') + 1
else julianday('now') - julianday('1900-01-01') + 2
end as excel_date;
You get the same effect if you change the base date from 1900-01-01 to
1899-12-31 or 1899-12-30 (i.e this adds 1 or 2 days).
select case
when julianday('now') <= julianday('1900-02-28')
then julianday('now') - julianday('1899-12-31')
else julianday('now') - julianday('1899-12-30')
end as excel_date;
If all your dates are after 1900-02-28 then you can safely skip the test
and use only the later base date.
select julianday('now') - julianday('1899-12-30') as excel_date;
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Dealing with dates in the format yyyymmdd
[EMAIL PROTECTED] wrote:
Great stuff.
For your information it needs to be:
julianday('1899-12-30')
Yes, that will accomplish the same thing.
There are really two issues, the fact that excel uses 1 (not 0) as the
serial number for 1900-01-01, and the fact that it incorrectly assumes
that 1900 was a leap year and therefore that there was a day 1900-02-29
(the excel date bug).
The real base date is 1900-01-01, but you need to add one to the
difference between the julian day numbers to correct for the base serial
number offset, and you need to add another one for any date after
1900-02-28 to correct for the leap year bug.
select case
when julianday('now') <= julianday('1900-02-28')
then julianday('now') - julianday('1900-01-01') + 1
else julianday('now') - julianday('1900-01-01') + 2
end as excel_date;
You get the same effect if you change the base date from 1900-01-01 to
1899-12-31 or 1899-12-30 (i.e this adds 1 or 2 days).
select case
when julianday('now') <= julianday('1900-02-28')
then julianday('now') - julianday('1899-12-31')
else julianday('now') - julianday('1899-12-30')
end as excel_date;
If all your dates are after 1900-02-28 then you can safely skip the test
and use only the later base date.
select julianday('now') - julianday('1899-12-30') as excel_date;
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Dealing with dates in the format yyyymmdd
Dennis,
Excel dates start from 30 December 1899.
Then there is of course the famous Excel date bug.
Just type in Google: Excel date bug
and you will see what I am talking about.
RBS
> [EMAIL PROTECTED] wrote:
>>> just a normal SQL alias name
>>>
>>
>> Of course, I get it.
>> Haven't got it working though. Still the invalid use of null error.
>>
>>
> Bart,
>
> Here is what I get:
>
> SQLite version 3.3.5
> Enter ".help" for instructions
> sqlite> select
> ...> cast (
>...> julianday(
>...> substr(20061204, 1, 4) || '-' ||
>...> substr(20061204, 5, 2) || '-' ||
>...> substr(20061204, 7, 2)
>...> ) - julianday('1900-01-01')
>...> as integer
>...> ) as excel_date
>...> ;
> 39053
> sqlite>
>
> When I display the value of a cell with the formula =today() as an
> integer it shows 39055. So there seems to be an off by 2 error (or, I
> suspect, two off by one errors). One comes from the fact that excel
> displays a value of zero as the invalid date 1900-01-00. So the minimum
> legal value is 1, and therefore we need to add one to the difference
> between the julianday numbers. This gives the following:
>
> sqlite> select
>...> cast (
>...> julianday(
>...> substr(20061204, 1, 4) || '-' ||
>...> substr(20061204, 5, 2) || '-' ||
>...> substr(20061204, 7, 2)
>...> ) - julianday('1900-01-01') + 1
>...> as integer
>...> ) as excel_date
>...> ;
> 39054
> sqlite>
>
> I can' t account for the other off by one error though. You could, of
> course, just add 2 instead of 1 to get the right date from excel.
>
> HTH
> Dennis Cote
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Dealing with dates in the format yyyymmdd
[EMAIL PROTECTED] wrote:
just a normal SQL alias name
Of course, I get it.
Haven't got it working though. Still the invalid use of null error.
Bart,
Here is what I get:
SQLite version 3.3.5
Enter ".help" for instructions
sqlite> select
...> cast (
...> julianday(
...> substr(20061204, 1, 4) || '-' ||
...> substr(20061204, 5, 2) || '-' ||
...> substr(20061204, 7, 2)
...> ) - julianday('1900-01-01')
...> as integer
...> ) as excel_date
...> ;
39053
sqlite>
When I display the value of a cell with the formula =today() as an
integer it shows 39055. So there seems to be an off by 2 error (or, I
suspect, two off by one errors). One comes from the fact that excel
displays a value of zero as the invalid date 1900-01-00. So the minimum
legal value is 1, and therefore we need to add one to the difference
between the julianday numbers. This gives the following:
sqlite> select
...> cast (
...> julianday(
...> substr(20061204, 1, 4) || '-' ||
...> substr(20061204, 5, 2) || '-' ||
...> substr(20061204, 7, 2)
...> ) - julianday('1900-01-01') + 1
...> as integer
...> ) as excel_date
...> ;
39054
sqlite>
I can' t account for the other off by one error though. You could, of
course, just add 2 instead of 1 to get the right date from excel.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Dealing with dates in the format yyyymmdd
Great stuff.
For your information it needs to be:
julianday('1899-12-30')
RBS
> [EMAIL PROTECTED] wrote:
>> Tried it, but get the error invalid use of null.
>> The field I tried it on had no NULL values.
>> Will figure it out.
>>
>>
> Bart,
>
> I should have tested what I posted first. There is a small problem, the
> julianday function needs a date string in -MM-DD format, but the
> numeric calculations don't insert leading zeros for months and days less
> than 10. Here i sa modified version that does leading zero insertion.
> Even more reason to use the original substring based version.
>
> select
> cast (
> julianday(
> cast(20061204 / 1 as integer) || '-' ||
> substr('00' || (cast(20061204 / 100 as integer) % 100), -2,
> 2) || '-' ||
> substr('00' || (20061204 % 100), -2, 2)
> ) - julianday('1900-01-01')
> as integer
> ) as excel_date
> ;
>
> HTH
> Dennis Cote
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Dealing with dates in the format yyyymmdd
Hi Dennis, Got this working now, great stuff and thanks! Will do some testing to see what is faster and report back here. RBS > [EMAIL PROTECTED] wrote: >> Thanks , will try that. >> What is as excel_date? >> Is this a variable or is this jus plain SQL against SQLite? >> >> > Bart, > > excel_date is just a normal SQL alias name for the complicated > expression. The string 'execl_date' will be returned as the name of this > result column. This name is only used if you look at or display the > column name, like you would if you use the sqlite shell to execute the > command. > > P.S. see my subsequent post after I noticed that your datefield was an > integer raher than a string. Note that this string based version will > also work because sqlite automatically converts the integer datefield > value into a string to pass it to the substr function. > > HTH > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Dealing with dates in the format yyyymmdd
[EMAIL PROTECTED] wrote:
Tried it, but get the error invalid use of null.
The field I tried it on had no NULL values.
Will figure it out.
Bart,
I should have tested what I posted first. There is a small problem, the
julianday function needs a date string in -MM-DD format, but the
numeric calculations don't insert leading zeros for months and days less
than 10. Here i sa modified version that does leading zero insertion.
Even more reason to use the original substring based version.
select
cast (
julianday(
cast(20061204 / 1 as integer) || '-' ||
substr('00' || (cast(20061204 / 100 as integer) % 100), -2,
2) || '-' ||
substr('00' || (20061204 % 100), -2, 2)
) - julianday('1900-01-01')
as integer
) as excel_date
;
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Dealing with dates in the format yyyymmdd
> just a normal SQL alias name Of course, I get it. Haven't got it working though. Still the invalid use of null error. RBS > [EMAIL PROTECTED] wrote: >> Thanks , will try that. >> What is as excel_date? >> Is this a variable or is this jus plain SQL against SQLite? >> >> > Bart, > > excel_date is just a normal SQL alias name for the complicated > expression. The string 'execl_date' will be returned as the name of this > result column. This name is only used if you look at or display the > column name, like you would if you use the sqlite shell to execute the > command. > > P.S. see my subsequent post after I noticed that your datefield was an > integer raher than a string. Note that this string based version will > also work because sqlite automatically converts the integer datefield > value into a string to pass it to the substr function. > > HTH > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Dealing with dates in the format yyyymmdd
> can convert to seconds since start of epoch, do the arithmetic, > then convert back Don't get you. It would be quite a calculation, with leap years etc. RBS > On Sun, Dec 03, 2006 at 05:31:55PM +, RB Smissaert wrote: >> Still have the problem though how to compare dates in SQLite when the >> format >> is the integer mmdd. Maybe I will need some custom SQLite function. > > What's difficult about comparing integers of the form mmdd? > > Comparing them is easy: normal integer comparison operators work just > fine. > > Date arithmetic with mmdd integers, OTOH, is more difficult; but you > can convert to seconds since start of epoch, do the arithmetic, then > convert back. > > Nico > -- > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Dealing with dates in the format yyyymmdd
Tried it, but get the error invalid use of null.
The field I tried it on had no NULL values.
Will figure it out.
RBS
> RB Smissaert wrote:
>> When moving data from Interbase to SQLite I have to convert integer
>> dates in
>> the format mmdd to Excel dates. These are integer numbers counting
>> the
>> days past 31 December 1899. With substr I can make it dd/mm/ (I am
>> in
>> the UK and that is the normal way to format dates) but the problem is it
>> will be displayed in Excel like mm/dd/ if that would be a possible
>> date.
>> This is due to the US date format of Excel.
>> So, would it be possible in SQLite to make a date format like this:
>> dd/mmm/ so that would be 03/dec/2006
>> This would prevent Excel from putting the month first.
>> or alternatively make it the Excel integer date format so the above date
>> would be: 39054
>>
>> I could handle the date formatting in VBA, but I would like to do as
>> much as
>> possible in SQLite as it will be faster and it would keep the code
>> neater.
>> Thanks for any advice.
>>
>>
> I saw later that your datefield was actually stored as an integer value
> (rather than a string as I had assumed) so this should work instead.
>
> select
> cast (
> julianday(
> cast(datefield / 1 as integer) || '-' ||
> (cast(datefield / 100 as integer) % 100) || '-' ||
> (datefield % 100)
> ) - julianday('1900-01-01')
> as integer
> ) as excel_date
> from mytable;
>
> HTH
> Dennis Cote
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Dealing with dates in the format yyyymmdd
On Sun, Dec 03, 2006 at 05:31:55PM +, RB Smissaert wrote: > Still have the problem though how to compare dates in SQLite when the format > is the integer mmdd. Maybe I will need some custom SQLite function. What's difficult about comparing integers of the form mmdd? Comparing them is easy: normal integer comparison operators work just fine. Date arithmetic with mmdd integers, OTOH, is more difficult; but you can convert to seconds since start of epoch, do the arithmetic, then convert back. Nico -- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Dealing with dates in the format yyyymmdd
[EMAIL PROTECTED] wrote: Thanks, will try that. What does the: % 100 do and what the Excel_date ? The % is the modulus operator. It returns the remainder after division by a number. For example, 1234 divided by 100 gives 12 and a remainder of 34. The '/' division operator returns the quotient 12, and the % modulus operator returns the remainder 34. 1234 / 100 => 12 1234 % 100 => 34 In hind sight I think the substr based version is clearer, bu this version may provide higher performance due to ferwer format conversions. You can try both and see if there is any measurable speed difference. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Batching functions
On Sun, Dec 03, 2006 at 11:24:36PM -0600, John Stanton wrote: > How about running a daemon on your machine which gets the request from > your user defined function, does the lookup with a persistent connection > and asynchronously updates the row in the DB? It does not need to be a > daemon, it could be a thread in your program if that is more apprpriate. > > When it has a few requests answered it can deliver them in one transaction. Clearly one can live without this feature. It means splitting what could be a single statement/transaction into two at the very least. > If your async process is fed by a FIFO buffer then netwrok slowness and > glitches will not stop your main process. You can optimize traffic by > not sending partially packets, by accumulating requests until you have a > full packet. There is little value in grouping more than that, but > having a persistent connection is valuable. Timeouts while holding on to a DB lock are probably the biggest problem with batcheable functions, yes, and perhaps the best reason to say no to them. Thanks, Nico -- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Dealing with dates in the format yyyymmdd
[EMAIL PROTECTED] wrote: Thanks , will try that. What is as excel_date? Is this a variable or is this jus plain SQL against SQLite? Bart, excel_date is just a normal SQL alias name for the complicated expression. The string 'execl_date' will be returned as the name of this result column. This name is only used if you look at or display the column name, like you would if you use the sqlite shell to execute the command. P.S. see my subsequent post after I noticed that your datefield was an integer raher than a string. Note that this string based version will also work because sqlite automatically converts the integer datefield value into a string to pass it to the substr function. HTH - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Dealing with dates in the format yyyymmdd
Thanks, will try that.
What does the: % 100 do and what the Excel_date ?
RBS
> RB Smissaert wrote:
>> When moving data from Interbase to SQLite I have to convert integer
>> dates in
>> the format mmdd to Excel dates. These are integer numbers counting
>> the
>> days past 31 December 1899. With substr I can make it dd/mm/ (I am
>> in
>> the UK and that is the normal way to format dates) but the problem is it
>> will be displayed in Excel like mm/dd/ if that would be a possible
>> date.
>> This is due to the US date format of Excel.
>> So, would it be possible in SQLite to make a date format like this:
>> dd/mmm/ so that would be 03/dec/2006
>> This would prevent Excel from putting the month first.
>> or alternatively make it the Excel integer date format so the above date
>> would be: 39054
>>
>> I could handle the date formatting in VBA, but I would like to do as
>> much as
>> possible in SQLite as it will be faster and it would keep the code
>> neater.
>> Thanks for any advice.
>>
>>
> I saw later that your datefield was actually stored as an integer value
> (rather than a string as I had assumed) so this should work instead.
>
> select
> cast (
> julianday(
> cast(datefield / 1 as integer) || '-' ||
> (cast(datefield / 100 as integer) % 100) || '-' ||
> (datefield % 100)
> ) - julianday('1900-01-01')
> as integer
> ) as excel_date
> from mytable;
>
> HTH
> Dennis Cote
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Time formatting
Hi Dennis,
Thanks for the code, made me think of an alternate approach.
Tried out your code and made some changes. Here is the updated version
select strftime('%Y-%m-%dT%H:%M:%f', localtime) ||
case
when (strftime('%s',localtime) - strftime('%s',GMTTime)) > 0
then '+' || strftime('%H:%M', strftime('%s',localtime) -
strftime('%s',GMTTime),'unixepoch')
else '-' || strftime('%H:%M', strftime('%s',GMTTime) -
strftime('%s',localtime),'unixepoch')
end
from twotimes
Works fine for any time zone.
With Regards
Karthick V
"The secret of being miserable is to have leisure to bother about whether
you are happy or not. The cure for it is occupation."
George Bernard Shaw (1856-1950)
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 28, 2006 11:04 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Time formatting
Karthick V - TLS , Chennai wrote:
> Hi everyone,
>
> I am trying to generate a time information with the time zone offset
> information in the UTC format
>
> -MM-DDTHH:MM:SS.mmm+HH:MM
>
>
Try this:
select strftime('%Y-%m-%dT%H:%M:%f', 'now', 'localtime') ||
case
when strftime('%H:%M', '12:00', 'localtime') < '12:00'
then strftime('-%H:%M', '00:00', 'utc')
else strftime('+%H:%M', '00:00', 'utc')
end;
When I run this here I get:
2006-11-28T10:29:51.091-07:00
Which is the correct timezone offset for MST.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
DISCLAIMER
The contents of this e-mail and any attachment(s) are confidential and intended
for the
named recipient(s) only. It shall not attach any liability on the originator or
HCL or its
affiliates. Any views or opinions presented in this email are solely those of
the author and
may not necessarily reflect the opinions of HCL or its affiliates. Any form of
reproduction,
dissemination, copying, disclosure, modification, distribution and / or
publication of this
message without the prior written consent of the author of this e-mail is
strictly
prohibited. If you have received this email in error please delete it and
notify the sender
immediately. Before opening any mail and attachments please check them for
viruses and
defect.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Dealing with dates in the format yyyymmdd
RB Smissaert wrote:
When moving data from Interbase to SQLite I have to convert integer dates in
the format mmdd to Excel dates. These are integer numbers counting the
days past 31 December 1899. With substr I can make it dd/mm/ (I am in
the UK and that is the normal way to format dates) but the problem is it
will be displayed in Excel like mm/dd/ if that would be a possible date.
This is due to the US date format of Excel.
So, would it be possible in SQLite to make a date format like this:
dd/mmm/ so that would be 03/dec/2006
This would prevent Excel from putting the month first.
or alternatively make it the Excel integer date format so the above date
would be: 39054
I could handle the date formatting in VBA, but I would like to do as much as
possible in SQLite as it will be faster and it would keep the code neater.
Thanks for any advice.
I saw later that your datefield was actually stored as an integer value
(rather than a string as I had assumed) so this should work instead.
select
cast (
julianday(
cast(datefield / 1 as integer) || '-' ||
(cast(datefield / 100 as integer) % 100) || '-' ||
(datefield % 100)
) - julianday('1900-01-01')
as integer
) as excel_date
from mytable;
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] is blob compression worth it
Hickey, Larry uttered: I have a blob structure which is primarily doubles. Is there anyone with some experience with doing data compression to make the blobs smaller? No experience with compressing blobs... Tests I have run so far indicate that compression is too slow on blobs of a few meg to be practical. I get now at least 20 to 40 inserts per second but if a single compression takes over a second, it's clearly not worth the trouble. Does anybody have experience with a compression scheme with blobs that consist of mostly arrays of doubles? If you have lots of common values, such as 0.0 or 1.0, then you may get very good compression. However, if the doubles are the result of arbitrary calculations, that may hit the binary representation of your data, so that two values close to 1.0 may actually be 0.99 and 0.9996. The data may compress better if stored as a textual representation, and be more portable to boot. But if the format is out of your hands, that may not be an option. Some schemes ( ibsen) offer lightening speed decompression so if the database was primarily used to read, this would be good choice but very expensive to do the compression required to make it. You don't indicate what compression algorithms you've tried (other than ibsen). You may find LZO an alternative, as it is designed for high performance (run time) compression. It is GPL, but commercial licenses are available. You might also want to try zlib, which may or may not perform better than ibsen, but has liberal licensing. If your blobs are big, and your compression is fast, you may have higher performance due to the reduction in IO. But something like bzip2 will likely reduce your performance too much. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Dealing with dates in the format yyyymmdd
Thanks , will try that.
What is as excel_date?
Is this a variable or is this jus plain SQL against SQLite?
RBS
> RB Smissaert wrote:
>> When moving data from Interbase to SQLite I have to convert integer
>> dates in
>> the format mmdd to Excel dates. These are integer numbers counting
>> the
>> days past 31 December 1899. With substr I can make it dd/mm/ (I am
>> in
>> the UK and that is the normal way to format dates) but the problem is it
>> will be displayed in Excel like mm/dd/ if that would be a possible
>> date.
>> This is due to the US date format of Excel.
>> So, would it be possible in SQLite to make a date format like this:
>> dd/mmm/ so that would be 03/dec/2006
>> This would prevent Excel from putting the month first.
>> or alternatively make it the Excel integer date format so the above date
>> would be: 39054
>>
>> I could handle the date formatting in VBA, but I would like to do as
>> much as
>> possible in SQLite as it will be faster and it would keep the code
>> neater.
>> Thanks for any advice.
>>
>> RBS
>>
>>
> This should give you the excel integer date format directll.
>
> select
> cast (
> julianday(
> substr(datefield, 1, 4) || '-' ||
> substr(datefield, 5, 2) || '-' ||
> substr(datefield, 7,2)
> ) - julianday('1900-01-01')
> as integer
> ) as excel_date
> from mytable;
>
> HTH
> Dennis Cote
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Dealing with dates in the format yyyymmdd
Hi RBS! In my application I use following approach: It is a part of view to export csv data to Excel: CREATE VIEW v_export_data_std AS SELECT localnumber, remotenumber, sipuser, strftime( "%Y", timestart, 'unixepoch', 'localtime' ) AS year, strftime( "%m", timestart, 'unixepoch', 'localtime' ) AS month, strftime( "%d", timestart, 'unixepoch', 'localtime' ) AS day, strftime( "%H", timestart, 'unixepoch', 'localtime' ) AS hour, strftime( "%M", timestart, 'unixepoch', 'localtime' ) AS minute, strftime( "%S", timestart, 'unixepoch', 'localtime' ) AS second, timeend-timestart AS totalduration, timeend-timeconnect AS talkduration -- idscript, idisdn, idsip, state, disconnectreason ... ORDER BY timestart; These time* values are stored in a table with following definition: timestart INTEGER NOT NULL CHECK( timestart > 0 ), timeconnect INTEGER, timeend INTEGER And in most cases the 'timestart' field is set to current time, as a number of second from 1/1/1970 epoch, something like 1154443669. For me this view is good enough to make any type of grouping and comparing in Excel. Regards, Denis -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Sunday, December 03, 2006 8:32 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd > Come to think of it, maybe I should make it mm/dd/ with the SQLite > function substr(). That seems to work fine. So, when I have 20061203, meaning 3 December 2006 then if I make it 12/03/2006 with substr() then Excel picks it up as the right date and I also can apply any date format. Still have the problem though how to compare dates in SQLite when the format is the integer mmdd. Maybe I will need some custom SQLite function. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 17:22 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd I have an integer, but that is mmdd and has nil to do with the Excel integer date format. Did you try putting a value in the integer format mmdd in Excel and have it as a normal Excel date? Or did you try it with a string in the format dd/mm/? Come to think of it, maybe I should make it mm/dd/ with the SQLite function substr. RBS -Original Message- From: Fred Williams [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 17:11 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd Still don't understand the issue. Excel stores the date internally as the integer as you describe, no matter how you chose to format the cell's display. Changing the display format does not change the cell's internal format. And to the best of my knowledge changing Excel's internal cell storage structure is not possible. Fred > -Original Message- > From: RB Smissaert [mailto:[EMAIL PROTECTED] > Sent: Sunday, December 03, 2006 8:56 AM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Dealing with dates in the format mmdd > > > I am familiar with Excel and I have tried that but it doesn't work. I > find the only reliable way to put dates in Excel is to put the integer > Excel date in like for example 39054 and then set the date > format in the > sheet. > > RBS > > -Original Message- > From: Fred Williams [mailto:[EMAIL PROTECTED] > Sent: 03 December 2006 14:41 > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Dealing with dates in the format mmdd > > Is there a reason you can use Excel's "Format Cells" to accomplish > what you wish? Enter a "Custom" format of "\mm\dd" in a cell and > enter "=today()" as a value in that cell. Have not fooled with Excel > much lately, but I think you can even format a spreadsheet > programmatically. > > Fred > > > -Original Message- > > From: RB Smissaert [mailto:[EMAIL PROTECTED] > > Sent: Sunday, December 03, 2006 8:21 AM > > To: sqlite-users@sqlite.org > > Subject: [sqlite] Dealing with dates in the format mmdd > > > > > > When moving data from Interbase to SQLite I have to convert integer > > dates in the format mmdd to Excel dates. These are integer > > numbers counting the > > days past 31 December 1899. With substr I can make it > > dd/mm/ (I am in > > the UK and that is the normal way to format dates) but the > > problem is it > > will be displayed in Excel like mm/dd/ if that would be a > > possible date. > > This is due to the US date format of Excel. > > So, would it be possible in SQLite to make a date format like this: > > dd/mmm/ so that would be 03/dec/2006 > > This would prevent Excel from putting the month first. > > or alternatively make it the Excel integer date format so the > > above date > > would be: 39054 > > > > I could handle the date formatting in VBA,
Re: [sqlite] Dealing with dates in the format yyyymmdd
RB Smissaert wrote:
When moving data from Interbase to SQLite I have to convert integer dates in
the format mmdd to Excel dates. These are integer numbers counting the
days past 31 December 1899. With substr I can make it dd/mm/ (I am in
the UK and that is the normal way to format dates) but the problem is it
will be displayed in Excel like mm/dd/ if that would be a possible date.
This is due to the US date format of Excel.
So, would it be possible in SQLite to make a date format like this:
dd/mmm/ so that would be 03/dec/2006
This would prevent Excel from putting the month first.
or alternatively make it the Excel integer date format so the above date
would be: 39054
I could handle the date formatting in VBA, but I would like to do as much as
possible in SQLite as it will be faster and it would keep the code neater.
Thanks for any advice.
RBS
This should give you the excel integer date format directll.
select
cast (
julianday(
substr(datefield, 1, 4) || '-' ||
substr(datefield, 5, 2) || '-' ||
substr(datefield, 7,2)
) - julianday('1900-01-01')
as integer
) as excel_date
from mytable;
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] is blob compression worth it
hi, i've written a field-based compression using bzip2. my experience: the fields must have at least 50 bytes, or the compressed data is bigger ! cu, gg Hickey, Larry schrieb: I have a blob structure which is primarily doubles. Is there anyone with some experience with doing data compression to make the blobs smaller? Tests I have run so far indicate that compression is too slow on blobs of a few meg to be practical. I get now at least 20 to 40 inserts per second but if a single compression takes over a second, it's clearly not worth the trouble. Does anybody have experience with a compression scheme with blobs that consist of mostly arrays of doubles? Some schemes ( ibsen) offer lightening speed decompression so if the database was primarily used to read, this would be good choice but very expensive to do the compression required to make it. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite3_open (new Win32 thread)
Dixon Hutchinson wrote: I think this is a different question, unrelated to the previous sqlite_open thread. I'm in a WIN32 environment. I'm using: h = CreateFile(path, GENERIC_READ, FILE_SHARE_READ || FILE_SHARED_WRITE, NULL, OPEN_EXISTING, FILE_ATTRIBUTE_NORMAL, NULL); to establish if my DB exists before I open it. If it does exist, I close the handle "h" immediately. I then call sqlite3_open. If the file did not previously exists, I then create my tables. Dioxn, Shouldn't that be: h = CreateFile(, FILE_SHARE_READ | FILE_SHARE_WRITE, ...) With a bitwise OR operator and FILE_SHARE_WRITE not FILE_SHARED_WRITE. You also have a race condition. If another process deletes the file after you close the handle but before sqlite opens it you will end up using an uninitialized database. You should look at using the pragma user_version command to detect if your database has been initialized (search for previous posts). HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] is blob compression worth it
I have a blob structure which is primarily doubles. Is there anyone with some experience with doing data compression to make the blobs smaller? Tests I have run so far indicate that compression is too slow on blobs of a few meg to be practical. I get now at least 20 to 40 inserts per second but if a single compression takes over a second, it's clearly not worth the trouble. Does anybody have experience with a compression scheme with blobs that consist of mostly arrays of doubles? Some schemes ( ibsen) offer lightening speed decompression so if the database was primarily used to read, this would be good choice but very expensive to do the compression required to make it. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Dealing with dates in the format yyyymmdd
Well, these fields I am talking about hold date information but they have nil to do with dates in that you can't do any date manipulation on the data. There also are proper date-time fields in the database and they hold something entirely different. The first field (with mmdd) hold a long data type and the second one holds a date data type. RBS > The Interbase date type is a simple integer! > > RB Smissaert wrote: >> No, these particular fields don't use the IB date type, but a simple >> integer. I have managed to get the Interbase dll ib_udf.dll working now >> and >> that has a substr function that seems to do the job. So now I can make >> 03-12-2006 type of dates and hopefully SQLite will accept that as a >> date. >> In a way it would still be better though to make Excel integer dates as >> that >> will save some trouble when dumping data in the sheets. Maybe I could >> make >> an Interbase UDF for that. Or maybe the SQLite dates are fine as well >> when >> dumped in an Excel sheet. >> >> RBS >> >> >> -Original Message- >> From: John Stanton [mailto:[EMAIL PROTECTED] >> Sent: 04 December 2006 00:41 >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] Dealing with dates in the format mmdd >> >> I suspect that you are not using the Interbase date type, which is a 64 >> bit object encapsulating date and time and having an epoch November 17, >> 1898. You seem to have some private packed format. >> >> Interbase has a rich set of date and time handling functions built in, >> provided you use the date type. >> >> You have two options in your application. The first is to just do with >> Sqlite what you did with interbase and have your own private date >> format. The second is to transform your dates into the Sqlite date >> format. It depends upon your application and reuse of legacy code. >> >> RB Smissaert wrote: >> >>>Don't know that much about it myself, but there are no functions for >>> this >>>that I know of. There are third party UDF's though and that is probably >> >> the >> >>>best way forward. We are still on Interbase 5.6 and I think the latest >>> is >>>7.5, so that might explain something. >>> >>>RBS >>> >>>-Original Message- >>>From: John Stanton [mailto:[EMAIL PROTECTED] >>>Sent: 03 December 2006 23:30 >>>To: sqlite-users@sqlite.org >>>Subject: Re: [sqlite] Dealing with dates in the format mmdd >>> >>>I know nothing of Interbase, but cannot imagine that it does not have a >>>set of date manipulation functions. >>> >>>RB Smissaert wrote: >>> >>> If I try to convert the integer Interbase date with standard SQL I get something like this: CAST(CAST((E.ADDED_DATE - (CAST(E.ADDED_DATE / 1 AS INT) * 1)) - " >>> >>>& >>> _ "CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS INT) * 1) AS INT) AS INT) || '/' || " & "CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS INT) * 1) / 100 AS INT) || '/' || " & "CAST(E.ADDED_DATE / 1 AS INT) AS VARCHAR(10)) But I haven't got it working yet. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 21:05 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd Is the date function as in: Function Overview Five date and time functions are available, as follows: 1. date( timestring, modifier, modifier, ...) ? Will give that a go. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 20:46 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd Why not use Interbase to transform the date into a character string, like an ISI8601 date or yymmdd, dd-mm- etc and then use the Sqlite date function to insert it into Sqlite? RB Smissaert wrote: >>It looks like Interbase uses a traditional date format based on an >> >> epoch. >> > >It just does year * 1 + month * 100 + day > > >I suppose I could something like this in SQL: > >((lIBDate - ((lIBDate \ 1) * 1 + ((lIBDate - (lIBDate \ 1) > * >1) \ 100) * 100)) + _ >(153 * (((lIBDate - (lIBDate \ 1) * 1) > \ >100) + _ >12 * ((14 - ((lIBDate - (lIBDate \ 1) >* 1) \ 100)) / 12) - 3) + 2) / 5 + _ >((lIBDate \ 1) + 4800 - ((14 - >((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) * _ >365 + ((lIBDate \ 1) + 4800 - _ > ((14 - ((lIBDate - (lIBDate \ >1) * 1) \ 100)) / 12)) / 4 - 32083) - _ > 2415033 > >Except it looks a mess and it is one day o
AW: [sqlite] for what reason :memory: is much slower than /dev/s hm/dummy.db
> -Ursprüngliche Nachricht- > Von: John Stanton [mailto:[EMAIL PROTECTED] > Gesendet: Freitag, 1. Dezember 2006 19:59 > An: sqlite-users@sqlite.org > Betreff: Re: [sqlite] for what reason :memory: is much slower than > /dev/shm/dummy.db > > > Eduardo Morras wrote: > > At 09:34 01/12/2006, you wrote: > > > >> Hi there, > >> > >> we are on an challanging project with very high requirements on > >> performance. > >> When doing some debugging we discover, that the sqlite method for > >> creating > >> an memory-based database is much slower than using e.g /dev/shm on > >> linux or > >> /tempfs on solaris. (We have measured an 20min performance > advantage > >> for the > >> /dev/shm style on a batch run which takes 70min with > :memory: and just > >> 49min > >> using /dev/shm. > >> Because our project needs to be ported to windows - the > /dev/shm is > >> not an > >> option - because win2000 does not support any temporary > memory based file > >> system. But beside that, we guess, that there will be a > possiblity to > >> tune > >> :memory: or we belief, that we to something wrong when > using :memory: > >> (for > >> example pragma page_size ...). > >> Is there any body who can give us some advises to tune up > our :memory: > >> database to become as fast as the /dev/shm alternativ? > >> > >> Thanks > >> roland > > > > > > On our project we desisted to use :memory: databases, only > a ram disk > > file system. From time to time make a snapshot to hard disk > or other > > persistent medium. > > > > In windows i suppouse you can make a ram disk using malloc and copy > > there your database file, set the pragma for temporary > files to memory > > and disable journaling. Make a new io routines access based > on windows, > > open/close, write/read etc... for access your memory malloc > ram disk. > > Again, from time to time stop reads/writes to database and > save it to disk. > > > > HTH > > > > > You might find you can get the same performance in a simpler > way by just > disabling synchronous writes. Read Dr Hipp's explanation of a memory > database for the reason. Hi, synchronous writes already disabled. The performance difference occours without synch-writes. thanks roland > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > - To unsubscribe, send email to [EMAIL PROTECTED] -
