Re: [sqlite] sqlite web site performance
* w. wg: > The documents on sqlite official site says that sqlite can works fine > if a site's traciffic is under 10 click per day. But I found a > lot of sites ( including IBM developer network) say the number is only > 1 per day. Your are probably confusing the number of hits and visitors. I'm sure the IBM developer network has more than 10,000 hits per day. Given the number of individual pages, search engines alone will generate more traffic than that. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] calculate age
Can confirm now that mine didn't work and yours does.
Nice work!
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 22 December 2006 19:17
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] calculate age
RB Smissaert wrote:
> I think I got it now:
>
> select
> (date('now') - '2002-12-22') -
> ((julianday('now', '-' ||
> (date('now') - '2002-12-22') ||
> 'year') <
> julianday('2002-12-22')))
>
> seems to work.
>
>
>
This is either clever or foolhardy depending upon how you look at it.
It works only because sqlite stops at the first illegal character (the
-) when converting a string to an integer as it tries to subtract the
two date stings.
(date('now') - '2002-12-22')
becomes
'2006-12-22' - '2002-12-22'
which is converted to
2006 - 2002
without any warning or error and produces the correct result 4
Other than that it uses the reverse logic of my case statement. Get the
difference of the years and subtract 1 or 0 depending upon the actual date.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] calculate age
Yes, a function like that would be useful.
Unless I am mistaken I would have to wait though till the maker of my VB
wrapper would incorporate it.
RBS
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 22 December 2006 19:26
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] calculate age
"RB Smissaert" <[EMAIL PROTECTED]> wrote:
> Is there a way to calculate the age given the 2 dates in the standard
format
> -mm-dd? I could do julianday('now') - julianday(dateofbirth) and
divide
> by 365, but that won't be accurate enough.
> It would be easy to calculate the age in the application and update the
> database, but I prefer to do it all in SQL if I can.
>
Suppose there were a MONTHSPAN() function that took two dates and
returns the number of months between them. You could then compute
the age by dividing monthspan() by 12.0 and taking the integer part.
I took a stab at writing a monthspan() function. Sometimes the
results seem a bit unusual:
SELECT monthspan('2007-01-01','2006-01-01') -> 12.0 OK
SELECT monthspan('2006-03-01','2006-01-01') -> 2.0 OK
SELECT monthspan('2006-03-31','2006-01-31') -> 2.0 OK
SELECT monthspan('2006-04-01','2006-02-01') -> 2.0 OK
SELECT monthspan('2006-04-30','2006-02-28') -> 2.064516 H
The algorithm causes the result to jump up to the next integer
value as you pass the anniversary date of each month. This leads
to some seemingly strange results on shorter months. But I
suppose you are always going to get that when you are trying
to do difference calculations on months of differing lengths.
Here is the code. I have not checked it in. I'm not sure it
is such a good idea.
RCS file: /sqlite/sqlite/src/date.c,v
retrieving revision 1.58
diff -u -r1.58 date.c
--- date.c 25 Sep 2006 18:05:04 - 1.58
+++ date.c 22 Dec 2006 19:25:00 -
@@ -747,6 +747,27 @@
}
/*
+**monthspan( TIMESTRING1, TIMESTRING2 )
+**
+** Return the number of months from the second date to the first
+*/
+static void monthspanFunc(
+ sqlite3_context *context,
+ int argc,
+ sqlite3_value **argv
+){
+ DateTime date1, date2;
+ if( argc==2 && isDate(1, argv, &date1)==0 && isDate(1, &argv[1],
&date2)==0 )
{
+double m1, m2;
+computeYMD(&date1);
+computeYMD(&date2);
+m1 = date1.Y*12.0 + date1.M + (date1.D-1)/31.0;
+m2 = date2.Y*12.0 + date2.M + (date2.D-1)/31.0;
+sqlite3_result_double(context, m1 - m2);
+ }
+}
+
+/*
**strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
**
** Return a string described by FORMAT. Conversions as follows:
@@ -997,6 +1018,7 @@
{ "time", -1, timeFunc},
{ "datetime", -1, datetimeFunc},
{ "strftime", -1, strftimeFunc},
+{ "monthspan", 2, monthspanFunc },
{ "current_time", 0, ctimeFunc },
{ "current_timestamp", 0, ctimestampFunc },
{ "current_date", 0, cdateFunc },
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] calculate age
> This is either clever or foolhardy depending upon how you look at it.
Probably the last, so will test a bit and then maybe switch to your method.
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 22 December 2006 19:17
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] calculate age
RB Smissaert wrote:
> I think I got it now:
>
> select
> (date('now') - '2002-12-22') -
> ((julianday('now', '-' ||
> (date('now') - '2002-12-22') ||
> 'year') <
> julianday('2002-12-22')))
>
> seems to work.
>
>
>
This is either clever or foolhardy depending upon how you look at it.
It works only because sqlite stops at the first illegal character (the
-) when converting a string to an integer as it tries to subtract the
two date stings.
(date('now') - '2002-12-22')
becomes
'2006-12-22' - '2002-12-22'
which is converted to
2006 - 2002
without any warning or error and produces the correct result 4
Other than that it uses the reverse logic of my case statement. Get the
difference of the years and subtract 1 or 0 depending upon the actual date.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] calculate age
"RB Smissaert" <[EMAIL PROTECTED]> wrote:
> Is there a way to calculate the age given the 2 dates in the standard format
> -mm-dd? I could do julianday('now') - julianday(dateofbirth) and divide
> by 365, but that won't be accurate enough.
> It would be easy to calculate the age in the application and update the
> database, but I prefer to do it all in SQL if I can.
>
Suppose there were a MONTHSPAN() function that took two dates and
returns the number of months between them. You could then compute
the age by dividing monthspan() by 12.0 and taking the integer part.
I took a stab at writing a monthspan() function. Sometimes the
results seem a bit unusual:
SELECT monthspan('2007-01-01','2006-01-01') -> 12.0 OK
SELECT monthspan('2006-03-01','2006-01-01') -> 2.0 OK
SELECT monthspan('2006-03-31','2006-01-31') -> 2.0 OK
SELECT monthspan('2006-04-01','2006-02-01') -> 2.0 OK
SELECT monthspan('2006-04-30','2006-02-28') -> 2.064516 H
The algorithm causes the result to jump up to the next integer
value as you pass the anniversary date of each month. This leads
to some seemingly strange results on shorter months. But I
suppose you are always going to get that when you are trying
to do difference calculations on months of differing lengths.
Here is the code. I have not checked it in. I'm not sure it
is such a good idea.
RCS file: /sqlite/sqlite/src/date.c,v
retrieving revision 1.58
diff -u -r1.58 date.c
--- date.c 25 Sep 2006 18:05:04 - 1.58
+++ date.c 22 Dec 2006 19:25:00 -
@@ -747,6 +747,27 @@
}
/*
+**monthspan( TIMESTRING1, TIMESTRING2 )
+**
+** Return the number of months from the second date to the first
+*/
+static void monthspanFunc(
+ sqlite3_context *context,
+ int argc,
+ sqlite3_value **argv
+){
+ DateTime date1, date2;
+ if( argc==2 && isDate(1, argv, &date1)==0 && isDate(1, &argv[1], &date2)==0 )
{
+double m1, m2;
+computeYMD(&date1);
+computeYMD(&date2);
+m1 = date1.Y*12.0 + date1.M + (date1.D-1)/31.0;
+m2 = date2.Y*12.0 + date2.M + (date2.D-1)/31.0;
+sqlite3_result_double(context, m1 - m2);
+ }
+}
+
+/*
**strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
**
** Return a string described by FORMAT. Conversions as follows:
@@ -997,6 +1018,7 @@
{ "time", -1, timeFunc},
{ "datetime", -1, datetimeFunc},
{ "strftime", -1, strftimeFunc},
+{ "monthspan", 2, monthspanFunc },
{ "current_time", 0, ctimeFunc },
{ "current_timestamp", 0, ctimestampFunc },
{ "current_date", 0, cdateFunc },
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] calculate age
Yes, thanks that works.
Will do a bit of testing to see which one is the fastest.
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 22 December 2006 19:05
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] calculate age
RB Smissaert wrote:
> Thanks, it is getting close, but it doesn't quite work.
>
> For example this:
>
> select
> case
> when date('2002-12-22', '+' ||
> strftime('%Y', 'now') - strftime('%Y', '2002-12-22') ||
> ' years') >= date('now')
> then strftime('%Y', 'now') - strftime('%Y', '2002-12-22')
> else strftime('%Y', 'now') - strftime('%Y', '2002-12-22') -1
> end
> as age
>
> Gives 3
>
>
Oops, I needed another set of brackets to ensure correct order of
operations, and I had the comparison wrong (need <= not >=).
This works:
select
case
when date('2002-12-22', '+' ||
(strftime('%Y', 'now') - strftime('%Y', '2002-12-22')) ||
' years') <= date('now')
then strftime('%Y', 'now') - strftime('%Y', '2002-12-22')
else strftime('%Y', 'now') - strftime('%Y', '2002-12-22') -1
end
as age
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] calculate age
RB Smissaert wrote:
I think I got it now:
select
(date('now') - '2002-12-22') -
((julianday('now', '-' ||
(date('now') - '2002-12-22') ||
'year') <
julianday('2002-12-22')))
seems to work.
This is either clever or foolhardy depending upon how you look at it.
It works only because sqlite stops at the first illegal character (the
-) when converting a string to an integer as it tries to subtract the
two date stings.
(date('now') - '2002-12-22')
becomes
'2006-12-22' - '2002-12-22'
which is converted to
2006 - 2002
without any warning or error and produces the correct result 4
Other than that it uses the reverse logic of my case statement. Get the
difference of the years and subtract 1 or 0 depending upon the actual date.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] calculate age
RB Smissaert wrote:
Thanks, it is getting close, but it doesn't quite work.
For example this:
select
case
when date('2002-12-22', '+' ||
strftime('%Y', 'now') - strftime('%Y', '2002-12-22') ||
' years') >= date('now')
then strftime('%Y', 'now') - strftime('%Y', '2002-12-22')
else strftime('%Y', 'now') - strftime('%Y', '2002-12-22') -1
end
as age
Gives 3
Oops, I needed another set of brackets to ensure correct order of
operations, and I had the comparison wrong (need <= not >=).
This works:
select
case
when date('2002-12-22', '+' ||
(strftime('%Y', 'now') - strftime('%Y', '2002-12-22')) ||
' years') <= date('now')
then strftime('%Y', 'now') - strftime('%Y', '2002-12-22')
else strftime('%Y', 'now') - strftime('%Y', '2002-12-22') -1
end
as age
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] calculate age
I think I got it now:
select
(date('now') - '2002-12-22') -
((julianday('now', '-' ||
(date('now') - '2002-12-22') ||
'year') <
julianday('2002-12-22')))
seems to work.
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 22 December 2006 18:12
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] calculate age
RB Smissaert wrote:
> Is there a way to calculate the age given the 2 dates in the standard
format
> -mm-dd? I could do julianday('now') - julianday(dateofbirth) and
divide
> by 365, but that won't be accurate enough.
> It would be easy to calculate the age in the application and update the
> database, but I prefer to do it all in SQL if I can.
>
> RBS
>
>
>
>
-
> To unsubscribe, send email to [EMAIL PROTECTED]
>
-
>
>
>
Try this (untested).
select
case
when date(dob, '+' ||
strftime('%Y', 'now') - strftime('%Y', dob) ||
' years') >= date('now')
then strftime('%Y', 'now') - strftime('%Y', dob)
else strftime('%Y', 'now') - strftime('%Y', dob) - 1
end
as age
from t;
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] calculate age
Thanks, it is getting close, but it doesn't quite work.
For example this:
select
case
when date('2002-12-22', '+' ||
strftime('%Y', 'now') - strftime('%Y', '2002-12-22') ||
' years') >= date('now')
then strftime('%Y', 'now') - strftime('%Y', '2002-12-22')
else strftime('%Y', 'now') - strftime('%Y', '2002-12-22') -1
end
as age
Gives 3
I will try a bit further with my construction.
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 22 December 2006 18:12
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] calculate age
RB Smissaert wrote:
> Is there a way to calculate the age given the 2 dates in the standard
format
> -mm-dd? I could do julianday('now') - julianday(dateofbirth) and
divide
> by 365, but that won't be accurate enough.
> It would be easy to calculate the age in the application and update the
> database, but I prefer to do it all in SQL if I can.
>
> RBS
>
>
>
>
-
> To unsubscribe, send email to [EMAIL PROTECTED]
>
-
>
>
>
Try this (untested).
select
case
when date(dob, '+' ||
strftime('%Y', 'now') - strftime('%Y', dob) ||
' years') >= date('now')
then strftime('%Y', 'now') - strftime('%Y', dob)
else strftime('%Y', 'now') - strftime('%Y', dob) - 1
end
as age
from t;
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] calculate age
This 'hard-coded' SQL does the job:
select
(date('now') - '2002-12-23') -
((julianday('now', '-4year') < julianday('2002-12-23')))
But the trouble is to get the value 4 here.
This value should be date('now') - '2002-12-23'
But I can't get the right syntax to achieve that.
RBS
-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: 22 December 2006 17:03
To: sqlite-users@sqlite.org
Subject: [sqlite] calculate age
Is there a way to calculate the age given the 2 dates in the standard format
-mm-dd? I could do julianday('now') - julianday(dateofbirth) and divide
by 365, but that won't be accurate enough.
It would be easy to calculate the age in the application and update the
database, but I prefer to do it all in SQL if I can.
RBS
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] calculate age
RB Smissaert wrote:
Is there a way to calculate the age given the 2 dates in the standard format
-mm-dd? I could do julianday('now') - julianday(dateofbirth) and divide
by 365, but that won't be accurate enough.
It would be easy to calculate the age in the application and update the
database, but I prefer to do it all in SQL if I can.
RBS
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Try this (untested).
select
case
when date(dob, '+' ||
strftime('%Y', 'now') - strftime('%Y', dob) ||
' years') >= date('now')
then strftime('%Y', 'now') - strftime('%Y', dob)
else strftime('%Y', 'now') - strftime('%Y', dob) - 1
end
as age
from t;
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] calculate age
Is there a way to calculate the age given the 2 dates in the standard format
-mm-dd? I could do julianday('now') - julianday(dateofbirth) and divide
by 365, but that won't be accurate enough.
It would be easy to calculate the age in the application and update the
database, but I prefer to do it all in SQL if I can.
RBS
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] sqlite web site performance
Hi , The documents on sqlite official site says that sqlite can works fine if a site's traciffic is under 10 click per day. But I found a lot of sites ( including IBM developer network) say the number is only 1 per day. Which one is correct ? 1 or 10 ? Thank you. -- WWG - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: Re: [sqlite] Sample dll using
Thanks! Can at whom is small sample on C/С ++? --- jose isaias cabrera <[EMAIL PROTECTED]> wrote: > > http://www.dprogramming.com/enticeaddrbook.html > > > > > - Original Message - > From: "Artem Yankovskiy" <[EMAIL PROTECTED]> > To: > Sent: Thursday, December 21, 2006 7:51 PM > Subject: [sqlite] Sample dll using > > > > Hello! > > Can I get sample source code for using DLL? > > > > > > Вы уже с > Yahoo!? > > > Испытайте > > > > обновленную > > > и > > > улучшенную > > > Yahoo! Почту! > > http://ru.mail.yahoo.com > > > > > - > > To unsubscribe, send email to > [EMAIL PROTECTED] > > > - > > > > > - > To unsubscribe, send email to > [EMAIL PROTECTED] > - > > Best regards, Artem Yankovskiy Вы уже с Yahoo!? Испытайте обновленную и улучшенную Yahoo! Почту! http://ru.mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
