SQLite Forum

The patch I posted here was broken by the refactoring in checkin e548e9299d. Here is a new patch, in case anyone is interested.

Index: src/date.c
==================================================================
--- src/date.c
+++ src/date.c
@@ -989,17 +989,20 @@
 **
 ** Return a string described by FORMAT.  Conversions as follows:
 **
 **   %d  day of month
 **   %f  ** fractional seconds  SS.SSS
+**   %G  year 0000-9999 of current week's Thursday
 **   %H  hour 00-24
 **   %j  day of year 000-366
 **   %J  ** julian day number
 **   %m  month 01-12
 **   %M  minute 00-59
 **   %s  seconds since 1970-01-01
 **   %S  seconds 00-59
+**   %u  day of week 1-7  monday==1
+**   %V  week of year 01-53  week belongs to year with most of its days
 **   %w  day of week 0-6  sunday==0
 **   %W  week of year 00-53
 **   %Y  year 0000-9999
 **   %%  %
 */
@@ -1079,15 +1082,44 @@
         break;
       }
       case 'S': {
         sqlite3_str_appendf(&sRes,"%02d",(int)x.s);
         break;
+      }
+      case 'u': {
+        sqlite3_str_appendchar(&sRes, 1,
+                       (char)(((x.iJD+43200000)/86400000) % 7) + '1');
+        break;
       }
       case 'w': {
         sqlite3_str_appendchar(&sRes, 1,
                        (char)(((x.iJD+129600000)/86400000) % 7) + '0');
         break;
+      }
+      case 'V':  /* Fall thru */
+      case 'G': {
+        DateTime y = x;
+        computeJD(&y);
+        y.validYMD = 0;
+        /* Adjust date to Thursday this week:
+           The number in parentheses is 0 for Monday, 3 for Thursday */
+        y.iJD += (3 - (((y.iJD+43200000)/86400000) % 7))*86400000;
+        computeYMD(&y);
+        if( zFmt[i]=='G' ){
+          sqlite3_str_appendf(&sRes,"%04d",y.Y);
+        }else{
+          /* borrowed code from case 'j' */
+          sqlite3_int64 tJD = y.iJD;
+          int nDay;
+          y.validJD = 0;
+          y.M = 1;
+          y.D = 1;
+          computeJD(&y);
+          nDay = (int)((tJD-y.iJD+43200000)/86400000);
+          sqlite3_str_appendf(&sRes,"%02d",nDay/7+1);
+        }
+        break;
       }
       case 'Y': {
         sqlite3_str_appendf(&sRes,"%04d",x.Y);
         break;
       }

That seems to have done the trick! (And a one line fix, to boot.)

I have been bisecting a bit, using my last example in the quoted post:

bisect complete
  1 BAD     2021-09-22 14:26:22 96610cc8251ad4ff
  3 BAD     2021-09-21 20:03:05 4a3f98ad0dd2a8c0
  5 BAD     2021-09-21 19:19:28 a1c7f7f8e1b46440 CURRENT
  4 GOOD    2021-09-21 17:26:23 2c0e7ae541e9ecb8
  2 GOOD    2021-09-18 16:15:54 99d6bb22e8735681

From my brief reading of the code, I can't for the life of me see where the number of escaped double quotes in the file argument can have any impact.

Replacing \" by \042 does seem to help, though.

Thanks; I will experiment a bit further. Just one quick note, though: tail -r works fine on macOS. It does not on linux, which I assume is where you ran your experiment. On (most? all?) linux system you have the tac command that does the same thing: It collects lines from stdin and outputs them in reverse order.

So no, I don't buy the hypothesis that my problem arises from a failed pipeline.

I cooked up a simpler example. I think it shows more clearly that the problem does not arise from the complexity of the pipeline nor from any error in the pipeline, but from the presence of the backslash-escaped double quote.

⬥ cat tt.csv
,irrelevant,
"2021-10-02",some,data
,also,irrelevant
⬥ sqlite3 tt.db
SQLite version 3.37.0 2021-09-24 16:38:15
Enter ".help" for usage hints.
sqlite> create table tt(date,x,y);
sqlite> .import -csv "|grep '^\"2' tt.csv" tt
sqlite> select 42;
   ...>

⬥ sqlite3 tt.db
SQLite version 3.37.0 2021-09-24 16:38:15
Enter ".help" for usage hints.
sqlite> select * from tt;
2021-10-02|some|data
sqlite>

⬥ sqlite3 tt.db
SQLite version 3.37.0 2021-09-24 16:38:15
Enter ".help" for usage hints.
sqlite> .import -csv "|grep '^.2' tt.csv" tt
sqlite> select * from tt;
2021-10-02|some|data
2021-10-02|some|data
sqlite>

Edited to add an even simpler example. It seems that an odd number of occurrences of \" within the double quoted argument is the crux here:

⬥ sqlite3 tt.db
SQLite version 3.37.0 2021-09-24 16:38:15
Enter ".help" for usage hints.
sqlite> drop table tt;
sqlite> create table tt(x);
sqlite> .import -csv "|echo '\"hi there\"'" tt
sqlite> select * from tt;
hi there
sqlite> .import -csv "|echo '\"hi there\"' | tr -d '\"'" tt
sqlite> select * from tt;
   ...>

(I expect you need to double that backslash.)

The backslash is there to escape the double quote, so that the resulting argument following -csv will be

|iconv -f l1 < trans.csv | grep '^"20' | tail -r

That string, in its turn, should be parsed by /bin/sh (with the initial | removed) so that the argument that grep sees is

^"20

meaning a line beginning with the three characters "20.

And indeed, the data winding up in the database file is consistent with my expectations, so I think I got the number of backslashes right.

This is a bit odd:

⬥ sqlite3 foo.db
SQLite version 3.37.0 2021-09-24 16:38:15
Enter ".help" for usage hints.
sqlite>
sqlite> .import -csv "|iconv -f l1 < trans.csv  | grep '^\"20' | tail -r" incoming
sqlite> select 42;
   ...>

That's a secondary prompt string, indicating that the shell is waiting for more input. I haven't found any way out of it other than to hit Crrl-D, exiting the shell.

Interestingly, when I reenter the shell, I find that the import has completed successfully.

Is it the complicated form of the .import line that throws it off? But if so, why does it present a primary prompt after that line? Isn't the parser completely reset after each line that it parses successfully?

I could add that incoming is a view with an INSTEAD OF INSERT trigger. That ought to be irrelevant; but I suppose I could try with a copy of the database in which incoming has been replaced by a regular table.

That just loads SQL code. And SQLite dot commands, I think. But it can't do what you can't do from the command line.

Edit: Oops, that was mistaken. Sorry.

You could add a RETURNING clause at the end.

If we could use a DELETE statement as a CTE¹, you could sort it thus:

WITH deleted AS (DELETE FROM foo WHERE condition RETURNING stuff)
SELECT * FROM deleted ORDER BY something;

¹ This capability is on my wishlist, but I am not holding my breath waiting for it. ;-)

May I also point out that the DELETE statement allows a RETURNING clause? I find it quite useful.

Tangentially, PostgreSQL allows DELETE statements with a RETUNING clause as a common table expression to be used in a WITH clause. I use it sometimes to move data between tables. Possibly, SQLite users could benefit from the same feature.

What on earth would anyone want to "embed" it in another application for?

I have no wish to do so myself, but I can't resist pointing out that fossil's sql subcommand appears to do just that.

Given the fact that SQLite is old enough to drink legally everywhere in the US (I think), you should perhaps not be too surprised.

PS. The question reminds me of Bill Clinton, during his impeachment process I believe: It depends what the meaning of “is” is.

I'm not sure whether internal tables always start with "sqlite_".

I am pretty sure I have seen that documented. Moreover, you are not allowed to create a table whose name starts with sqlite_ yourself; you get an error message if you try.

How about temporary tables?

They are listed in temp.sqlite_master.

If you did read the whole discussion thread here, you should be aware that this is already known to the participants. The statement is true, however, about the convenient fiction that is sometimes known as POSIX time, as well as the timescale used by SQLite itself. That these timescales ignore leap seconds is well known already.

Another way is to run .mode insert, then run SELECT statements to get a representative sample. Also, better run .schema and post the needed bits (avoiding any tables and views not relevant to the question). Try your best to keep the size of the resulting post down to the bare minimum.

Indeed, but since that is intended for all your SQLite sessions, it could be undesirable to use it for ad hoc purposes.

That could fail with integers too large to be exactly represented with doubles, however. Numbers can be hard to work with.

I didn't try too hard decoding your pictures, but I note that the expression

genre_name = 'Romance' AND genre_name = 'Comedy'

will never be true. Perhaps you wanted to use OR instead? Or simpler:

genre_name IN ('Romance','Comedy')

That can't be done, as far as I know.

One workaround is to put the sql commands (and/or dot commands) into a file, then specify that file with the -init flag to the command shell.

Poking around in the grammar at src/parse.y, I find the entry for CAST as follows:

expr(A) ::= CAST LP expr(E) AS typetoken(T) RP. {
  A = sqlite3ExprAlloc(pParse->db, TK_CAST, &T, 1);
  sqlite3ExprAttachSubtrees(pParse->db, A, E, 0);
}

and looking for typetoken in the same file, there is this illuminating comment:

// A typetoken is really zero or more tokens that form a type name such
// as can be found after the column name in a CREATE TABLE statement.
// Multiple tokens are concatenated to form the value of the typetoken.

To emphasise, zero or more tokens. This makes good sense in the context of CREATE TABLE, less so in the case of a CAST expression.

I conjecture that this inadvertently legalised a CAST expression with no type designation. It certainly appears to be undocumented.

Perhaps the grammar should be tightened to disallow this; but if that cannot (or should not) be done for the sake of backward compatibility, I think it ought to remain undocumented, so as not to trick users into relying on it. Or, it could be documented, with a caveat that the resulting behaviour is undefined. (It seems to cast to numeric at the moment.) In either case, I think it had better be kept out of the syntax diagram.

Just my three cents' worth – inflation, y´know.

Read it again. It's not the result that is truncated, it's the operands, before the modulo operation happens. So SQLite computes 8 mod 0, which becomes a NULL.

As to why it does that, it is probably a decision made such a long time ago nobody remembers anymore. It can't easily be changed now, for backwards compatibility. I suppose such a change could be made, with a suitable pragma to turn on the new behaviour, but then you had to make a strong case for that.

AND WHERE is a syntax error. Replace by AND.

Anyhow, I think your query is better written as a join:

SELECT s.station_name, s.start_lat, s.start_lng, started_at, ended_at, duration
FROM stations AS s
JOIN detail AS d
ON (s.start_lat = d.start_lat AND s.end_lat = d.ended_at);

Look at https://sqlite.org/copyright.html for some pointers

If it's about spelling mistakes in the documentation, I imagine they are not so strict about not accepting patches. Perhaps you could simply post a diff to the forum, if it is not too large.

Your input format is wrong: You need a decimal point, not a colon, between the seconds and the fractional seconds.

▶▶▶ create table foo(datetime text);
▶▶▶ insert into foo values ('2021-07-19 08:43:46.956'),('2021-07-20 10:26:41.357');
▶▶▶ select julianday(max(datetime))-julianday(min(datetime)) from foo;
julianday(max(datetime))-julianday(min(datetime))
-------------------------------------------------
1.07146297441795

Quick tip: Assuming you're doing this in the SQLite shell, if you run .mode insert, then subsequent SELECTs will produce INSERT statements that others can use to recreate the data. That will make it much easier for them to experiment.

I can play the guessing game too: A transaction was not closed before exiting. I think some language bindings (python?) open a transaction automatically, but leaves it up to the user to commit it.

You're probably onto something there. BBEDIT is a GUI application, whereas the edit() command in SQLite expects a path to an executable which will start an editor, and exit when the user is done. Starting a GUI Application on a Mac is a rather complicated affair involving more than a simple fork(), but the principle remains: The process activating the GUI application will usually continue as soon as it has triggered the activation of the application.

If you do this in the interactive SQLite shell, you would probably see the shell prompt immediately after invoking the edit.

For example, I don't have a copy of BBEDIT, but I tried an experiment using an editor called CotEditor (available from the App Store). CotEditor comes with an associated command line utility simply called cot, which will start CotEditor on a given file. It accepts a flag -w, which causes cot to wait until CotEditor has closed the file.

My little experiment proceeded as follows:

▶▶▶ create table foo(bar);
▶▶▶ insert into foo values("blah");
▶▶▶ select rowid,bar from foo;
rowid  bar
-----  ----
1      blah
▶▶▶ update foo set bar=edit(bar,'cot') where rowid=1;
-- note: the prompt below appeared immediately,
-- before I had time to do any editing; so, no change
▶▶▶ update foo set bar=edit(bar,'cot -w') where rowid=1;
-- here, nothing happened until I had saved and closed the temporary file
▶▶▶ select rowid,bar from foo;
rowid  bar
-----  ------
1      blargh
-- success!

If you wish this to work with BBEDIT, you need a similar tool. I don't know if BBEDIT provides such a tool.

Ah, that explains it.

I do wish for more context in SQLite error messages sometimes. I mean, just adding a few characters, say “near "("k","” would be a lot more helpful than “near "("”.

I am not an expert or authority, but here is my understanding of the issue, as far as it goes. If I am wildly off target, someone will hopefully correct me.

• A comma-separated list of expressions in parenthesis usually signifies a row value, or tuple.
• A VALUES expression is a special select statement, and as such produces a sequence of rows. Even if that sequence contains precisely one row, it is something different from a row value.
• A row value can contain any of the datatypes allowed by SQLite. Note that a row value is not among these; hence ((1,2,),(3,4)) is illegal.

The following seems to work in Postgres, but Sqlite is reporting "row value misused":

select * from kv
where (key, val) in (('k1', 1), ('k3', 3));

That is not so clear-cut. What's on the right side of WHERE … IN (…) is not an expression but a parenthesised list of expressions, so arguably, a list of row values could have been legitimate according to these rules. Perhaps it would be useful; but OTOH, the alternative to a list of expressions is a select statement, and VALUES is of course a select statement, so that will always work.

Another thing I have seen done is to join on a list of values, which works well in Postgres, but Sqlite reports a syntax error near "(":

select * 
from kv 
inner join (values ('k1', 1), ('k3', 3)) as bar("k", "v")
  on kv.key = bar.k and kv.val = bar.v;

I am not sure why that does not work in SQLite. The syntax diagrams seem to allow it.

Assuming the database file is corrupted, as seems likely (see the answer by David Raymond), you may be able to recover it using the .recover command of the sqlite shell:

sqlite3 original.db .recover | sqlite3 new.db

Run .help recover in the interactive shell to see some possibly useful options.

But after any database corruption, you should view the recovered data with some skepticism.

SQLite does allow some syntax for identifiers that is not standard SQL, such as backticks or square brackets for quoting identifiers.

If there are views and triggers and whatnot attached to the table, that might not work so well. In which case, make Table2 temporary, and after populating it,

BEGIN;
DELETE FROM Table1;
INSERT INTO Table1 SELECT * FROM Table2;
END;

The transaction is just to recover in case of a crash or power loss.

I have no idea, but would just point out that the table lacks the chainer_id column that appears in your query.

If a solution can be found that includes one row for each “nearest” timestamp, then those questions can easily be resolved by applying more filtering to the output, I think. In other words, those are questions that must be asked for any practical application, but they don't really influence the search for a solution much.

My two cents' worth: I think you'd be wise to avoid relying on this feature being implemented soon, if at all. If they plan to implement it for free, they are unlikely to say so before the feature has landed; doing otherwise invites a possibly endless stream of requests for ETAs, with attendant followups when the ETA has passed and the feature isn't implemented yet, effectively turning the ETA into a deadline.

Not that there is anything wrong with asking for features. I've done it myself. Just don't be disappointed if the requested feature doesn't materialise, or it takes much longer than desired to do so.

If the feature truly is important to you, you might consider paying to get it done. See the support page.

You can declare a UNIQUE constraint on a generated field. I believe that results in the creation of an index. Perhaps that is all you need?

Here is another possibility: Have a separate VIEW with a trigger to do your insertion into.

CREATE TABLE t1(
  id VARCHAR(64) PRIMARY KEY,
  myjson TEXT);

CREATE VIEW t1j AS SELECT json_insert(myjson,'$.id',id) myjson FROM t1;

CREATE TRIGGER t1_ins
INSTEAD OF INSERT ON t1j BEGIN
  INSERT INTO t1(id,myjson)
  VALUES (json_extract(NEW.myjson,'$.id'),json_remove(NEW.myjson,'$.id'));
END;

INSERT INTO t1j VALUES
  ('{"id":"foo","x":42}'),
  ('{"id":"bar","x":99}');

SELECT * FROM t1;

with the result (assuming column mode)

id   myjson
---  --------
foo  {"x":42}
bar  {"x":99}

I did a bit of extra work to avoid duplicating the id in the database. The view puts it back.

"11:59:59 am {tick} 12:00:00 am {tick} 12:00:01 pm" hurts my OCD.

Same here. To put it in other words, the visual change from 11:59:59 to 12:00:00 is so much greater than the change from 12:00:00 to 12:00:01 (visually and using the Hamming distance), it is only reasonable that the am/pm change should happen at the same time.

Until 2019 ISO 8601 allowed using 24:00:00 to refer to the end of the day but now that is forbidden.

I wasn't aware of that change. It makes sense, though, at least when you wish for machine readable time strings. I might still want to use it for human consumption, though.

I have also noticed that in the US, any insurance contract will start at 12:01 am, in order to avoid ambiguity. It would not help to say 12:00 midnight, since the question is still open whether that is the midnight before or after the stated date. But a slightly disconcerting side effect is that, if you switch insurance providers, your house will be uninsured for one minute after midnight. Better hope it is not struck by lightning at that moment.

Be careful! That produces the wrong result for the first hour after midnight, as well as the first hour past noon.

Somewhat tangentially, see also Confusion at noon and midnight (wikipedia).

Parsing of time strings other than ISO formatted ones isn't really SQLite's strong suit. If you want to feed am/pm time strings into the SQLite shell, you're probably better off converting the time strings using external means, maybe a Python program or some other programming language that you feel comfortable with. If you are using SQLite embedded in a C program, maybe you can use the strptime function to do what you want.

If the time strings in question follow a very rigid format, you may be able to do it combining SQLite's substring function along with some CASE expressions, but that would be cumbersome and quite fragile.

You can do all kinds of neat things with triggers. For example, an INSERT trigger on the original table to populate the other tables. I am not sure if this is useful in your case, but it's a thought anyway.

Myself, I have only recently discovered this use of triggers, but now I use them quite a bit. (Perhaps I am overly enthusiastic, but …) A typical pattern for me is a VIEW named incoming with a trigger INSTEAD OF INSERT ON incoming allowing me to directly import data from a csv file (or even json, in one case) and have the values inserted in tables that are better suited to the problem at hand.

I don't know why there could be any criticism because imho it has absolutely no negative impact.

That is not at clear. In fact, Postel's law itself has been criticised because being liberal in what you accept can allow errors to go undetected, with possible data corruption or worse (security holes, in the case of the Internet) as a consequence.

I wish it were called Postel's guideline instead of a law.

And to be clear, I have nothing against the deviation from the standard discussed in this thread. But I would be surprised to learn it had never happened that someone, somewhere, got bitten by it. Hopefully not too seriously.