[sqlite] generating mini-calendar: DATE and quoting...
I'd like to write a query that generates ten consecutive days starting at 2011-02-05 (for example), but I believe I'm having trouble with quoting. Assume I have a table of ten digits such as: CREATE TABLE digits (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, digit integer) INSERT INTO digits (digit) VALUES (0) INSERT INTO digits (digit) VALUES (1) INSERT INTO digits (digit) VALUES (2) INSERT INTO digits (digit) VALUES (3) INSERT INTO digits (digit) VALUES (4) INSERT INTO digits (digit) VALUES (5) INSERT INTO digits (digit) VALUES (6) INSERT INTO digits (digit) VALUES (7) INSERT INTO digits (digit) VALUES (8) INSERT INTO digits (digit) VALUES (9) A query that does NOT work is: sqlite SELECT DATE(2011-02-05 21:42:20, units.digit DAY) AS d FROM digits AS units; ... which returns ten blank records. Using units.digit as the sole argument shows that the counting is working: sqlite SELECT DATE(units.digit) AS d FROM digits AS units; -4713-11-24 -4713-11-25 -4713-11-26 -4713-11-27 -4713-11-28 -4713-11-29 -4713-11-30 -4713-12-01 -4713-12-02 -4713-12-03 ... but of course I'd like something more current. Is there some obvious quoting that I'm missing? Thanks in advance. - ff PS: I know you can do this with stored functions, but this is a small snippet of a larger system in which a stored function isn't appropriate. -- View this message in context: http://old.nabble.com/generating-mini-calendar%3A-DATE-and-quoting...-tp30896927p30896927.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] generating mini-calendar: DATE and quoting...
On Thu, 10 Feb 2011 17:17:29 -0500, fearless_fool rdp...@gmail.com wrote: [snip] A query that does NOT work is: sqlite SELECT DATE(2011-02-05 21:42:20, units.digit DAY) AS d FROM digits AS units; http://www.sqlite.org/lang_datefunc.html http://www.sqlite.org/lang_select.html (and a few others) Very truly, Samuel Adam ◊ http://certifound.com/ 763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States Legal advice from a non-lawyer: “If you are sued, don’t do what the Supreme Court of New Jersey, its agents, and its officers did.” http://www.youtube.com/watch?v=iT2hEwBfU1g ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] generating mini-calendar: DATE and quoting...
On Thu, Feb 10, 2011 at 05:27:02PM -0500, Samuel Adam scratched on the wall:
On Thu, 10 Feb 2011 17:17:29 -0500, fearless_fool rdp...@gmail.com wrote:
[snip]
A query that does NOT work is:
sqlite SELECT DATE(2011-02-05 21:42:20, units.digit DAY) AS d FROM
digits AS units;
http://www.sqlite.org/lang_datefunc.html
http://www.sqlite.org/lang_select.html
...and quote your strings correctly. SQL string literals (such
as the two in this select) use ('). () is reserved for labels
(table names and column names).
-j
--
Jay A. Kreibich J A Y @ K R E I B I.C H
Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable. -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] generating mini-calendar: DATE and quoting...
Samuel Adam-2 wrote: http://www.sqlite.org/lang_datefunc.html http://www.sqlite.org/lang_select.html (and a few others) Very truly, Hi SA: So I've been reading those very pages carefully. And since the docs say Note that ±NNN months works by rendering the original date into the -MM-DD format, adding the ±NNN to the MM month value, then normalizing the result. I'm even more perplexed when the following one-liner isn't properly normalized: sqlite SELECT DATE(2011-01-32); It returns a blank record rather than giving 2011-02-01, contrary to what the documentation suggests. What am I missing? -- View this message in context: http://old.nabble.com/generating-mini-calendar%3A-DATE-and-quoting...-tp30896927p30897057.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] generating mini-calendar: DATE and quoting...
Meh. I have a solution, but I don't like it very much because it feels
convoluted:
sqlite select strftime('%Y-%m-%d', julianday('2011-01-01') + digit) as d
from digits;
2011-01-01
2011-01-02
2011-01-03
2011-01-04
2011-01-05
2011-01-06
2011-01-07
2011-01-08
2011-01-09
2011-01-10
This takes advantage that JULIANDAY is in units of days. I'll go with this
unless some guru suggests something cleaner.
--
View this message in context:
http://old.nabble.com/generating-mini-calendar%3A-DATE-and-quoting...-tp30896927p30897133.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] generating mini-calendar: DATE and quoting...
On Thu, Feb 10, 2011 at 02:47:29PM -0800, fearless_fool scratched on the wall:
Meh. I have a solution, but I don't like it very much because it feels
convoluted:
sqlite select strftime('%Y-%m-%d', julianday('2011-01-01') + digit)
as d from digits;
This takes advantage that JULIANDAY is in units of days. I'll go with this
unless some guru suggests something cleaner.
SELECT date( '2011-01-01', digit || 'days' ) AS d FROM digits;
-j
--
Jay A. Kreibich J A Y @ K R E I B I.C H
Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable. -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] generating mini-calendar: DATE and quoting...
On 2/10/2011 5:17 PM, fearless_fool wrote:
I'd like to write a query that generates ten consecutive days starting at
2011-02-05 (for example), but I believe I'm having trouble with quoting.
Assume I have a table of ten digits such as:
CREATE TABLE digits (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
digit integer)
INSERT INTO digits (digit) VALUES (0)
INSERT INTO digits (digit) VALUES (1)
INSERT INTO digits (digit) VALUES (2)
INSERT INTO digits (digit) VALUES (3)
INSERT INTO digits (digit) VALUES (4)
INSERT INTO digits (digit) VALUES (5)
INSERT INTO digits (digit) VALUES (6)
INSERT INTO digits (digit) VALUES (7)
INSERT INTO digits (digit) VALUES (8)
INSERT INTO digits (digit) VALUES (9)
A query that does NOT work is:
sqlite SELECT DATE(2011-02-05 21:42:20, units.digit DAY) AS d FROM
digits AS units;
select date('2011-02-05 21:42:20', units.digit || ' days') as d from
digits as units;
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] generating mini-calendar: DATE and quoting...
On Thu, 10 Feb 2011 17:55:57 -0500, Jay A. Kreibich j...@kreibi.ch wrote:
On Thu, Feb 10, 2011 at 02:47:29PM -0800, fearless_fool scratched on the
wall:
Meh. I have a solution, but I don't like it very much because it feels
convoluted:
sqlite select strftime('%Y-%m-%d', julianday('2011-01-01') + digit)
as d from digits;
This takes advantage that JULIANDAY is in units of days. I'll go with
this
unless some guru suggests something cleaner.
SELECT date( '2011-01-01', digit || 'days' ) AS d FROM digits;
s/'days'/' days'/
(Thanks for the tip on quotes; I should have added
http://www.sqlite.org/lang_expr.html and
http://www.sqlite.org/lang_keywords.html .)
Very truly,
Samuel Adam ◊ http://certifound.com/
763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States
Legal advice from a non-lawyer: “If you are sued, don’t do what the
Supreme Court of New Jersey, its agents, and its officers did.”
http://www.youtube.com/watch?v=iT2hEwBfU1g
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] generating mini-calendar: DATE and quoting...
@igor, @jay (and a good assist from @samuel):
Cool beans -- that works and is cleaner than my hack. Thank you.
FYA, the final form of this query (to be used as a sub-query throughout much
of our system) is:
SELECT DATE('#{start_time.to_s(:db)}', (thousands.digit * 1000 +
hundreds.digit * 100 + tens.digit * 10 + units.digit) || ' days') AS date
FROM digits AS units
INNER JOIN digits AS tens
INNER JOIN digits AS hundreds
INNER JOIN digits AS thousands
WHERE (thousands.digit * 1000 + hundreds.digit * 100 + tens.digit *
10 + units.digit) #{ndays}
ORDER BY date
I'll leave the actual result as an exercise to the reader, but it's a handy
function to have around.
- ff
--
View this message in context:
http://old.nabble.com/generating-mini-calendar%3A-DATE-and-quoting...-tp30896927p30897388.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] generating mini-calendar: DATE and quoting...
On Thu, Feb 10, 2011 at 06:02:01PM -0500, Samuel Adam scratched on the wall: On Thu, 10 Feb 2011 17:55:57 -0500, Jay A. Kreibich j...@kreibi.ch wrote: SELECT date( '2011-01-01', digit || 'days' ) AS d FROM digits; s/'days'/' days'/ Indeed. 3.6.x does not require the space, but the newer 3.7 verions do. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
