sqlite3 time incorrectly interpreted by php

[Florin Muntean]

query of a view with time function returns the following error:

Warning: time() expects exactly 0 parameters, 3 given in /var/www/html/phpliteadmin/index.php on line 4744

no rows are returned.

this is the view:

CREATE VIEW 'vLights' as SELECT l.ts,l.duration,l.device,date(l.ts,'unixepoch','localtime') as DATE,time(l.ts,'unixepoch','localtime') as TIME, p.avgW, cast(l. duration as real)/60/60*p.avgW as powerW FROM "LIGHT" l join power p on l.device=p.device

[Florin Muntean]

This was working fine in the previous version v1.9.7.1

[Florin Muntean]

found that the issue is with php 7.3 as after downgrading to 7.2 the site works.

[Florin Muntean]

any update?

if I run the query inside the sqlite3 command line it works as expected.

this is the function I use in the query:
date(l.ts,'unixepoch','localtime') as DATE,
time(l.ts,'unixepoch','localtime') as TIME

[phpLiteAdmin]

Please have a look at your $custom_functions config variable. I just saw that the sample config contains 'time' in here. Remove this. With custom functions you can access PHP functions from SQL in phpliteadmin. In this case, this overwrites the SQL time function with the PHP time function.

‌

[Florin Muntean]

The time(..) function is an sqlite function and is working fine in previous versions of sqliteadmin as mentioned before.

The tool should not override sqlite internal functions. https://www.sqlite.org/lang_datefunc.html

[phpLiteAdmin]

Yes but the function is overwritten because of your configuration. You can remove 'time' from the $custom_functions config setting, then it won’t be overwritten anymore. I admit that the sample config is bad and should not include the time function. But you can easily solve your problem by adjusting the config. Probably the old config sample did not include this, which is why it worked before.

[Florin Muntean]

thanks, That fixed my issue. however I still think this is a bug as that was default configuration and I would not expect the sqlite internal functions to be overridden by default.

[phpLiteAdmin]

Removed time function from $custom_functions, because it overwrites the time function of SQLite Fixes issue #386 https://bitbucket.org/phpliteadmin/public/issues/386/sqlite3-time-incorrectly-interpreted-by

→ <<cset d73c9ad8cb67>>

[phpLiteAdmin]

Of course you are right that this is a bad default configuration. I just removed it from the default config. Thank you for reporting this.