SQLite Forum

Getting rid of double DATE()
Login

Getting rid of double DATE()

(1) By Cecil (CecilWesterhof) on 2021-01-24 15:47:51 [link]

I have a tcl program where I set a variable like this:
set updateLastBrewed "
    UPDATE selectRandom
    SET    lastUsed    = DATE('now', 'localtime')
    ,      lastUsedIdx = (
        SELECT IFNULL(MAX(lastUsedIdx), 0)
        FROM   selectRandom
        WHERE  lastUsed = DATE('now', 'localtime')
    ) + 1
    WHERE  description = :teaToBrew
"

This variable I later use to update a table.
But it contains two times:
    DATE('now', 'localtime')

That is maybe less efficient, but I do not care about that. There are two potential problems:
- If I change what I put into lastUsed, I have to change it in both places.
- When the statement is executed just when the day changes, they could give different results.

Is there a neater way to do this?

(2) By Keith Medcalf (kmedcalf) on 2021-01-24 16:17:13 in reply to 1

> If I change what I put into lastUsed, I have to change it in both places.

Yes.

 > When the statement is executed just when the day changes, they could give different results.

That cannot happen.  'now' is step stable by default.  And the entire statement runs to completion in a single step.

(3) By little-brother on 2021-01-24 17:53:23 in reply to 1 [link]

Check [UPDATE FROM](https://sqlite.org/lang_update.html).

(4.1) By Keith Medcalf (kmedcalf) on 2021-01-24 18:27:44 edited from 4.0 in reply to 3 [link]

One presumes you mean something like:

```
update selectRandom
   set lastUsed = now, 
       lastUsedIdx = (
                      select max(max(lastUsedIdx), 0) + 1
                        from selectRandom
                       where lastUsed == now
                     ) 
  from (
        select date('now', 'localtime') as now
       ) 
  where description == :teaToBrew;
```

which would eliminate having to write and execute the date function twice, any concern about the values being different during a single step being already irrelevant.

(5) By Cecil (CecilWesterhof) on 2021-01-24 19:12:36 in reply to 4.1 [link]

I did not know that the value could not be different. Good to know.

It does not work.

The definition of the table is:
    CREATE TABLE "selectRandom" (
	`selectRandomID`    INTEGER PRIMARY KEY AUTOINCREMENT,
	`description`	    TEXT NOT NULL UNIQUE,
	`comment`	    TEXT,
	`lastUsed`	    TEXT,
        `lastUsedIdx`       INTEGER,
        `stockTypeID`       INTEGER,
        `locationID`        INTEGER,

	FOREIGN KEY(`stockTypeID`) REFERENCES `stockTypes`(`stockTypeID`),
	FOREIGN KEY(`locationID`) REFERENCES `locations`(`locationID`)
)

When I execute (in DB Browser For SQLite):
    UPDATE selectRandom
    SET    lastUsed = now, 
           lastUsedIdx = (
        SELECT MAX(MAX(lastUsedIdx), 0) + 1
        FROM   selectRandom
        WHERE  lastUsed == now
    ) 
    FROM (
        SELECT DATE('now', 'localtime') as now
    ) 
    where description == 'White Tea'

I get:
near "FROM": syntax error: UPDATE selectRandom
    SET    lastUsed = now,
           lastUsedIdx = (
        SELECT MAX(MAX(lastUsedIdx), 0) + 1
        FROM   selectRandom
        WHERE  lastUsed == now
    )
    FROM

(6) By Keith Medcalf (kmedcalf) on 2021-01-24 19:18:58 in reply to 5 [link]

You SQLite3 is too old.  

You need at least SQLite3 3.33.0 that was released 2020-08-14 or later.  Versions prior to that do not understand UPDATE ... FROM ...

(7) By Cecil (CecilWesterhof) on 2021-01-24 20:56:30 in reply to 6 [link]

Thank you. I am using Debian and they do not have that in backports (yet).
Lets look if that can be changed.

On the other hand: it is not terrible. Especially because I now know that the two date calls cannot give a different value. But it would still nice to have.

(8) By Cecil (CecilWesterhof) on 2021-01-24 21:18:41 in reply to 7 [link]

CTE to the rescue:
    WITH now AS (
	    SELECT DATE('now', '+1 day', 'localtime') AS val
	)
    UPDATE selectRandom
    SET    lastUsed = (SELECT val FROM now), 
           lastUsedIdx = (
		SELECT IFNULL(MAX(lastUsedIdx), 0) + 1
        FROM   selectRandom
        WHERE  lastUsed == (SELECT val FROM now)
    ) 
    where description == 'White Tea'

I need to use the IFNULL instead of the double MAX.

(9) By Keith Medcalf (kmedcalf) on 2021-01-24 21:55:51 in reply to 8 [link]

Yeah, the scalar max returns null if any of the arguments are null, unlike the aggregate max that ignores null values.  (and IFNULL is just an alternate spelling of coalesce).

That does fix the "typing twice" but still results in "execute twice".

(10) By Cecil (CecilWesterhof) on 2021-01-24 22:47:59 in reply to 8 [link]

This is a bit better I think:
    WITH cte AS (
        SELECT DATE('now', 'localtime')        AS now
        ,      IFNULL(MAX(lastUsedIdx), 0) + 1 AS idx
        FROM   selectRandom
        WHERE  lastUsed == now
    )
    UPDATE selectRandom
    SET    lastUsed    = (SELECT now FROM cte)
    ,      lastUsedIdx = (SELECT idx FROM cte)
    WHERE  description = :teaToBrew

(11) By Cecil (CecilWesterhof) on 2021-01-24 22:51:05 in reply to 9 [link]

Well I find clearness of code more important as efficiency. (That is why I still tweaked it a bit.)
But could the execute twice be circumvented?

(12) By Keith Medcalf (kmedcalf) on 2021-01-25 11:51:11 in reply to 10 [link]

That circumvents executing the date function call twice by executing it four times.

Perhaps you mean something like this:

```
  WITH now(now)
    as (
        select date('now', 'localtime')
       ),
       cte(now, idx)
    AS (
        SELECT now,
               ifnull(max(lastUsedIdx), 0) + 1
          FROM selectRandom, now
         WHERE lastUsed == now
       )
UPDATE selectRandom
   SET (lastUsed, lastUsedIdx) = (SELECT now, idx FROM cte)
 WHERE description = :teaToBrew;
```

Note that "typing" does not necessarily equate to "executing", which is why there is the EXPLAIN QUERY PLAN (.eqp on) and EXPLAIN (.eqp full) in the CLI.

Of course, the repeated execution of the date function "takes the place of" the generation and use of a materialized view so avoiding the executions of such a non-expensive function call probably will not provide much (if any) actual benefit.  The above is, however, the CTE equivalent of using UPDATE ... FROM ...

(13) By Cecil (CecilWesterhof) on 2021-01-25 14:03:28 in reply to 12 [link]

That was not the intention. (Four times.)
Busy with other things: I will look into it later.

I should at last start exploring EXPLAIN. Just need to bite the bullet.

It is maybe not important for the execution time, but it never hurts to do things clean. That increases the change I will do it clean when it is important.

(14) By Cecil (CecilWesterhof) on 2021-01-26 05:59:31 in reply to 12 [link]

I probably should find a better name for the table now, or its field. I do not like that the name of the table and the field is the same.
Maybe rename the field to today. (I think it will always be current day.)

(15) By Cecil (CecilWesterhof) on 2021-01-29 11:26:44 in reply to 12 [link]

This goes wrong when there is no record for today. Rewrote it to:
    WITH now(today)
    AS (
        SELECT DATE('now', 'localtime')
    ),
    cte(today, idx)
    AS (
        SELECT (SELECT today FROM now)
        ,      IFNULL(MAX(lastUsedIdx), 0) + 1
        FROM   selectRandom, now
        WHERE  lastUsed == today
    )
    UPDATE selectRandom
    SET    (lastUsed, lastUsedIdx) = (SELECT today, idx FROM cte)
    WHERE  description = :teaToBrew

Or would it be better to use:
        FROM   selectRandom
        WHERE  lastUsed == (SELECT today FROM now)

(16) By Keith Medcalf (kmedcalf) on 2021-01-29 16:45:01 in reply to 15 [link]

The former.  

Putting the table now in the join and using lastused == today indicates that the descent order needs to be from the now into selectRandom -- the date function is only executed once (it must be materialized).

If you remove now from the join and instead use a scalar constant select then the query flattener will merely substitute the constant computation resulting in the date function being executed twice because there is not longer a join to optimize.

(17) By Cecil (CecilWesterhof) on 2021-01-29 16:58:07 in reply to 16 [link]

Thanks. Then I leave it as it is,