Re: [sqlite] Select with dates

[Niall O'Reilly]

On 16 Sep 2013, at 18:43, Petite Abeille wrote:

> What about simply using not overlapping intervals and call it a day?

Sure!  WFM.  I thought that was what I was suggesting.  8-)

/Niall

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

Re: [sqlite] Select with dates

[Petite Abeille]

On Sep 16, 2013, at 10:48 AM, Niall O'Reilly  wrote:

>   Consecutive closed intervals overlap.  Depending on the
>   application, this may be a problem; it can be avoided by
>   using half-open ones.

What about simply using not overlapping intervals and call it a day?

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

Re: [sqlite] Select with dates

[Simon Slavin]

On 16 Sep 2013, at 9:48am, Niall O'Reilly  wrote:

> On 14 Sep 2013, at 18:09, Petite Abeille wrote:
> 
>> Yeah... not sure why people are doing that to themselves though :D
> 
>   Consecutive closed intervals overlap.  Depending on the
>   application, this may be a problem; it can be avoided by
>   using half-open ones.

Or by not relying on a continuous number line when what you really want is 
serial-numbering.

I wouldn't attempt to locate follow-chains inside a SELECT which was doing 
something else.  I'd write one routine to figure out the serial numbers (and 
possibly store them back in the tables), and once that was done, use those to 
do the task I was really trying to do.

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

Re: [sqlite] Select with dates

[Niall O'Reilly]

On 14 Sep 2013, at 18:09, Petite Abeille wrote:

> Yeah... not sure why people are doing that to themselves though :D

Consecutive closed intervals overlap.  Depending on the
application, this may be a problem; it can be avoided by
using half-open ones.

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

Re: [sqlite] Select with dates

[Petite Abeille]

On Sep 14, 2013, at 4:24 AM, James K. Lowden  wrote:

>> Why not
>>SELECT * FROM "entry" WHERE
>>   bankdate >= date('now','start of month')
>>  AND bankdate < date('now','start of month','+1 month')
> 
> The half-open interval strikes again!  :-)

Yeah... not sure why people are doing that to themselves though :D

bankdate between start_of_month and end_of_month 

… now that reads much better :))



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

Re: [sqlite] Select with dates

[Keith Medcalf]

> On Thu, 12 Sep 2013 14:01:04 +0100
> Simon Davies  wrote:
> 
> > Why not
> > SELECT * FROM "entry" WHERE
> >bankdate >= date('now','start of month')
> >   AND bankdate < date('now','start of month','+1 month')
> 
> The half-open interval strikes again!  :-)

And you are using UTC ...

Notwithstanding the timezone you want to use, 

explain select * 
  from entry 
 where bankdate >= date('now', 'start of month') 
   and bankdate < date('now', 'start of month', '-1 day');

will generate the following code:

SELECT {0:0}
FROM {0,*} = entry
WHERE AND(GE({0:0},FUNCTION:date(item[0] = 'now'
 item[1] = 'start of 
month')),LT({0:0},FUNCTION:date(item[0] = 'now'

 item[1] = 'start of month'

 item[2] = '-1 day')))
END
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 Goto   0 19000
2 OpenRead   0 2 0 1  00
3 Rewind 0 17000
4 Column 0 0 100
5 String80 3 0 now00
6 String80 4 0 start of month  00
7 Function   3 3 2 date(-1)   02
8 Lt 2 161 collseq(BINARY)  6a
9 String80 5 0 now00
10String80 6 0 start of month  00
11String80 7 0 -1 day 00
12Function   7 5 2 date(-1)   03
13Ge 2 161 collseq(BINARY)  6a
14Column 0 0 800
15ResultRow  8 1 000
16Next   0 4 001
17Close  0 0 000
18Halt   0 0 000
19Transaction0 0 000
20VerifyCookie   0 1 000
21TableLock  0 2 0 entry  00
22Goto   0 2 000
sqlite>

You will note that the two date functions are executed for each candidate row.  
But, if you have a suitable index, the date functions are only executed once:

create index entry_bankdate on entry (bankdate);

SELECT {0:0}
FROM {0,*} = entry
WHERE AND(GE({0:0},FUNCTION:date(item[0] = 'now'
 item[1] = 'start of 
month')),LT({0:0},FUNCTION:date(item[0] = 'now'

 item[1] = 'start of month'

 item[2] = '-1 day')))
END
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 Goto   0 21000
2 OpenRead   1 3 0 keyinfo(1,BINARY)  00
3 String80 2 0 now00
4 String80 3 0 start of month  00
5 Function   3 2 1 date(-1)   02
6 IsNull 1 19000
7 SeekGe 1 191 1  00
8 String80 4 0 now00
9 String80 5 0 start of month  00
10String80 6 0 -1 day 00
11Function   7 4 1 date(-1)   03
12IsNull 1 19000
13IdxGE  1 191 1  00
14Column 1 0 700
15IsNull 7 18000
16Column 1 0 800
17ResultRow  8 1 000
18Next   1 13000
19Close  1 0 000
20Halt   0 0 000
21Transaction0 0 000
22VerifyCookie   0 2 000
23TableLock  0 2 0 entry  00
24Goto   0 2 000
sqlite> 

** I cannot say if this is correct behavior or not.  I would say that it is 
correct for each row because date('now') retrieves the date "now", not "then", 
so perhaps it should be executed for each row ... though it may not be exactly 
what you expect since you are probably making an assumption about "now" -- is 
it the "now" at the 

Re: [sqlite] Select with dates

[James K. Lowden]

On Thu, 12 Sep 2013 14:01:04 +0100
Simon Davies  wrote:

> Why not
> SELECT * FROM "entry" WHERE
>bankdate >= date('now','start of month')
>   AND bankdate < date('now','start of month','+1 month')

The half-open interval strikes again!  :-)

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

Re: [sqlite] Select with dates

[jwzumwalt]

Thank! This worked great!



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Select-with-dates-tp71222p71229.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select with dates

[Keith Medcalf]

select *
  from entry
 where bankdate between date('now', 'localtime', 'start of month')
and date('now', 'localtime', 'start of month', '+1 month', 
'-1 second');

assuming that your bankdate contains a datestring formatted as -mm-dd in 
localtime.  Without the 'localdate' you get UTC, which may cause consternation 
and confusion depending on your timezone and when you execute the query.

sqlite> select date('now', 'localtime', 'start of month'),
   ...>date('now', 'localtime', 'start of month', '+1 month', '-1 
second');
2013-09-01|2013-09-30

sqlite> select datetime('now', 'localtime', 'start of month'),
   ...>datetime('now', 'localtime', 'start of month', '+1 month', '-1 
second');
2013-09-01 00:00:00|2013-09-30 23:59:59

You may want to ensure that the date functions are executed only once rather 
than per-row by re-phrasing the query something like this:  (I am not sure if 
the optimizer now knows that date result is constant and only needs to be 
executed once for the whole query or not -- it did not used to unless the date 
was retrieved in a scalar query such as either of the below):

select *
  from entry,
   (select date('now', 'localtime', 'start of month') as begindate, 
   date('now', 'localtime', 'start of month', '+1 month', '-1 
second') as enddate) as daterange
 where bankdate between begindate and enddate;

which creates a temporary table with the start and end dates in it, and uses 
that table in join constraints.  Obviously this table will end up as the outer 
loop.

select *
  from entry
 where bankdate between (select date('now', 'localtime', 'start of month')) 
and (select date('now', 'localtime', 'start of month', '+1 
month', '-1 second'));

which will force the VDBE compiler to emit once conditionals around the date 
functions since it is clear that they represent a scalar constant.

The join format is somewhat more useful if you will be referring to the 
resulting dates more than once or in a join rather than a simple single table 
select ...


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of jwzumwalt
> Sent: Wednesday, 11 September, 2013 23:34
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Select with dates
> 
> I have not used the date function in select statements before.
> I have valid entries for the current month, what am I doing wrong?
> 
> SELECT * FROM "entry" WHERE
> bankdate > date('now','end of month','-1 month')
> AND bankdate < date('now','start of month','+1 month')
> 
> 
> 
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/Select-with-dates-tp71215.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> 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] Select with dates

[Hick Gunter]

And even if there was an "end of month" modifier, your expression would screw 
up more than half the time

Lets take a date in march, eg. The 15th

2013-03-15 -> (end of month) -> 2013-03-31 -> (-1 month) -> 2013-02-31 -> 
(renormalization) -> 2013-03-03

-Ursprüngliche Nachricht-
Von: Simon Davies [mailto:simon.james.dav...@gmail.com]
Gesendet: Donnerstag, 12. September 2013 15:01
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Select with dates

On 12 September 2013 06:34, jwzumwalt <jwzumw...@gmail.com> wrote:
> I have not used the date function in select statements before.
> I have valid entries for the current month, what am I doing wrong?
>
> SELECT * FROM "entry" WHERE
> bankdate > date('now','end of month','-1 month')
> AND bankdate < date('now','start of month','+1 month')
>

I see no "end of month" modifier in http://www.sqlite.org/lang_datefunc.html
Why not
SELECT * FROM "entry" WHERE
   bankdate >= date('now','start of month')
  AND bankdate < date('now','start of month','+1 month')

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


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select with dates

[Simon Davies]

On 12 September 2013 06:34, jwzumwalt  wrote:
> I have not used the date function in select statements before.
> I have valid entries for the current month, what am I doing wrong?
>
> SELECT * FROM "entry" WHERE
> bankdate > date('now','end of month','-1 month')
> AND bankdate < date('now','start of month','+1 month')
>

I see no "end of month" modifier in http://www.sqlite.org/lang_datefunc.html
Why not
SELECT * FROM "entry" WHERE
   bankdate >= date('now','start of month')
  AND bankdate < date('now','start of month','+1 month')

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

[sqlite] Select with dates

[jwzumwalt]

I have not used the date function in select statements before.
I have valid entries for the current month, what am I doing wrong?

SELECT * FROM "entry" WHERE 
bankdate > date('now','end of month','-1 month')
AND bankdate < date('now','start of month','+1 month')



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Select-with-dates-tp71215.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users