Sqlfilter proposal timestamps

Developing for Dolibarr
1

Hi

I want to look for a specific proposal when I have a timestamp from the automatic notification emails,

In the email I have this field

NOW_TMS 1707039969

And when I look in the json for that proposal I can see that value several different places
“date_validation”: 1707039969,
“date_modification”: 1707039969,
“datev”: 1707039969,

The other date fields in the json are:
“date_creation”: 1707039934,

“specimen”: 0,
“socid”: “100”,
“datec”: 1707039934,

“date”: 1707001200,
“datep”: 1707001200,
“fin_validite”: 1706953534,

but I can not find all those in the database table or I can not use them in the sqlfilter

| tms | timestamp | YES | | current_timestamp() | on update current_timestamp() |
| datec | datetime | YES | | NULL | |
| datep | date | YES | | NULL | |
| fin_validite | datetime | YES | | NULL | |
| date_valid | datetime | YES | | NULL | |
| date_signature | datetime | YES | | NULL | |
| date_cloture | datetime | YES | | NULL | |

I’ve tried with these sqlfilters
(t.datec:=:1707039934:) - which works but find nothing
(t.date:=:1707001200:) - error Unknown column ‘t.date’ in ‘where clause’
(t.datep:=:1707001200:) - which also works, but doesn’t find anything
(t.datev:=:1707039969:) - error Unknown column ‘t.datev’ in ‘where clause’
(t.tms:<:1707001200:) - which works but doesn’t find anything

Do I need to use non epoch time to get my data?

MariaDB [dolidb]> select rowid, tms, datec, datep, fin_validite, date_valid, date_signature, date_cloture from llx_propal where rowid=432;
±------±--------------------±--------------------±-----------±--------------------±--------------------±---------------±-------------+
| rowid | tms | datec | datep | fin_validite | date_valid | date_signature | date_cloture |
±------±--------------------±--------------------±-----------±--------------------±--------------------±---------------±-------------+
| 432 | 2024-02-04 10:46:09 | 2024-02-04 10:45:34 | 2024-02-04 | 2024-02-03 10:45:34 | 2024-02-04 10:46:09 | NULL | NULL |
±------±--------------------±--------------------±-----------±--------------------±--------------------±---------------±-------------+

2

these doesn’t find anything either

(t.tms:like:%1707001200%:) 
(t.tms:like:%2024-02-04 10:46:09%:) 
(t.tms:like:%1707039969%:)
3

Hello

try this

SELECT * FROM llx_propal p where p.tms=“20170204104609”;

Good continuation

4

doesn’t work

“error”: {
“code”: 400,
“message”: “Bad Request: Error when validating parameter sqlfilters → Bad syntax of the search string”
},

5

Hello

can you try this

SELECT * FROM llx_propal p where p.tms=20170204104609;

Good continuation

6

did not work

{
“error”: {
“code”: 400,
“message”: “Bad Request: Error when validating parameter sqlfilters → Bad syntax of the search string”
},
“debug”: {
“source”: “api_proposals.class.php:210 at call stage”,
“stages”: {
“success”: [
“get”,
“route”,
“negotiate”,
“authenticate”,
“validate”
],
“failure”: [
“call”,
“message”
]
}
}
}

7

Hello

for the sqlfilters, can you try this

t.tms='20170204104609' or t.tms=20170204104609

image
image1068×852 34.4 KB

Good continuation

8

both suggestions gives the same error

proposal_sqlfilter
proposal_sqlfilter964×1157 168 KB

9

Hello

check the sortfield parameter

image
image977×419 11.8 KB

Good continuation

10

sorry, no change, still doesn’t work.

I use 18.0.4 in tuxgasy container and my database is mariadb

11

Hello

for the sqlfilters, can you try this

t.tms='2017-02-04 10:46:09' or t.tms=2017-02-04 10:46:09

Good continuation

12

@pcbleu doesn’t work

13

You must follow the syntax provided as example for sqlfilters
Example:
(t.tms:=:‘2020-12-12 10:00:00’)

14

Where are API examples provided? And wouldn’t it be nice if the API explorer linked to those examples?

15

I started a page on the wiki for accurate information: Universal Search Filter Syntax - Dolibarr ERP CRM Wiki

1 Like