Re: [sqlite] Select with dates
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
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
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
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
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
> 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 beginning of the query, or is it
Re: [sqlite] Select with dates
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
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
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
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 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
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
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