[sqlite] Time and date functions

[Nando]

Thank you all. The solution of Jean-Christophe  is perfect  and thanks to
Igor and Michael I begin to understand the logic of the processing of dates
in SQLite, which is much more powerful than it had expected at first.

sqlite> select * from fechas where fecha between date('2011-12-18','-6
days','weekday 0') and date('2011-12-18','weekday 6');

18|2011-12-18

19|2011-12-19

20|2011-12-20

21|2011-12-21

22|2011-12-22

23|2011-12-23

24|2011-12-24

sqlite> select * from fechas where fecha between date('2011-12-24','-6
days','weekday 0') and date('2011-12-24','weekday 6');

18|2011-12-18

19|2011-12-19

20|2011-12-20

21|2011-12-21

22|2011-12-22

23|2011-12-23

24|2011-12-24

sqlite> select * from fechas where fecha between date('2011-12-21','-6
days','weekday 0') and date('2011-12-21','weekday 6');

18|2011-12-18

19|2011-12-19

20|2011-12-20

21|2011-12-21

22|2011-12-22

23|2011-12-23

24|2011-12-24

Igor... I thought I was faced with a syntax problem, thanks for putting me
on track. ;)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Time and date functions

[Igor Tandetnik]

Nando  wrote:
> I just realized that the "weekday" is not working as and believed, but is
> much easier. I can make simple queries of the form:
> 
> select * from dates WHERE date = date ('2011-12-09 ',' weekday 5 ');
> 
> Or
> 
> select * from dates WHERE date <= date ('2011-12-04 ',' weekday 6 ');
> 
> And they work properly. But I can not use this selection criterion with
> periods defined by the operator "between" using "X> = Y and X <= Z"

You can. But you have to carefully ensure that, in fact, Y <= Z. Otherwise the 
condition won't hold for any X. In your original post, you ended up with Y > Z.
-- 
Igor Tandetnik

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

Re: [sqlite] Time and date functions

[Nando]

I just realized that the "weekday" is not working as and believed, but is
much easier. I can make simple queries of the form:

select * from dates WHERE date = date ('2011-12-09 ',' weekday 5 ');

Or

select * from dates WHERE date <= date ('2011-12-04 ',' weekday 6 ');

And they work properly. But I can not use this selection criterion with
periods defined by the operator "between" using "X> = Y and X <= Z"

I'm testing with a simple table:

CREATE TABLE fechas (

id integer primary key autoincrement,

fecha numeric not null

);

1|2011-12-01

2|2011-12-02

3|2011-12-03

4|2011-12-04

5|2011-12-05

6|2011-12-06

7|2011-12-07

8|2011-12-08

9|2011-12-09

10|2011-12-10

11|2011-12-11

12|2011-12-12

13|2011-12-13

14|2011-12-14

15|2011-12-15

16|2011-12-16

17|2011-12-17

18|2011-12-18

19|2011-12-19

20|2011-12-20

21|2011-12-21

22|2011-12-22

23|2011-12-23

24|2011-12-24

25|2011-12-25

26|2011-12-26

27|2011-12-27

28|2011-12-28

29|2011-12-29

30|2011-12-30

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

Re: [sqlite] Time and date functions

[Jean-Christophe Deschamps]

select * from TABLE where DATE  between date('now','-7 days','Weekday 0')
and date('now','-7 days','Weekday 6');


Let me try this:

select * from TABLE where DATE  between date('now','-6 days','Weekday 0')
and date('now','Weekday 6');

or compare week numbers (%W specification) [slower] 


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

Re: [sqlite] Time and date functions

[Igor Tandetnik]

Nando  wrote:
> SQLite system for working with dates is very powerful, but I find it
> strange. I'm trying to select the dates between the beginning and end of a
> week defined from the date of a given day. If I understand the documentation
> thing this code should work:
> 
> 
> select * from TABLE where DATE  between date('now','-7 days','Weekday 0')
> and date('now','-7 days','Weekday 6');

Today is Saturday, 10/15. date('now','-7 days') is the last Saturday, 10/8. 
date('now','-7 days','Weekday 0') is the nearest Sunday on or after 10/8 - that 
is, the Sunday of 10/9. Similarly, date('now','-7 days','Weekday 6') is the 
nearest Saturday on or after 10/8 - that is, 10/8 itself. Since 10/9 is greater 
than 10/8, there are no dates that fall between them.

This inversion would hapeen every day except on a Sunday.

You might want something like this:

between date('now','Weekday 0', '-7 days') and date('now', 'Weekday 0', '-1 
days')

That's always the nearest previous Sunday through the following Saturday (which 
may be in the past or in the future). Adjust to taste.
-- 
Igor Tandetnik

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

Re: [sqlite] Time and date functions

[Black, Michael (IS)]

That's because today is Saturday.  So 'Weekday 6' is Saturday which is the same 
as 'now' - 7 days.  No date advance is done.

sqlite> select date('now','-7 days','Weekday 0');
2011-10-09
sqlite> select date('now','-7 days','Weekday 1');
2011-10-10
sqlite> select date('now','-7 days','Weekday 2');
2011-10-11
sqlite> select date('now','-7 days','Weekday 3');
2011-10-12
sqlite> select date('now','-7 days','Weekday 4');
2011-10-13
sqlite> select date('now','-7 days','Weekday 5');
2011-10-14
sqlite> select date('now','-7 days','Weekday 6');
2011-10-08

But if you do -6 days it behaves as expected...but only because today is 
Saturday again.  It won't work correctly tomorrow.

sqlite> select date('now','-6 days','Weekday 0');
2011-10-09
sqlite> select date('now','-6 days','Weekday 1');
2011-10-10
sqlite> select date('now','-6 days','Weekday 2');
2011-10-11
sqlite> select date('now','-6 days','Weekday 3');
2011-10-12
sqlite> select date('now','-6 days','Weekday 4');
2011-10-13
sqlite> select date('now','-6 days','Weekday 5');
2011-10-14
sqlite> select date('now','-6 days','Weekday 6');
2011-10-15

If you want just the last week do this:
sqlite> create table t(d date);

sqlite> insert into t values('2011-10-08');
sqlite> insert into t values('2011-10-09');
sqlite> insert into t values('2011-10-10');
sqlite> insert into t values('2011-10-11');
sqlite> insert into t values('2011-10-12');
sqlite> insert into t values('2011-10-13');
sqlite> insert into t values('2011-10-14');
sqlite> insert into t values('2011-10-15');
sqlite> select * from t where d between date('now','-6 days') and date('now');
2011-10-09
2011-10-10
2011-10-11
2011-10-12
2011-10-13
2011-10-14
2011-10-15


Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems



From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Nando [fl446...@gmail.com]
Sent: Saturday, October 15, 2011 4:17 PM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Time and date functions


SQLite system for working with dates is very powerful, but I find it
strange. I'm trying to select the dates between the beginning and end of a
week defined from the date of a given day. If I understand the documentation
thing this code should work:


select * from TABLE where DATE  between date('now','-7 days','Weekday 0')
and date('now','-7 days','Weekday 6');


But it does not work, although SQLite does not show any syntax error so do
not know where I'm wrong.
___
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] Time and date functions

[Nando]

SQLite system for working with dates is very powerful, but I find it
strange. I'm trying to select the dates between the beginning and end of a
week defined from the date of a given day. If I understand the documentation
thing this code should work:


select * from TABLE where DATE  between date('now','-7 days','Weekday 0')
and date('now','-7 days','Weekday 6');


But it does not work, although SQLite does not show any syntax error so do
not know where I'm wrong.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users