[sqlite] Fw: Timestamps from different timezones

[Bernie Reiter]

Hello Christopher,


> You absolutely cannot do this automatically unless you know > the source of 
> each timestamp, any maybe not even then. 
> Just as a f'rinstance, there are at least 3 different meanings > for EST, and 
> 2 of them are in the same country: 
> try -0500, +1000, +1100 for a start.

Thanks for your reply. I was afraid that this would be the answer... 

Thanks again

bernie

>-Original Message-

>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Bernie Reiter
>Sent: Saturday, 23 November, 2013 16:54
>To: sqlite-users@sqlite.org
>Subject: [sqlite] Timestamps from different timezones
>
>Dear List,
>
>I receive data records from various sources. I can't influence their
>formats. These data records are containing datetimestamps like this
>(without the doublequotes):
>
>"06-May-12 18:57:41
 BST"
>"Nov-22-13 22:58:10 PST"
>"23-Nov-13 08:56:57 GMT"
>"22.11.13    00:33:32 MEZ"
>"23-Nov-13 18:57:40 AEDST"
>"23-Nov-13 01:58:10 EST"
>
>I want to automatically convert these datetimestamps into standard UTC.
>
>Currently I am using a "manual approach" by:
>a) converting the datetimestamp into the common format " -MM-
>DDTHH:MM"
>b) reading the time zone code (e.g., BST)
>c) looking-up the timezone indicator for this time zone code (e.g. -
>01:00)
>(see paragraph
 "Formats 2 through 10 may be optionally followed by a
>timezone indicator..."
>on page http://sqlite.org/lang_datefunc.html )
>d) and appending this indicator to an SQL statement.
>
>My SQL statements look like this:
>SELECT datetime('2012-05-06T18:57:41-01:00');
>
>Is there a clever way to convert these datetimestamps into standard UTC ?
>
>
>I don't want to "mess around" with the local timezone of the server
>on which sqlite3 is running. This approach is not portable.
>
>The Unix/Linux 'date' utility is not portable either.
>
>Thanks a lot
>
>bernie
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Timestamps from different timezones

[Bernie Reiter]

Hello Keith,

Thanks for your reply.

> The select returns a timestamp in UTC, not in localtime.  
> If you want localtime you need to specify that:
> SELECT datetime('2012-05-06T18:57:41-01:00', 'localtime');

Yes, I do want timestamps in UTC only. Otherwise the same SELECT would return a 
different value when run on a server located in the UK compared to one located 
in the USA. => Not portable.

> or do you mean something like
> SELECT strftime('%s', '2012-05-06T18:57:41-01:00');
> which returns the number of seconds since the unixepoch?

No, because what is principally the difference when I store a datetime value in 
a SQLite field whether it is expressed as UTC time or unixepoch time based on 
UTC?
My application still would need to remember that this field does contain a 
datetime value based on UTC...

Thanks again

bernie





On Sunday, 24 November 2013, 5:51, Keith Medcalf <kmedc...@dessus.com> wrote:
 
>My SQL statements look like this:
>SELECT datetime('2012-05-06T18:57:41-01:00');
>
>Is there a clever way to convert these datetimestamps into standard UTC ?

The select returns a timestamp in UTC, not in localtime.  If you want localtime 
you need to specify that:

SELECT datetime('2012-05-06T18:57:41-01:00', 'localtime');

or do you mean something like

SELECT strftime('%s', '2012-05-06T18:57:41-01:00');

which returns the number of seconds since the unixepoch?

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Bernie Reiter
>Sent: Saturday, 23 November, 2013 16:54
>To: sqlite-users@sqlite.org
>Subject: [sqlite] Timestamps from different timezones
>
>Dear List,
>
>I receive data records from various sources. I can't influence their
>formats. These data records are containing datetimestamps like this
>(without the doublequotes):
>
>"06-May-12 18:57:41 BST"
>"Nov-22-13 22:58:10 PST"
>"23-Nov-13 08:56:57 GMT"
>"22.11.13    00:33:32 MEZ"
>"23-Nov-13 18:57:40 AEDST"
>"23-Nov-13 01:58:10 EST"
>
>I want to automatically convert these datetimestamps into standard UTC.
>
>Currently I am using a "manual approach" by:
>a) converting the datetimestamp into the common format " -MM-
>DDTHH:MM"
>b) reading the time zone code (e.g., BST)
>c) looking-up the timezone indicator for this time zone code (e.g. -
>01:00)
>(see paragraph
 "Formats 2 through 10 may be optionally followed by a
>timezone indicator..."
>on page http://sqlite.org/lang_datefunc.html )
>d) and appending this indicator to an SQL statement.
>
>My SQL statements look like this:
>SELECT datetime('2012-05-06T18:57:41-01:00');
>
>Is there a clever way to convert these datetimestamps into standard UTC ?
>
>
>I don't want to "mess around" with the local timezone of the server
>on which sqlite3 is running. This approach is not portable.
>
>The Unix/Linux 'date' utility is not portable either.
>
>Thanks a lot
>
>bernie
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Timestamps from different timezones

[Bernie Reiter]

Dear List,

I receive data records from various sources. I can't influence their formats. 
These data records are containing datetimestamps like this (without the 
doublequotes):

"06-May-12 18:57:41 BST"
"Nov-22-13 22:58:10 PST"
"23-Nov-13 08:56:57 GMT"
"22.11.13    00:33:32 MEZ"
"23-Nov-13 18:57:40 AEDST"
"23-Nov-13 01:58:10 EST"

I want to automatically convert these datetimestamps into standard UTC.

Currently I am using a "manual approach" by:
a) converting the datetimestamp into the common format " -MM-DDTHH:MM"
b) reading the time zone code (e.g., BST)
c) looking-up the timezone indicator for this time zone code (e.g. -01:00) 
(see paragraph "Formats 2 through 10 may be optionally followed by a timezone 
indicator..."
on page http://sqlite.org/lang_datefunc.html )
d) and appending this indicator to an SQL statement.

My SQL statements look like this:
SELECT datetime('2012-05-06T18:57:41-01:00');

Is there a clever way to convert these datetimestamps into standard UTC ?


I don't want to "mess around" with the local timezone of the server
on which sqlite3 is running. This approach is not portable.

The Unix/Linux 'date' utility is not portable either.

Thanks a lot

bernie
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT question (computing day of week the using strftime() function)

[Bernie Reiter]

@ 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 + (GMT)
From: Bernie Reiter <bernie_on_the_road_ag...@yahoo.co.uk>
Subject: [sqlite] SELECT question (computing day of week the using
strftime() function)
To: sqlite-users@sqlite.org
Message-ID: <606363.46898...@web25905.mail.ukl.yahoo.com>
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 <j...@q-e-d.org>
Subject: Re: [sqlite] SELECT question (computing day of week the using
strftime() function)
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Message-ID: <7.0.1.0.2.20100502014723.02ac0...@q-e-d.org>
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', 21

[sqlite] SELECT question (computing day of week the using strftime() function)

[Bernie Reiter]

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


  

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 28, Issue 26

[Bernie Reiter]

@jason

I'd like to follow-up Igor's and Michael's suggestion (see their referred-to 
messages below).

How about that:
a) You do a dump of your database table containing the values bob, 
jean-batiste, ...
a1) select a file for your dump:
sqlite> .output FILENAME
a2) Dump the database in an SQL text format
 If TABLE specified, only dump tables matching
 LIKE pattern TABLE.
sqlite> .dump ?TABLE? ...  
sqlite> .quit

I presume that you are working on a *NIX/Linux box.

Testwise grep the line containing 'bob' or any of the other working values in 
your generated dump file
$ grep --version
GNU grep 2.6.3

$ grep -i bob mydump-file.txt

Now grep using 'jean' or any other of your values exluding the dash in 
question. e.g. 'jean' only for  'jean-batiste'
$ grep -i jean mydump-file.txt

DO NOT use an editor to extract the line containing the value of e.g. 
'jean-batiste' as some editors are changing values when saving files. Better 
use grep instead.

If this is working then check your system for either the "hexdump" or the "od" 
(for octal dump) program, e.g.
$ echo a- | hexdump
000 2d61 000a  
003

$ echo a- | od -x
000 2d61 000a
003
In both cases the hex value of the letter 'a' and my dash character '-' was 
shown as 2d61.

$ echo aa | od -x
000 6161 000a
will show you '61' as the hex value of 'a'

$ N.B.: 2 dashes used here in this example, i.e., like "echo dashdash":
echo -- | od -x   
000 2d2d 000a
will show you '2d' as the hex value of '-'

Now look at the man page to work out the offset so that you start "dumping" 
with the last letter before your dash, e.g. n-batiste ... This way you can 
extract the value of your dash from your generated dump file.


If you are not working on a *NIX/Linux box then you might 
a) download grep as part of the GNU text-utils
or
b) download Fedora12 Live and boot it from USB-stick

Otherwise use your generated dump file and upload it using the Online HexDump 
Utility web site at
http://www.fileformat.info/tool/hexdump.htm


Finally use, e.g. 
the Decimal/Hex/Binary/Octal Calculator at 
http://www.indianabiomedical.com/Calculator/hex.html 
or
Online Unit Converters. Common Converters. Numbers Converter
at http://www.translatorscafe.com/cafe/units-converter/numbers/c/


and take it from there.

I did something like that while parsing for German Umlaute in html with awk to 
generate ASCII records for importing into SQLite - rather a RPitA (German 
Umlaute, not awk or SQLite ... :-) 

Cheerio

bernie


--

Message: 16
Date: Sun, 25 Apr 2010 23:28:06 -0400
From: "Igor Tandetnik" 
Subject: Re: [sqlite] values containing dash - not evaluated
To: sqlite-users@sqlite.org
Message-ID: 
Content-Type: text/plain;charset="iso-8859-1"

jason d wrote:
> I believe you misunderstood my problem. Its not that records dont exist. and
> select statement for Bob does work. a select * does display all the data.
> its the names with dashes that dont shows up. and i have 40,000 records.
> any with dashes do not give any result on a pure select statement. but if I
> select on any other column and then work on the resultset it is ok. for
> example I may choose column projectname since it does not have a dash (-) in
> it. The information is clearly there, just its as if it does not equate to
> anything at all.
> 
> SELECT * from Groups WHERE name = 'jean-baptiste' ; zero result.

What does this statement return:

select name, hex(name) from Groups
where name like '%jean%';

My guess is, you either have leading and/or trailing whitespace around the 
value, or the dash in the middle is not U+002D (HYPHEN-MINUS) but some other 
Unicode character that looks like a dash, e.g. U+2013 (EN DASH). The hex dump 
would tell.
-- 
Igor Tandetnik

--

Message: 31
Date: Mon, 26 Apr 2010 06:59:24 -0500
From: "Black, Michael (IS)" 
Subject: Re: [sqlite] values containing dash - not evaluated
To: "General Discussion of SQLite Database" 
Message-ID:
<9e0073162fd25b489a01ad86d92d983e032ed...@xmbil132.northgrum.com>
Content-Type: text/plain; charset="iso-8859-1"

First off confirm it's not a bug with sqlite2:

sqlite> create table Groups (name varchar(10));
sqlite> insert into Groups values('bob');
sqlite> insert into Groups values('jean-baptiste');
sqlite> select * from Groups where name='jean-baptiste';
jean-baptiste

If you don't get a results this way tje sqlite2 is the problem (which I doubt).

Then do an sql .dump of your table.
sqlite> .dump Groups
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE Groups (name varchar(10));
INSERT INTO "Groups" VALUES('bob');
INSERT INTO "Groups" VALUES('jean-baptiste');
COMMIT;

Then you should be able to see the SQL representation of the string and perhaps 
see what your problem is.

I don't know if sqlite2 has 

Re: [sqlite] Computing day of week the using strftime() function - Feature or bug ?

[Bernie Reiter]

Dear all. Sorry! Ashes on my head :-)

Last night it was 3.00 in the morning before I got off my screen ...

Adding single quotes AND actually using sqlite did it (see below).

Txs again for all your help and patience.

bernie

sqlite> select strftime('%w','2010-03-21');
0
sqlite> select strftime('%w','2010-03-22');
1
sqlite> select strftime('%w','2010-03-23');
2
sqlite> select strftime('%w','2010-03-24');
3
sqlite> select strftime('%w','2010-03-25');
4
sqlite> select strftime('%w','2010-03-26');
5
sqlite> select strftime('%w','2010-03-27');
6
sqlite> select strftime('%w','2010-03-28');
0



- Original Message 
From: Nicolas Williams <nicolas.willi...@sun.com>
To: Bernie Reiter <bernie_on_the_road_ag...@yahoo.co.uk>
Cc: sqlite-users@sqlite.org
Sent: Tue, 23 March, 2010 20:29:28
Subject: Re: [sqlite] Computing day of week the using strftime() function - 
Feature or bug ?

On Tue, Mar 23, 2010 at 07:25:56PM +, Bernie Reiter wrote:
> I am checking this for Sunday, March 21st 2010, Monday, 22nd March 2010 and 
> Tuesday, 23nd March 2010:
> 
> Sunday, March 21st 2010:SELECT strftime('%w',2010-03-21); => 6

You need single quotes around the date value.

2010-03-21 == 2010 + -03 + -21 -- surely not what you intended.

Nico
-- 



  

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Computing day of week the using strftime() function - Feature or bug ?

[Bernie Reiter]

Nico, thanks for your quick reply.

I have copied the function strftime('%Y-%m-%d', ...) directly the sqlite 
documentation 
and substituted the ... with my date string.
Unfortunately, your advice doesn't seem to convince my Linux box, 
neither with a straight single quote nor with a "backwards leaning" single quote

[liveu...@localhost ~]$ SELECT strftime('%w','2010-03-21');
bash: syntax error near unexpected token `('


[liveu...@localhost ~]$ SELECT strftime('%w',`2010-03-21`);
bash: syntax error near unexpected token `('

Txs

bernue




- Original Message 
From: Nicolas Williams <nicolas.willi...@sun.com>
To: Bernie Reiter <bernie_on_the_road_ag...@yahoo.co.uk>
Cc: sqlite-users@sqlite.org
Sent: Tue, 23 March, 2010 20:29:28
Subject: Re: [sqlite] Computing day of week the using strftime() function - 
Feature or bug ?

On Tue, Mar 23, 2010 at 07:25:56PM +, Bernie Reiter wrote:
> I am checking this for Sunday, March 21st 2010, Monday, 22nd March 2010 and 
> Tuesday, 23nd March 2010:
> 
> Sunday, March 21st 2010:SELECT strftime('%w',2010-03-21); => 6

You need single quotes around the date value.

2010-03-21 == 2010 + -03 + -21 -- surely not what you intended.

Nico
-- 



  

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Computing day of week the using strftime() function - Feature or bug ?

[Bernie Reiter]

Dear Dr. Hipp,

SQLite is a wonderful tool. Thanks a LOT ! I am working every day with it and 
love it more and more :-)
Only sometimes I am confused. Maybe the community can help or enlighten me.

Thanks again

bernie


1) My environment:
--
[liveu...@localhost ~]$ date
Mon Mar 22 22:12:40 CET 2010

Platform: Fedora V12 64 bit live version on AMD 64bit single core 1800MHz

[liveu...@localhost ~]$ sqlite3 --version
3.6.20

("yum install sqlite" does not update to current version: 2010-Mar-09 - Version 
3.6.23)
Package sqlite-3.6.20-1.fc12.x86_64 already installed and latest version
Nothing to do


2) Excerpts from the sqlite online documentation 
(http://sqlite.org/lang_datefunc.html)
---

Date And Time Functions:


Time Strings

A time string can be in any of the following formats:

   1. -MM-DD
   2. -MM-DD HH:MM
   3. -MM-DD HH:MM:SS 
...

The strftime() function also takes a format string as its first argument. 

FunctionEquivalent strftime()
date(...) strftime('%Y-%m-%d', ...) 

%w  day of week 0-6 with sunday==0 



3) Now me:
--
I am checking this for Sunday, March 21st 2010, Monday, 22nd March 2010 and 
Tuesday, 23nd March 2010:

Sunday, March 21st 2010:SELECT strftime('%w',2010-03-21); => 6
Monday, March 22nd 2010:   SELECT strftime('%w',2010-03-22); => 5
Tuesday, March 23nd 2010:  SELECT strftime('%w',2010-03-23); => 4

Monday, 22nd March 2010 22:12:40 h: 
SELECT strftime('%w',2010-03-22 22:12:40) ; => SQL error near "22": syntax error


Here is the verbatim copy from my command line:

[liveu...@localhost ~]$ sqlite3 
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT strftime('%w',2010-03-21);
6
sqlite> SELECT strftime('%w',2010-03-22);
5
sqlite> SELECT strftime('%w',2010-03-23);
4
sqlite> SELECT strftime('%w',2010-03-24);
3
sqlite> SELECT strftime('%w',2010-03-25);
2
sqlite> SELECT strftime('%w',2010-03-26);
1
sqlite> SELECT strftime('%w',2010-03-27);
0
sqlite> 



  

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users