@ Jean-Christophe Deschamps and Roger Andersson
Thanks A LOT :-) That is exactly what I was looking for.
I do appreciate your help.
Txs again
bernie
----------------------------------------------------------------------
13. SELECT question (computing day of week the using strftime()
function) (Bernie Reiter)
14. Re: SELECT question (computing day of week the using
strftime() function) (Jean-Christophe Deschamps)
16. Re: SELECT question (computing day of week the
usingstrftime() function) (Roger Andersson)
----------------------------------------------------------------------
Message: 13
Date: Sat, 1 May 2010 21:54:37 +0000 (GMT)
From: Bernie Reiter <[email protected]>
Subject: [sqlite] SELECT question (computing day of week the using
strftime() function)
To: [email protected]
Message-ID: <[email protected]>
Content-Type: text/plain; charset=utf-8
Dear List Members,
I do need your wisdom and experience. Thank you very much for sharing these
with me !
I have a table into which I import data records from an external source.
These imported data records are all containing a date field.
I can change the field definitions in this table without any problems.
I need to find out how many specific weekdays (e.g., how many Sundays) I have
in any given range of dates.
My problem: How to use the COUNT function in combination with the strftime()
function.
$ sqlite3 test.db3
SQLite version 3.6.20
sqlite> create table test (date VARCHAR(20), money INTEGER);
sqlite> INSERT INTO "test" VALUES('2007-07-20', 1000);
sqlite> INSERT INTO "test" VALUES('2007-07-21', 2100);
sqlite> INSERT INTO "test" VALUES('2007-07-22', 2200);
sqlite> SELECT date from test
...> WHERE date BETWEEN '2007-07-20' AND '2007-07-22';
2007-07-20
2007-07-21
2007-07-22
sqlite> SELECT COUNT(date) FROM test
...> WHERE date BETWEEN '2007-07-20' AND '2007-07-22';
3
sqlite> SELECT COUNT(date) FROM test
...> WHERE date = '2007-07-22';
1
sqlite> SELECT strftime('%w', date) FROM test
...> WHERE date BETWEEN '2007-07-20' AND '2007-07-22';
5
6
0
=> BERNIE'S COMMENT: 2007-07-22 is a Sunday <=
sqlite> SELECT strftime('%w', date) FROM test
...> WHERE date = '2007-07-22';
0
Perfect: I have 3 data records, only one of which is a Sunday.
I can see this in this easy example immediately, but does SQLite too?
I can import the result list of "SELECT strftime('%w', date) FROM test ..."
into awk to do the counting there,
but maybe there is a nice SQL way...
I have tried to construct a SELECT statement to count how many Sundays I have
in my data records.
I have tried many variantions of (sub)selects but I didn't get anywhere.
I don't want to "overflow" my posting by listing them all. I will show only 3
as examples which I used to check my SELECT statements. Here in these 3
examples I have not yet used the COUNT function...
sqlite> SELECT date AS XXX FROM test
...> WHERE date BETWEEN '2007-07-20' AND '2007-07-22'
...> AND (strftime('%w', XXX)) = 0;
sqlite> SELECT date AS XXX FROM test
...> WHERE XXX BETWEEN '2007-07-20' AND '2007-07-22'
...> AND (strftime('%w', date)) = 0;
sqlite> SELECT date AS XXX FROM test
...> WHERE XXX BETWEEN '2007-07-20' AND '2007-07-22'
...> AND date = (SELECT date FROM test WHERE (strftime('%w', date)) = 0);
Unfortunately, in all 3 examples the result of my SELECT statements is empty.
I have then enclosed the 0 for Sunday in single quotes:
sqlite> SELECT date AS XXX FROM test
...> WHERE XXX BETWEEN '2007-07-20' AND '2007-07-22'
...> AND date = (SELECT date FROM test WHERE (strftime('%w', date)) = '0');
2007-07-22
But my whole SELECT looks clumsy.
Any suggestions?
And I have no clue why I should enclose the integer result of a function (i.e.,
here 0) in quotes...
Thanks again for your help and do enjoy your weekend :-)
bernie
------------------------------
Message: 14
Date: Sun, 02 May 2010 01:50:08 +0200
From: Jean-Christophe Deschamps <[email protected]>
Subject: Re: [sqlite] SELECT question (computing day of week the using
strftime() function)
To: General Discussion of SQLite Database <[email protected]>
Message-ID: <[email protected]>
Content-Type: text/plain; charset="us-ascii"; format=flowed
>I need to find out how many specific weekdays (e.g., how many Sundays)
>I have in any given range of dates.
>My problem: How to use the COUNT function in combination with the
>strftime() function.
>
>$ sqlite3 test.db3
>SQLite version 3.6.20
>sqlite> create table test (date VARCHAR(20), money INTEGER);
>sqlite> INSERT INTO "test" VALUES('2007-07-20', 1000);
>sqlite> INSERT INTO "test" VALUES('2007-07-21', 2100);
>sqlite> INSERT INTO "test" VALUES('2007-07-22', 2200);
That will do:
select count(*) from test where date between '2007-07-20' and
'2007-07-22' and strftime('%w', date) = '0';
This will select and count, not compute anything.
------------------------------
Message: 16
Date: Sun, 2 May 2010 07:24:38 +0200
From: "Roger Andersson" <[email protected]>
Subject: Re: [sqlite] SELECT question (computing day of week the
usingstrftime() function)
To: "'General Discussion of SQLite Database'"
<[email protected]>
Message-ID: <94ae5b7b75dd46b4820308ee032d8...@prescot>
Content-Type: text/plain; charset="us-ascii"
> I need to find out how many specific weekdays (e.g., how many
> Sundays) I have in any given range of dates.
> My problem: How to use the COUNT function in combination with
> the strftime() function.
>
Maybe something like
sqlite3 test.db
SQLite version 3.6.23
sqlite> CREATE TABLE test (date TEXT, money INTEGER);
sqlite> INSERT INTO test VALUES('2007-07-20', 1000);
sqlite> INSERT INTO test VALUES('2007-07-21', 2100);
sqlite> INSERT INTO test VALUES('2007-07-22', 2200);
sqlite> INSERT INTO test VALUES('2007-07-27', 7000);
sqlite> INSERT INTO test VALUES('2007-07-28', 2800);
sqlite> INSERT INTO test VALUES('2007-07-29', 2900);
sqlite> INSERT INTO test VALUES('2007-07-22', 9200);
sqlite> SELECT strftime('%w', date) weekday, count(*) cnt FROM test GROUP BY
weekday;
weekday|cnt
0|3
5|2
6|2
------------------------------
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
End of sqlite-users Digest, Vol 29, Issue 2
*******************************************
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
