sql: not authorized to use function: strftime, date etc

[steepdawn974]

Issue

Per lightning-sql documentation, the functions strftime, date etc should be supported, but queries fail with the error:

   "message": "query failed with not authorized to use function: strftime (Unauthorized: 31 arg1=(none) arg2=strftime dbname=(none) caller=(none))"

Steps to Reproduce

Example 1:

lightning-cli sql -k "query"="SELECT account,tag, STRFTIME('%Y-%m-%d %H:%M:%f', timestamp / 1000000000, 'unixepoch'),sum(debit_msat/1000),sum(credit_msat/1000) from bkpr_income where tag='routed'  group by 1,2,3"

{
   "code": -1,
   "message": "query failed with not authorized to use function: strftime (Unauthorized: 31 arg1=(none) arg2=strftime dbname=(none) caller=(none))"
}

Example 2:

lightning-cli sql -k "query"="SELECT account,tag,date(timestamp),sum(debit_msat/1000),sum(credit_msat/1000) from bkpr_income where tag='routed'  group by 1,2,3"

{
   "code": -1,
   "message": "query failed with not authorized to use function: date (Unauthorized: 31 arg1=(none) arg2=date dbname=(none) caller=(none))"
}

getinfo

getinfo
{
"id": "xxx",
"alias": "xxx
"color": "0362df",
"num_peers": 4,
"num_pending_channels": 1,
"num_active_channels": 3,
"num_inactive_channels": 0,
"address": [
{
"type": "torv3",
"address": "xxxxxxx.onion",
"port": 9736
}
],
"binding": [
{
"type": "ipv4",
"address": "127.0.0.1",
"port": 9736
}
],
"version": "v24.05",
"blockheight": 857896,
"network": "bitcoin",
"fees_collected_msat": ,
"lightning-dir": "/home/bitcoin/.lightning/bitcoin",
"our_features": {
"init": "08a0000a8a59a1",
"node": "88a0000a8a59a1",
"channel": "",
"invoice": "02000002024100"
}
}

[ShahanaFarooqui]

Datetime functions were introduced in Core Lightning's v24.08 as part of PR #7467. However, the SQL documentation does not indicate the version in which this feature was added. To use these functions, your node has to be upgraded to v24.08.